Hiding toolbars, status bar etc.??

dan2

Board Regular
Joined
Mar 26, 2002
Messages
60
Hi all

I am using the following code within sub workbook_open ()

With Application
.Caption = "Store Paperwork"
.DisplayFormulaBar = False
.DisplayStatusBar = False
.DisplayScrollBars = False
.CommandBars("worksheet menu bar").Enabled = False
.CommandBars("chart menu bar").Enabled = False
.DisplayFullScreen = True
.CommandBars("full screen").Enabled = False
End With
With ActiveWorkbook
.Windows(1).Caption = Empty
.Windows(1).DisplayWorkbookTabs = False
.Windows(1).DisplayHeadings = False
.Windows(1).DisplayGridlines = False

and it works fine for a split second until a sheet is selected at the end of the procedure and then some of them turn themselves back on.

Anyone know why this is happening and how I can avoid it please? (this is really starting to bug me)

TIA

Dan.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
My head is starting to hurt now with banging it against the monitor.

Any ideas anyone PLEASE.
 
Upvote 0
With Application
.Caption = "Store Paperwork"
.DisplayFormulaBar = False
.DisplayStatusBar = False
.DisplayScrollBars = False
.CommandBars("worksheet menu bar").Enabled = False
.CommandBars("chart menu bar").Enabled = False
.DisplayFullScreen = True
.CommandBars("full screen").Enabled = False
End With
With ActiveWorkbook
.Windows(1).Caption = Empty
.Windows(1).DisplayWorkbookTabs = False
.Windows(1).DisplayHeadings = False
.Windows(1).DisplayGridlines = False

and it works fine for a split second until a sheet is selected at the end of the procedure and then some of them turn themselves back on.

Anyone know why this is happening and how I can avoid it please? (this is really starting to bug me)

Dan,

From the Excel help for DisplayGridlines and DisplayHeadings

'This property affects only displayed headings'

Not sure about the formula bar though.

Why don't you select the sheet before running the code? By the way I like this code and may nick it!

Regards,
Dan
 
Upvote 0
Dan

I just realised I missed part of the code out so here is the full code. It should work but its not and I know its probably just something small Im missing.

Sub screenset()
Sheets("toolbars").Select
Range("a1:a20").ClearContents
Range("a1").Select
For Each t In Application.Toolbars
If t.Visible = True Then
ActiveCell.Value = t.Name
ActiveCell.Offset(1, 0).Select
t.Visible = False
End If
Next t
Range("a1").Select
With Application
.Caption = "Store Paperwork"
.DisplayFormulaBar = False
.DisplayStatusBar = False
.DisplayScrollBars = False
.CommandBars("worksheet menu bar").Enabled = False
.CommandBars("chart menu bar").Enabled = False
.DisplayFullScreen = True
.CommandBars("full screen").Enabled = False
End With
With ActiveWorkbook
.Windows(1).Caption = Empty
.Windows(1).DisplayWorkbookTabs = False
.Windows(1).DisplayHeadings = False
.Windows(1).DisplayGridlines = False
End With
'Sheets("store paperwork").EnableSelection = xlUnlockedCells
Sheets("store paperwork").Select
Range("a1").Select
End Sub

If you use it, and get it working please let me know where I'm going wrong.

Thanks

Dan.
 
Upvote 0
Dan,

How about this?

Code:
Sub screenset()
Sheets("toolbars").Range("a1:a20").ClearContents

r = 1
Range("a1:a20").ClearContents
Range("a1").Select
For Each t In Application.Toolbars
    If t.Visible = True Then
        Sheets("toolbars").Cells(r, 1).Value = t.Name
        t.Visible = False
        r = r + 1
    End If
Next t

Sheets("store paperwork").EnableSelection = xlUnlockedCells
Sheets("store paperwork").Activate
Range("a1").Select

With Application
    .Caption = "Store Paperwork"
    .DisplayFormulaBar = False
    .DisplayStatusBar = False
    .DisplayScrollBars = False
    .CommandBars("worksheet menu bar").Enabled = False
    .CommandBars("chart menu bar").Enabled = False
    .DisplayFullScreen = True
    .CommandBars("full screen").Enabled = False
End With
With ActiveWorkbook
    .Windows(1).Caption = Empty
    .Windows(1).DisplayWorkbookTabs = False
    .Windows(1).DisplayHeadings = False
    .Windows(1).DisplayGridlines = False
End With
End Sub

HTH,
Dan
 
Upvote 0
Dan

I've worked out whats wrong with it.

The application.displaysetting=false needs to go first in the procedure or for some reason it cancels out the toolbars bit.

Now works fine for me. So if you want to use it do the above.

Dan.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top