I have searched and searched and cannot figure out how to do what I want to do. I have a form with a listbox. The listbox is populated from a column of data in Excel. It only uses the unique items in the column. A subroutine properly sorts the data before populating the listbox. One of the items in the listbox is "--Click to add new item--".
I want a click event on the listbox, such that when I click "--Click to add new item--", an input box appears to add the new item. So far, my code works. However, when I click "OK" on the input box, I want to add the item to the list (it does that just fine), re-sort the list (it fails here) and make that new item show as selected (it fails here also).
I've included the relevant code below. Any ideas would be greatly appreciated. I'm pulling out what little hair I have left on this one.
NOTE: I'm not a super-skilled programmer, so give your answer like you were explaining to a beginner. Thanks!
Bruce
Code:Private Sub UserForm_Initialize()
Dim col As New Collection
Dim r As Long
Dim m As Long
Dim i As Integer, j As Integer, k As Integer
Dim temp As Variant
Dim ctl As Control
Dim strCtl(1) As String
Dim strColLetter As String
strCtl(0) = "lbxCategory"
strCtl(1) = "lbxExpertise"
Dim myCtl As String
Sheets("myname").Activate
For k = 1 To 2
myCtl = strCtl(k - 1)
strColLetter = Chr(64 + k)
m = Range(strColLetter & Rows.Count).End(xlUp).Row
On Error Resume Next
For r = 2 To m
col.Add Item:=Range(strColLetter & r).Value, Key:=CStr(Range(strColLetter & r).Value)
Next r
For r = 1 To col.Count
Me.Controls(myCtl).AddItem col(r)
Next r
Me.Controls(myCtl).AddItem "--Click here to add new item"
iSortMe (Me.Controls(myCtl).Name)
Set col = Nothing
Next k
End Sub
Private Sub iSortMe(myCtl As String)
Dim i As Long
Dim j As Long
Dim temp As Variant
With Me.Controls(myCtl)
For j = 0 To Me.Controls(myCtl).ListCount - 2
For i = 0 To Me.Controls(myCtl).ListCount - 2
If .List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
End If
Next i
Next j
End With
End Sub
Private Sub lbxCategory_Click()
Dim strCategory As String
Dim i As Integer, j As Integer
Dim strCatName As String
strCategory = lbxCategory.Text
If Left(strCategory, 2) = "--" Then
strCategory = InputBox("Type a new category", "New Category")
If strCategory = vbNullString Then Exit Sub
lbxCategory.AddItem strCategory
strCatName = Me.Controls("lbxCategory").
iSortMe (strCatName) 'this fails
For i = 0 To lbxCategory.ListCount - 1
If lbxCategory.List(i) = strCategory Then
j = i
lbxCategory.ListIndex = j 'this line does not seem to work
lbxCategory.Selected(j) = True ' nor does this line
End If
Next i
End If
End Sub
