VBA KeyPress Event Help

[business_analyst]

Hello All,

I have several textboxes within a userform and was wondering if there was some way to utilize one KeyPress method for all of these, rather than having to put the code within each textbox KeyPress event. I read some where about using Handles at the end of KeyPress:

textbox1_KeyPress(byval KeyAscii as MSForms.ReturnInteger) _
Handles textbox2_KeyPress, textbox3_KeyPress

However I am getting an error when I try this.

 

[Norie]

I've never heard of Handles being used in VBA, so I don't quite know where you read that.

There may however be other ways to work with multiple controls, it really depends on what you are actually doing.

 

[business_analyst]

I've never heard of Handles being used in VBA, so I don't quite know where you read that.

There may however be other ways to work with multiple controls, it really depends on what you are actually doing.

Well, I'm actually trying to prevent certain textboxes on a form from allowing letters to be entered. Basically so that the user can only enter numbers. I am currently using this KeyPress event:

Private Sub txt1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii < 48 Or KeyAscii > 57 Then
        KeyAscii = 0
    End If
End Sub

It works just fine, I was just hoping to not have to paste this into every textbox in my Userform.

 

[Norie]

You could try using a class event, here's an example.

1 Create a userform with multiple textboxes and a commandbutton.

2 Add a class module (Insert>Class), call it CTextboxes and put this code in it

Option Explicit
 
Public WithEvents TextGroup As MSForms.TextBox
 
Private Sub TextGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    If KeyAscii < 48 Or KeyAscii > 57 Then
        KeyAscii = 0
    End If

End Sub
 
'Private Sub TextGroup_Change()
'    If Len(TextGroup.Value) > 0 And Not IsNumeric(TextGroup.Value) Then
'        MsgBox "Only numeric data, thank you"
'        TextGroup.Value = Left(TextGroup.Value, Len(TextGroup.Value) - 1)
'    End If
'End Sub

Note there's a commented out sub in there for the Change event - I don't know why but I prefer to use that rather than something like KeyPress.
3 Put this code in the userform module.

Option Explicit
 
Dim TextBoxes() As New CTextboxes
 
Private Sub CommandButton1_Click()
    Unload Me
End Sub
 
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim I As Long

    I = 1
    
    For Each ctl In Me.Controls

        If TypeName(ctl) = "TextBox" Then
            ReDim Preserve TextBoxes(1 To I)
            Set TextBoxes(I).TextGroup = ctl
            I = I + 1
        End If

    Next ctl

End Sub

Now give the form a whirl and see if it's anywhere near what you are looking for.

 

[business_analyst]

Thanks Norie, that acutally works just perfectly. Much appreciated.

 

[Bluxome79]

Thank You very Much

 

[ssingh75]

Hi Noorie,

Can you please try help me to get the below one if possible?

Hi Norie,

Can you help me to get below one for combobox.

My Combobox contains data like below.

0.41587AmarSingh
0.23.AnandMishra
10.23.Abhay
...
..
.
..

.

I want to select data based on the character not number...
means if i press A then it will go reflect data with contains A..

Hope this i clear..or pls let me know if you need more clarification.

Its quite urgent.