ActiveX Control Return Keypress MsgBox Loop

[Guest]

A simplified version of my problem. Consider a single command button on a
blank worksheet. I want to be able execute the command button Click event
with both the Return key and a mouse click. I want to be able to clear the
message box with both the Return key and a mouse click. If I try to clear the
message box with the Return key, it also executes the command button Click
event again. So using the Return key to clear the message box puts you into
a loop. Any ideas are greatly appreciated.

Thanks, Tom

'In ThisWorkbook
Private Sub Workbook_Open()
Sheet1.Button1.Activate
End Sub

'In Sheet1
Private Sub Button1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Select Case KeyCode
'Return is only key press that we care about
Case vbKeyReturn
Call Button1_Click
End Select
End Sub
Private Sub Button1_Click()
MsgBox "I am stuck in a loop when I use the Return key to clear this message
box" & vbNewLine & "but everything is fine if I use a mouse click to clear
this message box"
End Sub

 

[Guest]

Just two ideas:
1) Deactivate the button after you've clicked it and activate again it after
you've closed the Msgbox.
2) Declare a global boolean variable, say BtnClicked. Start by setting
BtnClicked as False. In your Button1_Click procedure, set BtnClicked to
True. Based on that, conditionally disable parts of the procedure as needed.
And with the message box, if the user clicks OK, reset the boolean variable
back to False.

Hope that helps,
Anony

 

[Guest]

Thanks for the very helpful suggestions. I have tried both of them with a
limited degree of success. I think my problem is that I do not know with
what Event to re-activate the button or reset the boolean variable. Given
the choice, I would like to do this with deactivating and re-activating the
button. I prefer this method because in the actual workbook that I am
dealing with, there are around 20 instances of this problem. Any guidance
about the location and or event for reactivating the button or resetting the
boolean is appreciated.

Regards, Tom

-----------------------
'Attempt to deactivate control
Private Sub Button1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Select Case KeyCode
'Return is only key press that we care about
Case vbKeyReturn
Call Button1_Click
End Select
End Sub
Private Sub Button1_Click()
'Deactivate Button1
Sheet1.Range("A1").Select
msg = MsgBox("Trying with deactivation")
End Sub
'where to re-activate Button1?
-------------------------------
'Attempt to flag with global boolean variable
'Public BtnClicked As Boolean in Module1
Private Sub Worksheet_Activate()
BtnClicked = False
End Sub
Private Sub Button1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If BtnClicked = False Then
Select Case KeyCode
'Return is only key press that we care about
Case vbKeyReturn
Call Button1_Click
End Select
End If
End Sub
Private Sub Button1_Click()
BtnClicked = True
msg = MsgBox("Trying with global boolean variable")
End Sub
'where to reset BtnClicked = False?

 

[Guest]

Sorry it took so long to get to you, you probably already has the problem
figured out by now.
But in case you haven't, I'd reactivate the button after you've cleared the
message box, assuming that was what you were trying to accomplish in the
first place with the return key.