Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
jgmiddel
 
Posts: n/a
Default Userform problem (Listindex)


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Userform problem (Listindex)

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
userform that add data in all w/sheets tkraju via OfficeKB.com Excel Discussion (Misc queries) 0 March 24th 06 04:29 AM
Urgent Help Required on Excel Macro Problem Sachin Shah Excel Discussion (Misc queries) 1 August 17th 05 06:26 AM
Problem With Reference Update Egon Excel Worksheet Functions 17 July 16th 05 05:45 AM
Copy an Drag cell Formula Problem Nat Excel Discussion (Misc queries) 1 June 20th 05 03:24 PM
Freeze Pane problem in shared workbooks JM Excel Discussion (Misc queries) 1 February 1st 05 12:04 AM


All times are GMT +1. The time now is 07:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"