software

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 -gt;
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 quot;i = ComboBox1.ListIndexquot;. 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 lt; 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 gt; 1 Then
ComboBox1.ListIndex = i - 1
If i - 1 = 1 Then CommandButton2.Enabled = False
Else
CommandButton2.Enabled = False
End If
End Sub--
jgmiddel
------------------------------------------------------------------------
jgmiddel's Profile: www.excelforum.com/member.php...oamp;userid=32714
View this thread: www.excelforum.com/showthread...hreadid=536301I 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(quot;sheet1quot;)
Set myRng = .Range(quot;a1:a20quot;)
End With

Me.CommandButton1.Caption = quot;Nextquot;
With Me.CommandButton2
.Caption = quot;Previousquot;
.Enabled = False
End With
Me.CommandButton3.Caption = quot;Cancelquot;
Me.CommandButton4.Caption = quot;Okquot;

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

With Me.ComboBox1
.Style = fmStyleDropDownList
.ColumnCount = 5
.ColumnWidths = quot;22;0;0;0;0quot;
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(quot;Labelquot; amp; iCtr) = Me.ComboBox1.List(WhichOne, iCtr)
Next iCtr
Me.Label4.Caption = WhichOne 1

With Me.ComboBox1
Me.CommandButton1.Enabled = CBool(.ListIndex lt; .ListCount - 1)
Me.CommandButton2.Enabled = CBool(.ListIndex gt; 0)
End With

End Subjgmiddel wrote:
gt;
gt; In A1 - D20 I have values. The range A1 - A20 is the input for the
gt; ComboBox
gt; in my userform. What should happen is the following: ComboBox -gt;
gt; onchange:
gt; refresh the data in the labels. If the selected item in the ComboBox is
gt; the
gt; value from A10, in Label 1 should the value of B10 appear, etc. And: if
gt; the
gt; first item is selected, the PREVIOUS button must be disabled, when the
gt; last
gt; item is selected, the NEXT button.
gt;
gt; I have some errors in the code:
gt;
gt; - when the first item is selected, I get an error
gt; - when the second item is selected, the button NEXT is disabled
gt; - when the last item is selected, the button NEXT is still enabled,
gt; pressing
gt; it will lead to an error
gt;
gt; I think there are some errors in the lines quot;i = ComboBox1.ListIndexquot;. I
gt; made
gt; some changes, but they didn't work properly. Any suggestions??
gt;
gt; --------
gt;
gt; Private Sub ComboBox1_Change()
gt; Dim i As Long 'Index
gt; i = ComboBox1.ListIndex
gt; If i = ComboBox1.ListCount Or i = 1 Then CommandButton1.Enabled =
gt; False
gt; Label1.Caption = Cells(i, 2).Value
gt; Label2.Caption = Cells(i, 3).Value
gt; Label3.Caption = Cells(i, 4).Value
gt; End Sub
gt;
gt; Private Sub CommandButton1_Click() 'Next Button
gt; Dim i As Long
gt; i = ComboBox1.ListIndex
gt; If i lt; ComboBox1.ListCount Then
gt; ComboBox1.ListIndex = i 1
gt; If i 1 = ComboBox1.ListCount Then CommandButton1.Enabled = False
gt; Else
gt; CommandButton1.Enabled = False
gt; End If
gt; End Sub
gt;
gt; Private Sub CommandButton2_Click() 'Previous Button
gt; Dim i As Long
gt; i = ComboBox1.ListIndex
gt; If i gt; 1 Then
gt; ComboBox1.ListIndex = i - 1
gt; If i - 1 = 1 Then CommandButton2.Enabled = False
gt; Else
gt; CommandButton2.Enabled = False
gt; End If
gt; End Sub
gt;
gt; --
gt; jgmiddel
gt; ------------------------------------------------------------------------
gt; jgmiddel's Profile: www.excelforum.com/member.php...oamp;userid=32714
gt; View this thread: www.excelforum.com/showthread...hreadid=536301

--

Dave Peterson