Userform activating the sheet where it was initiat

Question

I have a userform with few buttons for different macros to run. At the end of execution of macro it hides the userfrom. I have resablled the close button of userform with the following code:

Private Sub UserForm_QueryClose _
  (Cancel As Integer, CloseMode As Integer)
'   Prevents use of the Close button
    If CloseMode = vbFormControlMenu Then
        hmm
        Cancel = True
    End If
End Sub

hmm hides the userfrom. Now the challenge is suppose there are 3 workbook.

1. Workbook A: The macro enabled workbook in which I have written the code for userform and sub routines

2. Workbook B: Where I press the key combination and this shows the userfrom

3. Workbook C: Where I want to execute same macro with the help of same userfrom

Now when I press key combination (while Workbook C is activated) it activates other workbook (the one where I initiated the userfrom first) and then show the userfrom. Though when I press the some button on userfrom it runs the relevant macro but activates the workbook where the form was initiated first.

I want to keep the same workbook activated where this key combination was pressed to show the userfrom (so it do not go to the previous workbook). Please help

The Code:

This is the key combinantion and has been saved in ThisWorkbook of Microsoft Excel Objects

Private Sub Workbook_Open()
    Application.OnKey "%{s}", "LoadMenu"
End Sub

These procedures are in a code module and loads and hides the userfrom (named as MainMenu)

Private Sub LoadMenu()

MainMenu.Show

End Sub



Sub hideMenu()

MainMenu.Hide

End Sub

This is an example sub routine which is run when we click a button on the userfrom,

Sub highlightYellow()
    selection.Interior.Color = vbYellow
    hideMenu ' This hides menu after the execution
End Sub

Now once i have run the macro on some workbook (by calling the userform first time and by clicking a button on it) for the first time like as I said before, and then when I go to some other workbook and then again press this key combination it takes me back to the first workbook when I used this thing for the first time and when I press the highlighting button it highlights the workbook where I pressed the key combination but at the end do not know why the first workbook automatically becomes activate.

Please refer the below link for the workbook, please open 2-3 new workbooks and then try on them together.

https://www.dropbox.com/s/952lrsrsglt9f6y/Test%20Book.xlsm?dl=0

Answer 1

All userforms as objects. Separate them at every run to have independent instances. Try this

Private Sub LoadMenu()

Dim frmTemp as MainMenu

Set frmTemp = New MainMenu

frmTemp.Show

Unload frmTemp

End Sub