VBA script help..Please !!!!

[Guest]

I have been given the script below by Ron De Bruin - and I can't thank him
enough, however I'm still scratching my head as it doesn't EXACTLY what I
want.

I want ALL the built in command bars/toolbars etc that are shown in a normal
excel sheet to be hidden when I open my 'Log' - EXCEPT my own personal
toolbar named 'LOG' and the 'Worksheet Menu Bar'.

2 problems arise from the script below :-

1) The Worksheet Menu Bar is displayed, but my 'LOG' toolbar is not.
2) If I open another seperate excel sheet, all the same commands have been
copied to this new excel workbook, whereas I want all the normal toolbars etc
showing.

here's the script :-

Private Sub workbook_Beforeclose(Cancle As Boolean)
On Error Resume Next 'in case toolbar is absent
Application.CommandBars("log").Delete

End Sub


Private Sub Workbook_Activate()
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.Name <> "Worksheet Menu Bar" And Cbar.Name <> "log" Then
Cbar.Enabled = True
End If
Next
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False

End With
End Sub

Private Sub Workbook_Deactivate()
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
Cbar.Enabled = True
Next
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
End Sub

Can anybody help with this....pls pls pls !!!!!!!!!!!

 

[Dave Peterson]

Maybe it's as simple as changing this:

Cbar.Name <> "log"
to
lcase(cbar.name) <> "log"

When VBA compares text, it's case sensitive (unless you do something special).

 

[Dave Peterson]

And this doesn't look correct, either:

Private Sub workbook_Beforeclose(Cancle As Boolean)

Maybe ....

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 

[Guest]

'thanks Dave, will give it a try and see how I get on, one other question to
try and make this a bit easier for me.
If I recorded a macro to close all the toolbars etc that I wanted and to
show my worksheet exactly how I wanted it to look, how would I get this
'macro' to run on openeing the worksheet and then how to I get all the menus
to open again when it has been closed.
Also wher shud I place this code in the VB editor - in the 'Thisworkbook'
part?

thanks Dave and sorry for dumb questions but I'm a little novice at VB etc.

 

[Dave Peterson]

That kind of code (hide toolbars when you open a workbook) goes into the
ThisWorkbook module.

If you were hiding the toolbars based on the sheet in a workbook, you could have
the code in the worksheet module that should show this behavior.

 

[Guest]

sorry Dave
but how can I get excel to perform this macro automaticaly once I open the
sheet ??
thanks again
Anthony

 

[Dave Peterson]

If you mean open the workbook, then excel will do it automatically*

* assumes that you have the code in ThisWorkbook and events are enabled and
macros are enabled.

sorry Dave
but how can I get excel to perform this macro automaticaly once I open the
sheet ??
thanks again
Anthony