Multi-Select List Box

[nayagamvalli]

Hi Everyone,

I have created a multi select list box with options like

All
Option1
Option2
Option3

Now if anyone select the option All, then i don't want to allow anyone
to select other options. I am using Excel 2000. I found this is the
best place to post my query. I have used the below code but it is not
working

for i=0 to listbox1.listcount-1
listbox1.Selected(i) = False
next

Thanks in advance

Cheers

Valli

 

[Dave Peterson]

I created a userform with a listbox on it and this seemed to work ok:

Option Explicit
Dim BlkProc As Boolean
Private Sub ListBox1_Change()
Dim iCtr As Long
If BlkProc = True Then Exit Sub
If Me.ListBox1.Selected(0) = True Then
BlkProc = True
For iCtr = 1 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(iCtr) = False
Next iCtr
BlkProc = False
End If
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
BlkProc = True
.MultiSelect = fmMultiSelectMulti
BlkProc = False
.AddItem "All"
.AddItem "Option1"
.AddItem "Option2"
.AddItem "Option3"
End With
End Sub

 

[nayagamvalli]

Thanks Dave. I have created the listbox using control toolbox in
worksheet. Also i have tried the above code in Excel 2000, i could
able to select All and the option1, option2.. also.

 

[Dave Peterson]

I put this under the worksheet module that held the listbox:

Option Explicit
Dim BlkProc As Boolean
Private Sub ListBox1_Change()
Dim iCtr As Long
If BlkProc = True Then Exit Sub
If Me.ListBox1.Selected(0) = True Then
BlkProc = True
For iCtr = 1 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(iCtr) = False
Next iCtr
BlkProc = False
End If
End Sub
'I used this to populate the listbox
Private Sub Worksheet_Activate()
With Me.ListBox1
BlkProc = True
.MultiSelect = fmMultiSelectMulti
BlkProc = False
.AddItem "All"
.AddItem "Option1"
.AddItem "Option2"
.AddItem "Option3"
End With
End Sub

 

[Valli]

I put this under the worksheet module that held the listbox:

Option Explicit
Dim BlkProc As Boolean
Private Sub ListBox1_Change()
Dim iCtr As Long
If BlkProc = True Then Exit Sub
If Me.ListBox1.Selected(0) = True Then
BlkProc = True
For iCtr = 1 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(iCtr) = False
Next iCtr
BlkProc = False
End If
End Sub
'I used this to populate the listbox
Private Sub Worksheet_Activate()
With Me.ListBox1
BlkProc = True
.MultiSelect = fmMultiSelectMulti
BlkProc = False
.AddItem "All"
.AddItem "Option1"
.AddItem "Option2"
.AddItem "Option3"
End With
End Sub

Great Dave..Thanks a lot for your help