disabling enter key while form is running

[beach]

I have a vba line which responds to a command button being clicked, and then opens a j-walk enhanced dataform add-in, as below:


Private Sub CommandButton1_Click()
Sheets("INDATA").Select
Application.Run "dataform2.xla!ShowDataForm"
Sheets("CURRENT JOBS").Select
End Sub

Wht I want to do is disable the "enter" key while the j-walk form is active, due to the reason that the form closes if you hit enter, rather than button within the form itself, and then renable the enter key when the macro takes me back to the main page (in my case, named "CURRENT JOBS"

There are times when a user will go to the sheet named "INDATA" and needs to be able to use the enter key just as normal, so I dont want to disable it permanently, only while the j-walk form is active


Any help

Thanks

 

[DRJ]

Try something like this.

Private Sub CommandButton1_Click()

    Me.Hide
    Unload Me

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
    Cancel = True

End Sub

Change userform1 to the name of the userform.
change command button 1 to the name of the command button that closes the userform.

 

[beach]

Re: disabling enter key while data form is running

thanks for the first response (jacob), but i cant get into the code for the j-walk data form, (plus I admit, I don't know how and where exactly to add the snippet to my code)

Any other suggestions to disable the enter key, in the code before the j-walk opens, and then perhaps a macro tied to the worksheet I'm returning to (I've done that once before, when you open a worksheet a sheet level auto macro runs), but I need help with the disable/enable syntax.

Thanks, browsers

 

[DRJ]

Try this.

Sub NoEnter()
    Application.OnKey "~", "NoEnter"
    Application.OnKey "{Enter}", "NoEnter"
End Sub

 

[DRJ]

Ohh and use this to re-activate enter

Sub NoEnter() 
    Application.OnKey "~", "" 
    Application.OnKey "{Enter}", "" 
End Sub

 

[beach]

hi again jacob

I have tried the code on a clean work sheet, and it seems to work (I think) at least the data is placed in the cell, and the active cell frame doesnt move down or right... but thats hard to tell if it will actually achieve what I hope it to do running either side of my existing code, activating the data form.

Problem is I don't know how to write it in to include the j-walk form jump action.
Where do the bits you gave me actually go??

HERE?? (disable)
Private Sub CommandButton1_Click()
Sheets("INDATA").Select
Application.Run "dataform2.xla!ShowDataForm" OR HERE ??
AND HERE (the re-enable bit...)
Sheets("CURRENT JOBS").Select OR HERE ???
End Sub

Remember I am a VBA bunny, and the first ten or twelve tries I have got various error messages like "duplicate sub", or "expected ="


Thanks

 

[Chitosunday]

You need only one onkey either "~" or {enter} not both. The first one should follow the sheets like this
Sheets("INDATA").Select
Application.OnKey "~", "NoEnter"


the second one should be
Sheets("CURRENT JOBS").Select
Application.OnKey "~", ""


Note: you should also have this in your code most likely put it next your last end sub

sub NoEnter()
'just a dummy sub
end sub

 

[Chitosunday]

You need only one onkey either "~" or {enter} not both. The first one should follow the sheets like this
Sheets("INDATA").Select
Application.OnKey "~", "NoEnter"


the second one should be
Sheets("CURRENT JOBS").Select
Application.OnKey "~", ""


Note: you should also have this in your code most likely put it next your last end sub

sub NoEnter()
'just a dummy sub
end sub

 

[DRJ]

{Enter} is for the enter key on the numeric key pad
~ is for the enter key on the main keyboard

Try it like this

Private Sub CommandButton1_Click() 
    
    Sheets("INDATA").Select 

    Application.OnKey "~", "NoEnter"  
    Application.OnKey "{Enter}", "NoEnter

    Application.Run "dataform2.xla!ShowDataForm" 

    Application.OnKey "~", "" 
    Application.OnKey "{Enter}", ""
    
    Sheets("CURRENT JOBS").Select 

End Sub

 

[DRJ]

Also make a dummy macro in this case called "NoEnter" like

Sub NoEnter()
End Sub

Otherwise you will get an error that it can't find the macro.