Home |
Search |
Today's Posts |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 B1:D20 (hidden from view, though) and the row number (also hidden). The 4 labels were for the the values in column B:D (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 jgmiddel wrote: 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 -- jgmiddel ------------------------------------------------------------------------ jgmiddel's Profile: http://www.excelforum.com/member.php...o&userid=32714 View this thread: http://www.excelforum.com/showthread...hreadid=536301 -- Dave Peterson |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
userform that add data in all w/sheets | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |