Catch that keypress!

Alan Nicoll

Board Regular
Feb 3, 2004
I'd like to temporarily hide a form at the user's option so my help system can show something on the spreadsheet, then have the user press any key to return to the form.

I know there's a way. Seeking guidance. :pray:


Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Take a look at the on line help for the onkey method. This should let you do what you want with the keypress.
Upvote 0
OnKey doesn't seem to do it. The purpose of this method seems to be to assign special functions to special keys. That is,

Application.OnKey "{ENTER}", "TestProcedure"

will turn my Enter key into a hot key to start TestProcedure. It won't "catch that keypress." Or am I missing something?

Thanks for your help.
Upvote 0
Sorry, I just saw in the help and thought it might do the trick. How about using the KeyDown or KeyUp event on the form? This should do it, as long as you are not unloading the form, just hiding it.
Upvote 0
Still no dice. Can't get the form to recognize the keypress while the form is hidden. I set the focus to the form, which didn't work. Then I created a TextBox and put the focus on that, still didn't work.

Here's my latest try:

Private Sub HideButton_Click()
Dim newHour As Integer, newMinute As Integer, newSecond As Integer

'MsgBox "Press [Enter] to return to help screen"

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
Application.Wait TimeSerial(newHour, newMinute, newSecond)
If Not KeyPress Then GoTo Retry

End Sub

Public Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

KeyPress = True

End Sub

Upvote 0
I neglected to mention... the above code hides the form and then stays in the Retry loop.

YesShow and NoShow are subs to show and hide execution.

Thanks for your efforts.
Upvote 0
I tried a few tests, and it looks like the userform loses focus when it's hidden. I wasn't able to figure a way around this either. Sorry. The only thing I can think of is maybe something with the worksheet_Change event, but that doesn't seem right. There must be a better way. You might be able to move the form off screen or something, instead of hiding it?
Upvote 0
Is there a way to let a form be minimized, like a regular window? Maybe change the mode/modeless property?

My current solution is to reduce the height & width of my help window to a small size and make a "Restore" button visible, but I'd like something more elegant, if it exists.
Upvote 0
What about the HIDE and SHOW methods for the userform?

In addition I found this out on the web:

Sometimes you want to allow users to be able to switch between your form and your spreadsheet by clicking on either one. All you need to do is set the form property of Show Modal to False or you can try this. However this is only for Excel 2000 & above.

Sub myForm() vbModeless
End Sub
Upvote 0
Looks like that would work, though I'll have to suspend program execution until the user responds to the Help form, i.e., make it do what a Modal form is designed to do. :eek:

Reminds me of the days when I spent most of my time in 6809 assembly language trying to make Color Basic work the way I wanted.

Thanks for the input.
Upvote 0

Forum statistics

Latest member

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
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 "".
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