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 !!!!!!!!!!!

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).



Anthony wrote:

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

And this doesn't look correct, either:

Private Sub workbook_Beforeclose(Cancle As Boolean)

Maybe ....

Private Sub Workbook_BeforeClose(Cancel As Boolean)




Anthony wrote:

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

'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" wrote:

And this doesn't look correct, either:

Private Sub workbook_Beforeclose(Cancle As Boolean)

Maybe ....

Private Sub Workbook_BeforeClose(Cancel As Boolean)




Anthony wrote:

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

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.



Anthony wrote:

'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" wrote:

And this doesn't look correct, either:

Private Sub workbook_Beforeclose(Cancle As Boolean)

Maybe ....

Private Sub Workbook_BeforeClose(Cancel As Boolean)




Anthony wrote:

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


--

Dave Peterson

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

"Dave Peterson" wrote:

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.



Anthony wrote:

'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" wrote:

And this doesn't look correct, either:

Private Sub workbook_Beforeclose(Cancle As Boolean)

Maybe ....

Private Sub Workbook_BeforeClose(Cancel As Boolean)




Anthony wrote:

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


--

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.



Anthony wrote:

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

"Dave Peterson" wrote:

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.



Anthony wrote:

'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" wrote:

And this doesn't look correct, either:

Private Sub workbook_Beforeclose(Cancle As Boolean)

Maybe ....

Private Sub Workbook_BeforeClose(Cancel As Boolean)




Anthony wrote:

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


--

Dave Peterson


--

Dave Peterson