Hiding Parts of Excel

N

Neil

I would like my spreadsheet to hide parts of excel when a user opens it. Ideally it would hide almost everything, scrollbar, status bar, formula bar, sheet tabs etc. Can this be done with code and what code would it be?
 
R

Rob van Gelder

I've seen solutions which do it, with problems.

This example I've seen sometimes wasn't able to restore the settings, so
when the user loaded Excel they got a menu bar and nothing else. Helpdesk
calls galore.

It's probably possible to do it properly though.
I recommend that you resist fighting Excel and try to work with it. Most
users are comfortable with the interface and standard toolbars.


One way using FullScreen:

Sub test()
With Application
.DisplayFullScreen = True
.CommandBars("Full Screen").Visible = False
End With
With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
End Sub


Here's another way (test destroys, test2 restores)
Sub test()
Dim cmd As CommandBar

With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
.DisplayScrollBars = False
For Each cmd In .CommandBars
If cmd.Visible And Not cmd.Name =
..CommandBars.ActiveMenuBar.Name Then
cmd.Controls(1).Tag = "Restore Me"
cmd.Visible = False
End If
Next
End With

'stuff that you gets saved with the workbook
With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
End Sub

Sub test2()
On Error Resume Next
Dim cmd As CommandBar
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
.DisplayScrollBars = True
For Each cmd In .CommandBars
If cmd.Controls(1).Tag = "Restore Me" Then
cmd.Controls(1).Tag = ""
cmd.Visible = True
End If
Next
End With
End Sub




--
Rob van Gelder - http://www.vangelder.co.nz/excel


Neil said:
I would like my spreadsheet to hide parts of excel when a user opens it.
Click to expand...
Ideally it would hide almost everything, scrollbar, status bar, formula bar,
sheet tabs etc. Can this be done with code and what code would it be?
 
R

Rob van Gelder

I suspect that you want to make Excel look like just any other application.

I strongly recommend that you do not hide parts of Excel for this purpose.
Don't fight Excel's interface. Work *with* it - the features are useful to
users.

Here's your answer:

Private Sub Workbook_Open()
'code here to hide Excel bits
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'code here to unhide Excel bits
End Sub
 
Top