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