Results 1 to 7 of 7

VBA KeyPress Event Help

This is a discussion on VBA KeyPress Event Help within the Excel Questions forums, part of the Question Forums category; Hello All, I have several textboxes within a userform and was wondering if there was some way to utilize one ...

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Posts
    99

    Question VBA KeyPress Event Help

    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.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: VBA KeyPress Event Help

    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.
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Jun 2009
    Posts
    99

    Default Re: VBA KeyPress Event Help

    Quote Originally Posted by Norie View Post
    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.

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: VBA KeyPress Event Help

    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.
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Jun 2009
    Posts
    99

    Default Re: VBA KeyPress Event Help

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

  6. #6
    New Member
    Join Date
    Oct 2011
    Posts
    1

    Default Re: VBA KeyPress Event Help

    Thank You very Much

  7. #7
    Board Regular
    Join Date
    Jan 2012
    Location
    Mumbai
    Posts
    439

    Default Re: VBA KeyPress Event Help

    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.
    Susheelkumar Singh
    --------------------
    Excel,Access - 2007
    Success comes in can's not can't

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com