Code needs a little modification.

  • Thread starter Thread starter Marvin Hlavac
  • Start date Start date
M

Marvin Hlavac

I have a code that hides Menu Bar:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CommandBars("Worksheet Menu Bar").Enabled = False
End Sub

However, one little inconvenience is that once I open a worksheet with this
code and close it, all other workbooks I open after that will have no Menu
Bar either. I have to open again the workbook that contains the code and
change the "False" to "True". Is there a way that it would be done
automatically before I close the workbook that contains the code so I don't
have to do it manually every time I need Menu bar on some other workbook?

Thanks in advance.
 
Marvin,

Why not just add a corresponding Deactivate event?

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CommandBars("Worksheet Menu Bar").Enabled = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Marvin, when you modify toolbars in the Open event, you should always
restore them in the BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Worksheet Menu Bar").Enabled = True
End Sub

The problem with the BeforeClose event is that if the close is cancelled by
the user, the event still fires. If this is a concern, you should use
modifications similar to what I gave you for the fixed window size problem.
If you don't want to bother, the worst that will happen is that the workbook
will remain open with the menu bar restored.
 
Hi Bob & Vasant,

I just got home from a little x-mas shopping and the first thing I did was
to try both of your suggestions. Both do exactly what I needed and I thank
you. I'd should ask Santa to get me a good book on VBA so I don't have to
just copy & paste but I could actually create my own stuff :-)

Is there any difference between: Workbook_BeforeClose and
Workbook_WindowDeactivate ?

Thanks Bob and Vasant very much.
 
Marvin,

The obvious is that BeforeClose is fired when you close a workbook,
Deactivate is when you deactivate the window associated with that
workbook<vbg>.

The Deactivate one fires when you switch from one workbook to another, the
BeforeClose only fires if you close the workbook.

One thing I didn't test originally, but I have now, is whether Deactivate
fires when you close the workbook - it does.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
The Deactivate one fires when you switch from one workbook to another, the
BeforeClose only fires if you close the workbook.
Click to expand...


For the above reason I will use the Deactivate one even though most of the
time it would make no difference for me. Again thank you both Bob and Vasant
very much for your help with this.
 
Back
Top