Refresh listbox

[bwwhite]

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

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

[p45cal]

Would you be able to attach (a version of) your workbook with attendant userform and necessary code here? It drastically reduces assumptions we have to make as well as making it easier to reproduce your problem.

[bwwhite]

Here is an example spreadsheet. There are 2 tabs in the spreadsheet--"Expertise" and "Info". The Expertise tab identifies individuals with various areas of expertise. One person can have multiple areas of expertise or multiple people can have the same expertise. The Info tab provides background information on an identified expert.

There are 2 forms in the VBA editor--FindExpert and AddNewExpert. FindExpert works as it should. When you run the FindExpert form, it lists all the unique areas of expertise in the first listbox. Click on "passing" and the name "Joe Montana" appears in the Name listbox. Click on his name in that listbox and other information about him appears.

It's the AddNewExpert form that is giving me trouble. I want to use this form to add new experts to the spreadsheet. Expertises are arranged in categories. When AddNewExpert is invoked, all unique categories and expertises are listed in the appropriate windows. Thus, one can type in the name and then choose their expertise and the category in which to place that expertise.

However, I also want the form to allow the user to create a new category and/or expertise. Therefore the first item on the list is "--Click here to add new ...". When the user clicks there, an inputbox appears so you can type in the new category or expertise. That item is then added to the appropriate listbox. Where it fails is that for some reason, I'm not passing the right information to my sorting subroutine at that point, because I want that new item to be in the proper place on the list. Also, I want that newly added item to be highlighted (selected) and visible in that listbox.

At this point, only the "Close" button works on each tab of the AddNewExpert userform. I haven't worked on them as I've been stuck on this current issue.

Any help is greatly appreciated! Thanks in advance!

Bruce

[bwwhite]

IN an earlier post, I said my sort function quit working. Being somewhat braindead, I finall realized it really IS working. My problem is that in the real worksheet, my data begins with upper case letters. I was adding new data in lower case, which the sorting procedure always put AFTER the upper case letters.

However, having the new item appear selected after adding to the list still doesn't work they way I want it to. I was hoping to incorporate it inot the listbox_click function but can't get it to work. I have a workaround by creating a "refresh" button. Any help getting it to work in the listbox_click function would be wonderful!

Thanks!

Bruce

[p45cal]

having the new item appear selected after adding to the list still doesn't work they way I want it to

Nor can I! It seems that the setting of the .listindex, the .value or the .selected(j) all work to highlight/select the new category outside of the lbxCategory_Click sub, but not from within it. Another workaround might be to have a button to add a new category instead of clicking within the category listbox. This would simplify the code and the correct highlighting would occur.

A few unrelated points:
The line:

strCatName = Me.Controls("lbxCategory").Name

uses the name of the control to return, well, the name!
You can shorten it to:

strCatName ="lbxCategory"

However, you do something in the called procedure with this string to return the control. You can, instead of passing a string, pass the control itself.
So

iSortMe (strCatName)

becomes:

iSortMe Controls("lbxCategory")

with a bit of shortening of the iSortMe procedure to:

Private Sub iSortMe(myCtl As Control)
Dim i As Long
Dim j As Long
Dim temp As Variant
With myCtl
    For j = 0 To .ListCount - 2
        For i = 0 To .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

Elsewhere you have the two lines:

            myFormula = "=IF(C" & intLastRow & Chr(34) & Chr(34) & "," & Chr(34) & _
                    "no one identified" & Chr(34) & ",D" & intLastRow & "),if(D" & _
                    intLastRow & "=" & Chr(34) & Chr(34) & ",C" & intLastRow & ",D" & _
                    intLastRow & "&" & Chr(34) & " " & Chr(34) & "&C" & intLastRow & "))"
            mySht.Cells(intLastRow, 5).Formula = myFormula

which can be shortened to one line by using an R1C1 type formula (which I've changed a bit so that if someone puts in just a space or spaces in the names it still complains (as it should) that names are required. I'm pretty sure the formula does the same as yours:

mySht.Cells(intLastRow, 5).FormulaR1C1 = "=IF(TRIM(RC[-2] & RC[-1])="""",""no one identified"",TRIM(RC[-1] &"" ""& RC[-2]))"

[bwwhite]

p45cal,

Thank you for the suggestion on creating a separate button to do what I wanted to do. It worked just fine. Is there any special way to acknowledge your helpfulness other than this reply? Since I am satisfied with the solution, do I just reply with "Solved" preceding the subject line?

Also, your formula was better than mine and did the same thing. Thanks for the insights on some leftover code I should have cleaned up, too.

Thanks again!

Bruce

[p45cal]

Is there any special way to acknowledge your helpfulness other than this reply?

No, no special way, the reply was enough.

do I just reply with "Solved" preceding the subject line?

You can add the Solved bit with thread tools in the top right of the page.

[snb]

A much simpler way to populate the unique sorted listbox:

Private Sub UserForm_Initialize()
With CreateObject("System.Collections.ArrayList")
For Each cl In Sheets("expertise").Columns(2).SpecialCells(2)
If Not .contains(cl) Then .Add cl
Next
.Sort
lbxExpertise.List = Application.Transpose(.toarray())
End With
End Sub