VBA Excel Userform Listbox Initial Selection Based on Cell Value With Color


New Member
Jun 14, 2014
I have a Userform with several items including a Listbox entitled LBNewCase. The ListBox is populated (RowSource) through a range of cells. There is a cell that indicates the previous choice from this list.This cell (LastCaseMatch) uses the match function to find the position of the previous selection within the list used as the RowSource. When the ListBox is initialized I would like the list to appear with the last choice pre-selected/highlighted and the BackColor to be a light red so that the user will not have to change the selection if it to be the same for the current input but with the BackColor is light red to indicate that no change has been made (another item selected from the list) and that they should at least be aware of looking at this item.

If a change is made I would like to change the BackColor to a light yellow which would indicate to the user that they did make a change.

I have included an counter to track the number of changes being made before changing the BackColor to light yellow because the Private Sub UserForm_Initialize() calls the Private Sub LBNewCase_Change() multiple times and I do not wish the BackColor to be changed to light yellow thru the Private Sub LBNewCase_Change() during the initialization.

In the Userform:
Private Sub UserForm_Initialize()
      Sheets("Library").Range("NewCaseCounter") = 0 

'Set RowSource Locations for the Userform Listboxes
      CasesList = Sheets("Library").Range("CasesList").Value
      LBNewCase.RowSource = CasesList
'Set Userform Boxes That Bring in the Last Asset's Data to Light Red
      LBNewCase.BackColor = RGB(255, 115, 115) 'light red
'Set Userform Initial Values
          NewCaseMatch = Sheets("Library").Range("LastCaseMatch") - 1
          Sheets("Library").Range("LastCaseMatchB") = NewCaseMatch
      LBNewCase.Selected(NewCaseMatch) = True

 End Sub

Private Sub LBNewCase_Change()
    NewCaseCounter = Sheets("Library").Range("NewCaseCounter")
    NewCaseCounter = NewCaseCounter + 1
    Sheets("Library").Range("NewCaseCounter") = NewCaseCounter
    NewCaseMatch = Sheets("Library").Range("LastCaseMatchB")
    If NewCaseCounter > 2 Then
        LBNewCase.BackColor = RGB(255, 255, 205) 'light yellow
        NewCaseMatchB = Sheets("Library").Range("LastCaseMatchB")
    End If
    LBNewCase.Selected(NewCaseMatch) = True
End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics