CODEWITHSUNDEEP
excelvba
INOH
INOH

Reputation: 385

Disable Keyboard Input On Userform Excel

I currently have Userform with 79 Comboboxes which are populated with all the same values 1-5. As of now if a user hits a keystroke by mistake i get an error. because after combobox change it creates a calculation because it does not recognize character input. so what i have done now is changed the combobox to fmStyleDropDownList, which works for only adding only items listed in combobox, the problem now is that after combobox change event i do not have a if statement to capture a 0 if is inputed. That would not be a problem if i didnt have about 3000 comboboxes throughout my project, so my question is how can i either hide the item 0 in the combobox (if i remove 0, the calculation automatically places a 0 in the placement of the combobox in order for the calculation to work) or if a can add a control to userform if any of the comboboxes text equals 0 then frm?whatever.show.

 Dim ctrl As Control
 For Each ctrl In Me.Controls
 If TypeOf ctrl Is MSForms.ComboBox Then
    With ctrl
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
        .AddItem "5"
        .AddItem "0"


    End With
End If
Next ctrl

Upvotes: 2

Views: 1808

Answers (1)

MatthewD
MatthewD

Reputation: 6761

Test the key input in the combobox keypress event.

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    'Test for zero being pressed.
    If KeyAscii = 48 Then
        'If we get a zero entered, change it to nothing.
        KeyAscii = 0
    End If
End Sub

Upvotes: 3

Related Questions