ListBox and ListIndex

[mtm4300 via OfficeKB.com]

I have two Option Buttons, a ListBox, and two command buttons on a UserForm.
If the user clicks on Optionbutton1, the Listbox populates with certain data.
If the user clicks on Optionbutton2, the Listbox populates with different
data. The user then selects one of the items in the listbox and clicks
"Continue" (command button). After clicking the command button, the program
sends the user to a specific Sheet.

I am having a problem with:
When the user clicks on Optionbutton2 the Listbox populates fine, however it
does not send the user to the correct sheet after he/she clicks "Continue". I
think I am doing this right and I cannot figure out what is wrong. Any help
would be greatly appreciated. My code follows below.
_______________________

Private Sub CommandButton4_Click()
' Stores the selection from the Listbox to allow the user to continue to
the correct pipe type.

If Me.ListBox1.ListIndex = 0 = True Then
Worksheets(48).Activate
UserForm12.Hide

ElseIf Me.ListBox1.ListIndex = 1 = True Then
Worksheets(49).Activate
UserForm12.Hide

ElseIf Me.ListBox1.ListIndex = 2 = True Then
Worksheets(50).Activate
UserForm12.Hide

ElseIf Me.ListBox1.ListIndex = 3 = True Then
Worksheets(51).Activate
UserForm12.Hide

ElseIf Me.ListBox1.ListIndex = 4 = True Then
Worksheets(52).Activate
UserForm12.Hide

ElseIf Me.ListBox1.ListIndex = 5 = True Then
Worksheets(68).Activate
UserForm12.Hide

ElseIf Me.ListBox1.ListIndex = 6 = True Then
Worksheets(69).Activate
UserForm12.Hide

ElseIf Me.ListBox1.ListIndex = 7 = True Then
Worksheets(70).Activate
UserForm12.Hide

ElseIf Me.ListBox1.ListIndex = 8 = True Then
Worksheets(71).Activate
UserForm12.Hide

ElseIf Me.ListBox1.ListIndex = 9 = True Then
Worksheets(72).Activate
UserForm12.Hide
End If
End Sub
Private Sub OptionButton1_Click()
Me.ListBox1.Clear
With Me.ListBox1
.AddItem "A", Index = 0
.AddItem "B", Index = 1
.AddItem "C", Index = 2
.AddItem "D", Index = 3
.AddItem "E", Index = 4
End With

End Sub
Private Sub OptionButton2_Click()
Me.ListBox1.Clear
With Me.ListBox1
.AddItem "A", Index = 5
.AddItem "B", Index = 6
.AddItem "C", Index = 7
.AddItem "D", Index = 8
.AddItem "E", Index = 9

End With

End Sub

 

[Dick Kusleika]

Private Sub OptionButton2_Click()
Me.ListBox1.Clear
With Me.ListBox1
.AddItem "A", Index = 5
.AddItem "B", Index = 6
.AddItem "C", Index = 7
.AddItem "D", Index = 8
.AddItem "E", Index = 9

End With

End Sub

You should be getting an error on the AddItem statements. I get "Variable
not Defined" on Index. In any event, the index you supply can't be greater
than the ListCount, which should be zero after the Clear and you should get
a run time error if you removed the "Index = " part.

The index argument of AddItem doesn't assign an index number to the entry,
it only determines where in the list the new item will be added. The
ListIndex when A is selected will still be zero despite providing and Index
argument in AddItem.

Here's how I would do it:

Private Sub CommandButton4_Click()
If Me.OptionButton1.Value Then
Worksheets(Me.ListBox1.ListIndex + 48).Activate
Else
Worksheets(Me.ListBox1.ListIndex + 68).Activate
End If
Me.Hide
End Sub


--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com


End Sub

 

[mtm4300 via OfficeKB.com]

That does it. Thank you very much!

Private Sub OptionButton2_Click()
Me.ListBox1.Clear

[quoted text clipped - 8 lines]

End Sub

You should be getting an error on the AddItem statements. I get "Variable
not Defined" on Index. In any event, the index you supply can't be greater
than the ListCount, which should be zero after the Clear and you should get
a run time error if you removed the "Index = " part.

The index argument of AddItem doesn't assign an index number to the entry,
it only determines where in the list the new item will be added. The
ListIndex when A is selected will still be zero despite providing and Index
argument in AddItem.

Here's how I would do it:

Private Sub CommandButton4_Click()
If Me.OptionButton1.Value Then
Worksheets(Me.ListBox1.ListIndex + 48).Activate
Else
Worksheets(Me.ListBox1.ListIndex + 68).Activate
End If
Me.Hide
End Sub