Re-Run Listbox Selection

[grady121]

Not being used to working with Listboxes, I've been given a code that populates a UserForms Listbox with all the names in column B.
Depending on the selection made, it populates a marker cell at column "U" in the same row with a "1", to work with sheet Formulas.

Unfortunately it only runs once one before closing the UserForm.
What I'm looking for is a modification of my existing code that allows me to make another selection for the second name.
I don't want to make two selections at the same time, but to re-show the list deselected, enabling me to select again from the same list.

Without creating another UserForm I would also like to change the Background colour and Text in a TextBox1 on the UserForm to distinguish between the two options and re-run the code to fill the marker cell.


Existing Code:

Private Sub UserForm_Initialize()
    
With ActiveSheet.Range("B10")
    ListBox1.List = .Resize(.CurrentRegion.Rows.Count, 1).Value
End With

End Sub

Private Sub CommandButton1_Click()

For i = 0 To ListBox1.ListCount - 1
      If ListBox1.Selected(i) Then
         lngRow = i + 10
           
    Application.ScreenUpdating = False
End If
Next i

    ' Enter Formula Marker in column U
On Error GoTo NOPICK
    Range("U" & lngRow).Value = "1"
    
    Application.ScreenUpdating = True

#######
Need to add 2nd choice option here with change of BackColor & Text.
    ' Change Background Colour & Text
    BackColor = &HFFC0C0
    TextBox1.Value = "Second Choice"
#######
    
    Unload Me
    Range("D6").Select
    MsgBox "Finished", 64, "All Done"
        
Exit Sub

NOPICK:
    MsgBox "   No selection made.", 64, "Error"
    
End Sub

Any help to a Newbie appreciated.

 

[Norie]

Is this a multiselect listbox?

 

[grady121]

Thanks for response Norie
It's set to frmMultiSelectSingle, which I think is what I want.

i.e. Select the first name and run code to insert first marker.
Clear selection and make changes to UserForm colour & TextBox.
Allow second selection and run insert code again.
Exit Sub.

Thanks again.

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> lngRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> ListBox1.ListCount - 1<br>        <SPAN style="color:#00007F">If</SPAN> ListBox1.Selected(i) <SPAN style="color:#00007F">Then</SPAN> lngRow = i + 10<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">If</SPAN> lngRow = 0 Then: MsgBox "   No selection made.", 64, "Error": <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    <SPAN style="color:#007F00">' Enter Formula Marker in column U</SPAN><br>    Range("U" & lngRow).Value = "1"<br>    <SPAN style="color:#007F00">'lngRow = 0</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> TextBox1.Value = "Second Choice" <SPAN style="color:#00007F">Then</SPAN><br>        Unload Me<br>        Range("D6").Select<br>        MsgBox "Finished", 64, "All Done"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#007F00">'2nd choice option here with change of BackColor & Text.</SPAN><br>    ListBox1.BackColor = &HFFC0C0<br>    ListBox1.ListIndex = -1<br>    TextBox1.Value = "Second Choice"<br>    <SPAN style="color:#007F00">'TextBox1.Enabled = False</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Norie]

If that's the case you don't need the loop to find out the selected item.

You can use the ListIndex of the listbox for that:

' first check if an item is selected in the listbox:
If ListBox1.ListIndex = -1 Then
 
      lngRow = ListBox1.ListIndex +10
Else
 
    MsgBox "Nothing selected from list."
 
    Exit Sub
 
End If
 
' code to put marker on worksheet
 
' now clear the the listbox of the selection
 
ListBox1.ListIndex = -1

Not 100% sure what you want to do next because I can't work out where the textbox comes into it and why you want it to change colour.

Is there going to be a value in the textbox?

 

[grady121]

Thanks guys.

Both replies have helped me understand a little more of what's going on and the amendments I need to make, to my code work better.

AlphaFrog has also given me a bit more info that I can use later on as well.

Thanks again for you prompt replies.