How to hide all the worksheets in Excel

[adam]

Hi Anyone Following is a code that Un hides all the hidden worksheets from a workbook.

Code: Select all

Sub UnhideSheets()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = True
Next
End Sub

What I want to know is whether all the sheets could be hidden in an excel workbook by modifying the above formula. I tried by modifying. But failed.

As I'm having a form as a home page, I want only the form to be visible when I open the workbook and the form with command buttons to be the source to open the hidden sheets as per my need.

I hope I have made my question clear.

Thanks in advance.

Regards
Adam

[HansV]

You cannot hide ALL worksheets - at least one sheet must be visible. You could use a sheet with only some instructions to the user, a copyright message or similar for this purpose. Let's say that you name this sheet Introduction. You can then modify the code like this:

Code: Select all

Sub HideSheetsExceptOne()
  Dim ws As Worksheet
  For Each ws In Worksheets
    If Not ws.Name = "Introduction" Then
      ws.Visible = xlSheetHidden
    End If
  Next ws
End Sub

[adam]

Thank you for the help & recommendation.

Ill try to follow accordingly.

Regards
Adam

[Rudi]

In addition to Hans's advice, you can add to Hans's code that the macro hides features such as grid-lines, column and row headers, the formula bar and even the status bar. This makes the interface look different from the default Excel interface and more like an EIS (Enterprise Information System).

[adam]

Hi Rudi,

How to do that? if I may ask

[Rudi]

Hi Rudi,

How to do that? if I may ask

You can record a macro that switches off those areas of the interface.

Similar to:

Code: Select all

Sub HideInterface()
    ActiveWindow.DisplayGridlines = False
    Application.DisplayFormulaBar = False
    ActiveWindow.DisplayHeadings = False
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Please review and respond to the form…"
    Range("B3").Select
End Sub

[Leif]

How to do that? if I may ask

One of the best ways to learn how to do things like that is to record a macro, and view the results.

To turn off things like the grid lines, scroll bars, etc., start the recorder, then from Options deselect what you want:

x.jpg

(The above is the Options window in Excel 2002)
Stop the recorder, the view the result in VBA.

You'll then get something like:

Code: Select all

    With ActiveWindow
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
        .DisplayWorkbookTabs = False
    End With
    With Application
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With

[adam]

Thanks for the help guys. I really appreciate it.

regards
Adam