Excel VBA Add Item below ListBox Selection

Question

I have a ListBox that I am reading a text file into that has several lines. I am currently clicking a line to search for the value clicked in a sheet and then adding that value to the listbox which goes to the bottom.

If I have 10 lines in my listbox and I click line 5, how do I add an item to line 6?

Sub FindListValue()

Dim FirstAddress As String
Dim rSearch As Range  'range to search
Dim c As Range

Sheets("PN-BINS").Activate

Set rSearch = ActiveSheet.Range("b1", Range("b65536").End(xlUp))

strFind = Me.ListBox1.Value 'what to look for

With rSearch
    Set c = .Find(strFind, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then    'found it
     c.Select
    'MsgBox strFind & c.Offset(0, -1).Value


    Me.ListBox1.AddItem strFind & " " & c.Offset(0, -1).Value

    Else: MsgBox strFind & " is not listed!"    'search failed
    'Range("K1").Select
    End If
    End With

End Sub

Please post your current VBA code for the listbox.

Ralph
– Ralph

2016-11-11 16:15:27 +00:00
Commented Nov 11, 2016 at 16:15 — Ralph
– Ralph, Commented Nov 11, 2016 at 16:15

Ralph · Accepted Answer · 2016-11-11 17:55:51Z

There are a few steps involved to achieve the desired result:

Check if an item on the listbox is selected. If you allow for MultiSelectthen you can also check how many items have been selected and where you want to insert the new item. If nothing is selected then simply add the item to the end of the list.
If an item on the list is selected, then store the INDEX for the currently selected item so that you
- know where to insert the new item and
- know which item should be selected at the end of the macro.
Iterate the listbox from the selected item on forward (the prior items do not need to be modified) to insert the new value and store the "overwritten" value in the list in a temp variable (so you can "overwrite" the next line in the list.
At the end you finally add a new item to the list and write the temp variable as a value to the list (newly added item).

Here is the complete code to achieve the above:

Option Explicit

Private Sub btnAdd_Click()

Dim y As Long
Dim tmp1 As String
Dim tmp2 As String
Dim curSelection As Long

'If nothing is selected then you can simply add it to the end of the list
If Me.ListBox1.ListIndex < 0 Then
    Me.ListBox1.AddItem Me.TextBox1.Value
    Exit Sub
End If

'Save the INDEX for the currently selected item
curSelection = Me.ListBox1.ListIndex

tmp2 = Me.TextBox1.Value
For y = curSelection To Me.ListBox1.ListCount - 1
    tmp1 = Me.ListBox1.List(y)
    Me.ListBox1.List(y) = tmp2
    tmp2 = tmp1
Next y
Me.ListBox1.AddItem tmp2
Me.ListBox1.Selected(curSelection) = True

End Sub

And this is how it should work at the end:

Collectives™ on Stack Overflow

Excel VBA Add Item below ListBox Selection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related