Userform problem (Listindex)

[jgmiddel]

In A1 - D20 I have values. The range A1 - A20 is the input for the
ComboBox
in my userform. What should happen is the following: ComboBox ->
onchange:
refresh the data in the labels. If the selected item in the ComboBox is
the
value from A10, in Label 1 should the value of B10 appear, etc. And: if
the
first item is selected, the PREVIOUS button must be disabled, when the
last
item is selected, the NEXT button.

I have some errors in the code:

- when the first item is selected, I get an error
- when the second item is selected, the button NEXT is disabled
- when the last item is selected, the button NEXT is still enabled,
pressing
it will lead to an error

I think there are some errors in the lines "i = ComboBox1.ListIndex". I
made
some changes, but they didn't work properly. Any suggestions??

--------

Private Sub ComboBox1_Change()
Dim i As Long 'Index
i = ComboBox1.ListIndex
If i = ComboBox1.ListCount Or i = 1 Then CommandButton1.Enabled =
False
Label1.Caption = Cells(i, 2).Value
Label2.Caption = Cells(i, 3).Value
Label3.Caption = Cells(i, 4).Value
End Sub

Private Sub CommandButton1_Click() 'Next Button
Dim i As Long
i = ComboBox1.ListIndex
If i < ComboBox1.ListCount Then
ComboBox1.ListIndex = i + 1
If i + 1 = ComboBox1.ListCount Then CommandButton1.Enabled = False
Else
CommandButton1.Enabled = False
End If
End Sub

Private Sub CommandButton2_Click() 'Previous Button
Dim i As Long
i = ComboBox1.ListIndex
If i > 1 Then
ComboBox1.ListIndex = i - 1
If i - 1 = 1 Then CommandButton2.Enabled = False
Else
CommandButton2.Enabled = False
End If
End Sub

 

[Dave Peterson]

I built a small userform with a combobox and 4 labels and 4 commandbuttons.

The combobox had 5 columns. But only the first was visible. It held the values
in A1:A20 (visible) along with the values in B120 (hidden from view, though)
and the row number (also hidden).

The 4 labels were for the the values in column B (3 of them) and last was used
as a row indicator.

The four commandbuttons were for Next, Previous, Cancel, and Ok.

This is the code I had under the userform:

Option Explicit
Private Sub ComboBox1_Change()
Call ChangeTheValues(Me.ComboBox1.ListIndex)
End Sub
Private Sub CommandButton1_Click()
'next button
With Me.ComboBox1
'changing the .listindex will cause the _change event to fire
.ListIndex = .ListIndex + 1
End With
End Sub
Private Sub CommandButton2_Click()
'Previous button
With Me.ComboBox1
'changing the .listindex will cause the _change event to fire
.ListIndex = .ListIndex - 1
End With
End Sub
Private Sub CommandButton3_Click()
'cancel button
Unload Me
End Sub
Private Sub CommandButton4_Click()
'ok button
'do whatever you need for ok
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long

With Worksheets("sheet1")
Set myRng = .Range("a1:a20")
End With

Me.CommandButton1.Caption = "Next"
With Me.CommandButton2
.Caption = "Previous"
.Enabled = False
End With
Me.CommandButton3.Caption = "Cancel"
Me.CommandButton4.Caption = "Ok"

Me.CommandButton1.TakeFocusOnClick = False
Me.CommandButton2.TakeFocusOnClick = False
Me.CommandButton3.TakeFocusOnClick = False
Me.CommandButton4.TakeFocusOnClick = False

With Me.ComboBox1
.Style = fmStyleDropDownList
.ColumnCount = 5
.ColumnWidths = "22;0;0;0;0"
For Each myCell In myRng.Columns(1).Cells
.AddItem myCell.Value
For iCtr = 1 To 3
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
.List(.ListCount - 1, 4) = myCell.Row
Next myCell

'changing the .listindex will cause the _change event to fire
.ListIndex = 0
End With

End Sub
Private Sub ChangeTheValues(WhichOne As Long)
Dim iCtr As Long

For iCtr = 1 To 3
Me.Controls("Label" & iCtr) = Me.ComboBox1.List(WhichOne, iCtr)
Next iCtr
Me.Label4.Caption = WhichOne + 1

With Me.ComboBox1
Me.CommandButton1.Enabled = CBool(.ListIndex < .ListCount - 1)
Me.CommandButton2.Enabled = CBool(.ListIndex > 0)
End With

End Sub