How to determine which sheets appear on opening a workbook.
 

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

How to determine which sheets appear on opening a workbook.
 

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

--


Gary Keramidas
Excel 2003


"Glenn" wrote in message
...
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

How to determine which sheets appear on opening a workbook.
 

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.


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Glenn" wrote:

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

How to determine which sheets appear on opening a workbook.
 

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.

"ryguy7272" wrote:

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.


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Glenn" wrote:

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

How to determine which sheets appear on opening a workbook.
 

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



"Glenn" wrote:

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.

"ryguy7272" wrote:

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.


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Glenn" wrote:

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

How to determine which sheets appear on opening a workbook.
 

Surperb. It works a treat. Many thanks

Glenn

"Gary Brown" wrote:

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



"Glenn" wrote:

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.

"ryguy7272" wrote:

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.


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Glenn" wrote:

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

How to determine which sheets appear on opening a workbook.
 

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


On Mon, 22 Mar 2010 08:08:01 -0700, Glenn
wrote:

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