Not-So-Easy Select and Deselect in Listbox (For Me At Least)

[DruTheFu]

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:

1. When the form is opened, the default selection of the listbox (lbOfficeLocation) is "***All Offices***".
   
2. If the user selects any other value (multiple values can be selected), the value "***All Offices***" is deselected.
   
3. 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.

 

[WayneRyan]

DTF,

Private Sub Form_Open
Dim holder As Integer
'
' Condition 1
'
For holder = 0 To lbOfficeLocation.ListCount - 1
    If lbOfficeLocation.ItemData(holder)  = "***All Offices***" Then lbOfficeLocation.Selected(holder) = True
    Next holder
End Sub


Private Sub lbOfficeLocation_OnClick()
Dim holder As Integer
'
' Condition 2
'
If lbOfficeLocation.ItemData(ListIndex) <> "***All Offices***" And lbOfficeLocation.Selected(ListIndex) Then
   For holder = 0 To lbOfficeLocation.ListCount - 1
       If lbOfficeLocation.ItemData(holder)  = "***All Offices***" Then lbOfficeLocation.Selected(holder) = False
       Next holder
'
' Condition 3
'
ElseIf lbOfficeLocation.ItemData(ListIndex) = "***All Offices***" And lbOfficeLocation.Selected(ListIndex) Then
       For holder = 0 To lbOfficeLocation.ListCount - 1
           If lbOfficeLocation.ItemData(holder)  <> "***All Offices***" Then lbOfficeLocation.Selected(holder) = False
           Next holder
End If
End Sub

Wayne

 

[DruTheFu]

I posted this in the code exactly as is, and it did not work. I do appreciate your response and effort though.

Is there anything I'm not doing or doing wrong?

 

[WayneRyan]

DTF,

What does it do, or not do?

Any errors?

Wayne

 

[DruTheFu]

It doesn't give any errors. When I click on a selection, it a) selects the value without de-selecting the "All" value, or b) selects the "All" value without de-selecting all the other values.

 

[modest]

1) Make sure you chose the bound column as the column that "All Offices" are in
2) Make sure you sent us the right string - see that its ***All Offices*** and not **** All Offices **** (or something similar)
3) You may have do do this by looping through the ItemsSelected instead
4) Post your database to get the sure way

 

[modest]

Dim bAllSelected As Boolean
Dim vTempItem    As Variant
bAllSelected = False

For each itm in Me.lbOfficeLocation.ItemsSelected
   If Me.lbOfficeLocation.Column(0, itm) = "***All Offices***"
      bAllSelected = True
      vTemp = itm
   Else
      bAllSelected = False
   End if
Next

Select Case bAllSelected
   Case True:
      For Each itm in Me.lbOfficeLocation.ItemsSelected
         If itm <> vTemp Then
            Me.lbOfficeLocation.Selected(itm) = False
         End If
      Next
   Case False:
      For Each itm in Me.lbOfficeLocation.ItemsSelected
         If itm = vTemp Then
            Me.lbOfficeLocation.Selected(itm) = False
         End If
      Next
End Select

See if this works for when All Offices are selected