Prev: Extracting a number from a multiline-entry
Next: Complex Excel formula help
From: Glenn on 22 Mar 2010 11:58
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
From: Gord Dibben on 22 Mar 2010 12:08
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 <Glenn(a)discussions.microsoft.com>
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

First  |  Prev  | 
Pages: 1 2
Prev: Extracting a number from a multiline-entry
Next: Complex Excel formula help