VBA KeyPress Event Help

business_analyst

Board Regular
Joined
Jun 5, 2009
Messages
99
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:
Code:
textbox1_KeyPress(byval KeyAscii as MSForms.ReturnInteger) _
Handles textbox2_KeyPress, textbox3_KeyPress
However I am getting an error when I try this.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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:

Code:
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. :(
 
Upvote 0
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
Code:
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.
Code:
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.:)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top