combobox

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 -

VB Code:

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 Sub

Now, 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 [vbcode][/vbcode] tags for more clarity. - Hack

Moved to Office Development

Quote:

---

Originally Posted by mikeymay

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.

Quote:

---

Originally Posted by mikeymay

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.

VB Code:

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...

VB Code:

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