Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening a list of workbooks with vba, similar to going through sheets in a workbook? | Excel Programming | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
How to make the opening of a workbook conditional upon the opening of another workbook | Excel Programming | |||
How to make opening of workbook conditional of opening of another workbook | Excel Programming |