combobox

[mikeymay]

I have a userform which enables the user to input various items of data. The userform also has a combobox whose 'rowsource' is dependant on one of the other criteria input using the userform.

I can set up the rowsource using hidden worksheets on excel, but ideally I would like to specify the 'rowsource' within the code.

I have following code to select what appears in combobox -
Private Sub ComboBox1_Click()
If Numeric_Amount < 500 Then
If Numeric_Amount = 0 Or Numeric_Amount = "" Then
MsgBox ("An Amount needs entering before a level of authorisation can be selected.")
Else
Auth = 1
Else
If Numeric_Amount >= 500 And Numeric_Amount < 2000 Then
Auth = 2
Else
Auth = 3
End If
End If
End If
Select Case Auth
Case 1
Auth_Name.AddItem "Name1"
Auth_Name.AddItem "Name2"
Auth_Name.AddItem "Name3"
Auth_Name.AddItem "Name4"
Auth_Name.AddItem "Name5"
Case 2
Auth_Name.AddItem "Name1"
Auth_Name.AddItem "Name2"
Auth_Name.AddItem "Name3"
Auth_Name.AddItem "Name4"
Case 3
Auth_Name.AddItem "Name1"
Auth_Name.AddItem "Name2"
Auth_Name.AddItem "Name3"
Case Else
MsgBox ("An amount needs entering before level of Authorisation can be selected.")
End Select
End SubNow, I would expect that when the drop down combobox is selected on the userform this sub will kick in but it doesn't. This works with radio buttons, etc, but not this combobox.

Any help with the above would be greatly appreciated.










Edit: Added tags for more clarity. - Hack

[Hack]

Moved to Office Development

[DKenny]

I would expect that when the drop down combobox is selected on the userform this sub will kick in but it doesn't. This works with radio buttons, etc, but not this combobox.
You should use the _Change event instead of the _Click event.


I can set up the rowsource using hidden worksheets on excel, but ideally I would like to specify the 'rowsource' within the code.
If you have the data in a range you could use the following sub to loop through that range a specific number of times and add the values to the 2nd combobox.


Sub FillBox2(RowCount As Integer)
Dim MyRange As Range
Dim CellNum As Integer

'Reset the 2nd Combobox
Auth_Name.Clear

'The range containing the list items for the Auth_Name CB
'You will need to prefix the range with a worksheet name, if the range is on a seperate sheet
Set MyRange = Range("your Range Name here")

'Loop through the range
For CellNum = 1 To RowCount
'Add the item
Auth_Name.AddItem MyRange.Cells(CellNum, 1)
Next CellNum

'Select the 1st possible venue
Auth_Name.ListIndex = 0

Set MyRange = Nothing
End Sub


Then your Select Case statement becomes...

Select Case Auth
Case 1
FillBox2 5
Case 2
FillBox2 4
Case 3
FillBox2 3
Case Else
MsgBox ("An amount needs entering before level of Authorisation can be selected.")
End Select