Activating a macro with the 'Enter' key 2

[newguy86]

What I have is a userform that only has one button. When the user fills out the form and presses the 'Enter' key I want one of my macro's to run automatically. I have looked into this online but all I can find is examples for Excel, and the Microsoft VBA Help page is not helping.

Any suggestions?

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[GingerR]

What is the code behind your button?
When you add a button to the form with the wizard, one of the choices to run a macro. Try that, see the code it writes for you and see if that helps.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[kjv1611]

Travis,

Are you saying you want the code only to run if the user presses the <Enter> button on their keyboard, or are you saying you want the code to run if the user clicks the button OR presses <Enter>?

If you want to specifically deal with the <Enter> button and that only, then you need to look at On KeyPress, I believe.. If not, then it's something along those lines. I forget, as I've not personally used it in a while.

In that code, you'd need to use a conditional statement to check for if the key pressed was the <Enter> key. I can find the code for this if need be... but it may not be today..

--

"If to err is human, then I must be some kind of human!" -Me

 

[newguy86]

To answer both of your questions, the code that is behind my button is what I need run when the 'Enter' key is pressed. I have the search function setup under the OnClick event so that when someone clicks the button the search function runs, but I also want the code to run when someone hits 'Enter' while the form is open.

kjv1611,
Can you find me the code for what you are talking about because I looked at the OnKeyPress event syntax in the help area and I cannot figure out how to define the 'Enter' key and then have VBA run the macro when the key is pressed?

Thanks for all of your help!

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[kjv1611]

I think I just remembered part of it.. Tada! [LOL]

Do a help search on KeyCode, and see if that gets what you need..

Basically, what you need to do is trap the KeyCode... and you may need to do this in the Form's OnCurrent event if the other doesn't work..

--

"If to err is human, then I must be some kind of human!" -Me

 

[newguy86]

I tried "KeyCode" and nothing came back.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[kjv1611]

Here's some code that I found which I used before, and it worked quite well (it was a few years ago, so if you're using Office 2007, bear in mind there could be a piece or two that has to be tweaked):

Private Sub cmbComments_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 9 Or KeyCode = 13 Then
        If Shift = 0 Then
            bolCommentAfterUpdateRun = True
            Call cmbComments_AfterUpdate
        End If
    ElseIf KeyCode = vbKeyDown Then
        cmbComments.Dropdown
    Else
        bolCommentAfterUpdateRun = False
    End If
End Sub

Here's a public function I set up (probably with help at Tek-Tips:

Option Compare Database
Option Explicit

'This code takes any 1-12 digits entered as meaning the first day of the associated month,
'and handles any errors in the user inputing an alpha character as apposed to numeric.
    Public Sub DateEdit(KeyCode As Integer, Shift As Integer, ctl As Control)
    If KeyCode = 9 Or KeyCode = 13 Then
        If Shift = 0 Then
            If ctl.Text = vbNullString Then
                If InStr(ctl.Properties("Name"), "Place") Then
                    MsgBox "Please enter the correct date.", vbOKOnly, "No Date Entered"
                    Forms!frmAudit.bolSetFocus = True
                Else 'If InStr(ctl.Pages, "Name") Then
                    Forms!frmAudit.bolSetFocus = False
                End If
            Else
                Forms!frmAudit.bolSetFocus = False
                If InStr(ctl.Text, "/") = 0 And InStr(ctl.Text, "-") = 0 Then
                    If IsNumeric(ctl.Text) Then
                        If ctl.Text >= 1 And ctl.Text <= 12 Then
                            ctl.Text = DateSerial(VBA.Year(Date), ctl.Text, 1)
                        End If
                        Forms!frmAudit.bolSetFocus = False
                    Else
                        MsgBox "Please enter the date in a numeric format.", vbOKOnly, "Invalid Format."
                        ctl.Undo
                        ctl = vbNullString
                        Forms!frmAudit.bolSetFocus = True
                    End If
                End If
            End If
    End If
    End If
End Sub

And called it with:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Code for setting single digit dates to full dates (1 = 01/01/[CurrentYear])

Private Sub bpsDueDate_KeyDown(KeyCode As Integer, Shift As Integer)
    Call DateEdit(KeyCode, Shift, bpsDueDate)
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[newguy86]

I just tried the first set of code that you gave me and it worked. Here is what I did:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then
        If Shift = 0 Then
            [name of macro here]
        End If
    End If
End Sub

I put this into every object in the form and even added it to the form itself so that no matter what object had the focus, or if no object had the focus, whenever the user hit 'Enter' the search function would run.

Thanks everyone for all of your help.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[PHV]

BTW, why not simply set the Default property of the CommandButton to True ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bohemian9485]

I agree with what PHV said, just set the commanbutton's default property to True, and when the user press the enter key, the button's click event will be activated automatically.

 

[newguy86]

That's exactly what I was looking for. Thank you PHV.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!