How to determine which sheets appear on opening a workbook.

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

Is it possible to determine which sheets appear when a workbook is opened. I
have a workbook with a number of hidden sheets that are updated by me from
time to time and I would like them to start off hidden when the workbook is
opened by say, someone else on the network. I have toyed with the Workbook
Open in VB but to no avail. Many thanks

Glenn
 
if i understand correctly, maybe something like this in the workbook open
event

Private Sub Workbook_Open()
If UCase(Environ("username")) = "GLENN" Then
Worksheets("sheet1").Visible = True
Else
Worksheets("sheet1").Visible = False
End If
End Sub
 
No problem. Hit Alt+F11. Under Project-VBA Project, you will see a small
file named 'ThisWorkbook'. Double-click and copy/paste the following code
into the window that opens:
Private Sub Workbook_Open()
Worksheets("Example").Activate
End Sub

"Example" is the name of the worksheet that you want open when the workbook
opens, so change this to suit your specific needs.
 
Many thanks. Just tried it. It made the sheet I want active but, didn't
make it the only sheet visible which is my ultimate aim.
 
Give this a show Glenn...

Private Sub Workbook_Open()
Dim wks As Worksheet

Worksheets("Sheet1").Activate

For Each wks In Worksheets
If wks.Name <> "Sheet1" Then
wks.Visible = xlSheetHidden
End If
Next wks

End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown
 
Surperb. It works a treat. Many thanks

Glenn

Gary Brown said:
Give this a show Glenn...

Private Sub Workbook_Open()
Dim wks As Worksheet

Worksheets("Sheet1").Activate

For Each wks In Worksheets
If wks.Name <> "Sheet1" Then
wks.Visible = xlSheetHidden
End If
Next wks

End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown
Click to expand...
 
I would hide the sheets before close so's if users disable macros the sheets
will be hidden.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
sht.Visible = xlSheetVeryHidden
Next sht
End Sub

To allow you to see all sheets and edit them.

In a general module...............

Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
Back
Top