Solved: VBA to Hide Toolbar

[Andybuck86]

Hi all,

After a very successful response to my first forum post earlier I'm back again with another question.

I am looking to create a VBA that can disable all toolbars in a spreadsheet.

I have created a spreadsheet which will be saved on a network drive and accessed by over 100 people to use as a template timesheet. I have created a button to save the document based on the information in some cells and request a password for end user input (thanks to this very forum )

Now what I really don't want to happen is for the end user to then close the spreadsheet and click yes to 'save changes' as this will overwrite my template

Therefore I need a VBA to hide all toolbars including (if possible) the exit button. I literally only want to be able to see the cells.

Of course I will also need this VBA to automatically hide the toolbars when entering the spreadsheet, and make them reappear when closing the spreadsheet!

Is this possible? I haven't a clue where to start

Any help is much appreciated.

Thanks in advance


Andy

[aysam]

Peace to you all
Andy
Try this on a sample workbook not the main one to get assured
Use this in a standard module

Sub Auto_Open()
On Error Resume Next
Sheets("Main").Activate
Range("A1").Select
With Application
    .DisplayFullScreen = True
    .CommandBars("Full Screen").Visible = False
    .CommandBars("Worksheet Menu Bar").Enabled = False
End With
End Sub

and use this in This workbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
    .DisplayFullScreen = False
    .CommandBars("Full Screen").Visible = False
.CommandBars("Worksheet Menu Bar").Enabled = True
End With
End Sub

then tell me what happens !!!!
aysam

[Ken Puls]

Aysam,

I haven't tested your code, as I don't have Excel 2003 available right now, but can I make a suggestion? Set up a single routine that controls the view and call it from the necessary places:

Standard Module
---------------

[VBA]
Public Sub AllowMenus(bMode as Boolean)
With Application
If bMode = True then
.DisplayFullScreen = False
Else
.DisplayFullScreen = True
End If
.CommandBars("Full Screen").Visible = bMode
.CommandBars("Worksheet Menu Bar").Enabled = bMode
End With
End Sub
[/VBA]

ThisWorkbook Module
-------------------
[VBA]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call AllowMenus(True)
End Sub
Private Sub Workbook_Open()
Call AllowMenus(False)
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Call AllowMenus(False)
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Call AllowMenus(True)
End Sub
[/VBA]

This should make it a lot easier to maintain later.

[aysam]

Peace to you all
Ken
Thanks so much for the advice. I should try it to learn .

[Andybuck86]

Aysam,

I haven't tested your code, as I don't have Excel 2003 available right now, but can I make a suggestion? Set up a single routine that controls the view and call it from the necessary places:

Standard Module
---------------

[vba]
Public Sub AllowMenus(bMode as Boolean)
With Application
If bMode = True then
.DisplayFullScreen = False
Else
.DisplayFullScreen = True
End If
.CommandBars("Full Screen").Visible = bMode
.CommandBars("Worksheet Menu Bar").Enabled = bMode
End With
End Sub
[/vba]

ThisWorkbook Module
-------------------
[vba]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call AllowMenus(True)
End Sub
Private Sub Workbook_Open()
Call AllowMenus(False)
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Call AllowMenus(False)
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Call AllowMenus(True)
End Sub
[/vba]

This should make it a lot easier to maintain later.

Hi - thanks very much for everyone's responses.

I have tried both sets of codes. Both of which have put the worksheet (and now all of my worksheets) in full screen mode.

However - it does not seem to exit the full screen mode upon exiting the sheet which is what I need.

Have I done something wrong?

Thanks again


Andy

[Andybuck86]

Hi - thanks very much for everyone's responses.

I have tried both sets of codes. Both of which have put the worksheet (and now all of my worksheets) in full screen mode.

However - it does not seem to exit the full screen mode upon exiting the sheet which is what I need.

Have I done something wrong?

Thanks again


Andy

Apologies - I have now figured it out. I was entering the 2nd VBA into the sheet1 module rather than the workbook module!!

Thanks again for everyone's help. It's working like a charm now

[Ken Puls]

Andy, make sure you only have the Auto_Open OR the Workbook_Open code in your workbook. It will be painful to maintain if you have both as they both fire at startup. (That would be why you full screened on going in, but nothing else happened.)

I'd recommend removing the Auto_Open, as the rest all work in sync with each other, and that way you only have one to maintain.

Cheers,

[Andybuck86]

Andy, make sure you only have the Auto_Open OR the Workbook_Open code in your workbook. It will be painful to maintain if you have both as they both fire at startup. (That would be why you full screened on going in, but nothing else happened.)

I'd recommend removing the Auto_Open, as the rest all work in sync with each other, and that way you only have one to maintain.

Cheers,

Hi Ken,

Thanks for the advice. I believe I only have the Auto_Open code in my workbook.

One more question sorry! - This code hides everything other than the close, minimise and restore buttons.....which is great.

But - I would also like to be able to disable the 'save document' prompt upon closing the window.

Is there a way I can build this into the code already provided?

I have tried the following code but with no luck:

[VBA]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.Saved = True
Cancel = True
SaveAsUI = False
End Sub
[/VBA]

Thanks again for any help


Andy

[Ken Puls]

No problem. Just make sure the Auto_Open macro only reads as follows:

[vba]Call AllowMenus(True)[/vba]

With regards to the other, this is untested, but try this:
[vba]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
ActiveWorkbook.Saved = True
Application.EnableEvents = True
End Sub [/vba]

[Andybuck86]

No problem. Just make sure the Auto_Open macro only reads as follows:

[vba]Call AllowMenus(True)[/vba]

With regards to the other, this is untested, but try this:
[vba]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
ActiveWorkbook.Saved = True
Application.EnableEvents = True

End Sub

[/vba]

Ken,

I re-read your original post and finally understood what you mean! I have removed the Auto_Open as originally suggested. Works a treat -thank you.

I have only recently started playing with VBA and so at the moment I only have a very basic understanding.

Thanks for the disable password VBA but I can't get this to work.

My problem may be that I'm not sure where to place the code you have provided. Obviously I already have VBA in my workbook module. I have tried your code at the start and end of the code I already have but nothing seems to happen. I.e. I still get the option to save the workbook when exiting.

Thanks again


Andy

[Andybuck86]

Ken,

I re-read your original post and finally understood what you mean! I have removed the Auto_Open as originally suggested. Works a treat -thank you.

I have only recently started playing with VBA and so at the moment I only have a very basic understanding.

Thanks for the disable password VBA but I can't get this to work.

My problem may be that I'm not sure where to place the code you have provided. Obviously I already have VBA in my workbook module. I have tried your code at the start and end of the code I already have but nothing seems to happen. I.e. I still get the option to save the workbook when exiting.

Thanks again


Andy

I have found a solution to this now by searching the forums. The following seems to work for me in the workbook module

[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = Not Cancel
End Sub
[/VBA]

Thanks again for everyone's help!!