Not-So-Easy Select And Deselect In Listbox (For Me At Least)

I made sure I did a search for this topic before I posted, so please don't flame me.

I'm trying to do something that in theory does not seem hard to do, but for some reason I can't translate it into code. With using a ListBox, I understand how to do a simple "when a value is selected, the remaining values are deselected" code. What I am trying to do is:



When the form is opened, the default selection of the listbox (lbOfficeLocation) is "***All Offices***".

If the user selects any other value (multiple values can be selected), the value "***All Offices***" is deselected.

If the user selects the value "***All Offices***", all other values are deselected.

For some reason or another, I can get (1) & (2) to work, but I cannot get (3) to work. After I make my initial selection that forces the deselection of value "***All Offices***" and highlights any other individual or multiple values, I try to re-select "***All Offices***" and it won't highlight, and will not deselect any of the other selected values.

I have tried multiple ways to code this, from using a For loop, If statement, and even a Select statement (Case 1, Case 2, ...), even using both Click() and AfterUpdate() as the event procedures, and nothing. I don't know how to use a IIF() statement very well, but do I need to use one of those? The code for what I have so far is below:

The name of the Listbox is "lbOfficeLocation".

Private Sub lbOfficeLocation_Click()

Dim holder As Integer

For holder = 0 To lbOfficeLocation.ListCount - 1
If lbOfficeLocation.Selected(holder) = True Then
Me.lbOfficeLocation.Selected(0) = False
End If
Next holder

End Sub


This code is for the deselection of value "***All Offices***" if any other value is selected. Please help me find the code to help with scenerio (3) as listed above, while still maintaining (2).

Thank you in advance, and feel free to ask for any additional info.