Worksheet Specific Userform

[animas]

I have created a MODELESS userform which I want to make some worksheets specific only.

I was trying the following code inside a particular worksheet (mySHEET) module

Private Sub Worksheet_Activate()
 frmMenu.Show vbModeless
End Sub

Private Sub Worksheet_Deactivate()
    Unload frmMenu
End Sub

Problem is, when user opens mySHEET(frmMenu is shown) and without deactivating mySHEET, he opens another workbook window, frmMenu is still showed. This is unexpected.

How do I solve the issue?

 

[Richard Schollar]

Hi

You need to dimension a variable as your form and refer to that:

'at top of worksheet module:
 
Dim myFrm As frmMenu
 
Private Sub Worksheet_Activate()
Set myFrm = New frmMenu
myFrm.Show vbModeless

End Sub

Private Sub Worksheet_Deactivate()
Unload myFrm
End Sub

 

[animas]

Having following error for "Unload myFrm"

Runtime error 91
object variable or withblock variable not set.

 

[Richard Schollar]

Had you closed your userform by other means (eg clicking on its red cross)? Just plonk an On Error Resume Next before the UnLoad line in the Deactivate event to stop this causing your code to error out.

 

[animas]

frmMenubar module code

Private Sub cmdUnprotect_Click()
    UnprotectWorksheet ()
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please do not close."
  End If
End Sub

Private Sub UserForm_Activate()
    '//set form position
    With frmMenubar
        .Top = Application.Top + 170 '< change 125 to what u want
        .Left = Application.Left + 30 '< change 25 to what u want
    End With
End Sub

mySHEET module code

'Dim myFrm As frmMenubar

Private Sub Worksheet_Activate()
    Set myFrm = New frmMenubar
    myFrm.Show vbModeless
End Sub

Private Sub Worksheet_Deactivate()
    '// unload userform
    On Error Resume Next
    Unload myFrm
End Sub

This code is also not executing frmMenubar module codes in UserForm_Activate sub.

 

[Richard Schollar]

I would get into the habit of requiring variable declaration in your projects by placing Option Explicit at the top of your modules - this would have indicated to you that you have an undeclared variable in the Deactivate event proc.

Uncomment the

'Dim myFrm As frmMenu

and the code should work.

 

[animas]

option explicit is with my file code. Sorry for not including here. I tested the commented line with uncomment. No changes. What I did for a quick fix is I uloaded the form in ThisWorkbook module Workbook_WindowDeactivate event. Though one issue remains, when user comes back to worksheet, it is still unloaded. I wish there was a Worksheet_WindowActivate event!
I can hardcode the sheet name and show the form in Workbook_WindowActivate event, which I am not willing to, since worksheet might be renamed or copied within workbook.

 

[Richard Schollar]

It does work fine for me with what I originally gave (and with the addition of the On Error Resume Next) so I would reconfirm what you are seeing. How are you deactivating the worksheet? is it via navigating to another sheet, or clicking on another program?

 

[animas]

How are you deactivating the worksheet? is it via navigating to another sheet, or clicking on another program?

I just used the worksheet_deactivate event as mentioned in my previous post.

 

[Richard Schollar]

No, that is the event code that runs when the worksheet is deactivated - I was asking how you actually deactivate the sheet? If you are doing so by eg clicking on another program in the taskbar, then the deactivate even doesn't fire. If you are doing so by clicking on another sheet, or another open workbook then it should fire.