Catch that keypress!

[Alan Nicoll]

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. ray:

Thanks.

 

[Cbrine]

Take a look at the on line help for the onkey method. This should let you do what you want with the keypress.

 

[Alan Nicoll]

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.

 

[Cbrine]

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.

 

[Alan Nicoll]

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

YesShow
Help.TextBox1.SetFocus
Hide
'MsgBox "Press [Enter] to return to help screen"

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

Show
NoShow
End Sub

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

KeyPress = True

End Sub

 

[Alan Nicoll]

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.

 

[Cbrine]

Alan,
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?

 

[Alan Nicoll]

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.

 

[Marc]

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()
UserForm.show vbModeless
End Sub

 

[Alan Nicoll]

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.

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.