Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding Sheets BeforeSave (Excel 2000)

    Hi All, any help on this would greatly be appreciated ...

    I have 10 worksheets in a workbook. Two of the worksheets will always be visible. The other eight need to be re-hidden when the workbook is saved. Further, at any given time I may have 2-10 of the worksheets visible. A worksheet becomes visible when data-entry for that sheet is needed. In other words, I would like to have only two worksheets visible when the workbook is opened. Help!
    --cat
    hmmm ...

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hiding Sheets BeforeSave (Excel 2000)

    How about something like this:

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    ActiveWindow.SelectedSheets.Visible = False
    End Sub</pre>


    Add all the appropriate sheet names to the array

    In your code just use:
    <pre>worksheets("sheet3").visible = true</pre>


    when you want a particular one visible

    Steve

  3. #3
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Sheets BeforeSave (Excel 2000)

    Thanks, that works if all the possible worksheets are open. I get an error if even one of them is already hidden. My problem is that I don't know which ones are visible when the worksheet is saved. Any ideas?
    -cat

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Sheets BeforeSave (Excel 2000)

    This can help you

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wshtItem As Excel.Worksheet
    Dim wshtFirst
    Sheets("FirstSheet").Visible = True
    Sheets("FirstSheet").Select

    wshtFirst = "FirstSheet"

    For Each wshtItem In ThisWorkbook.Worksheets
    If Not UCase(wshtItem.Name) = UCase(wshtFirst) Then
    wshtItem.Visible = xlSheetHidden
    End If
    Next wshtItem

    Set wshtItem = Nothing

    End Sub

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hiding Sheets BeforeSave (Excel 2000)

    Try this
    This will HIDE all the worksheets EXCEPT sheet1 and sheet2 (change names as appropriate). It shouldn't matter which are hidden and which are not.

    <pre>Option Explicit
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wks As Worksheet
    For Each wks In Worksheets
    If wks.Name = "Sheet1" Or _
    wks.Name = "Sheet2" Then
    wks.Visible = xlSheetVisible
    Else
    wks.Visible = xlSheetHidden
    End If
    Next wks
    End Sub</pre>


    Steve

  6. #6
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Sheets BeforeSave (Excel 2000)

    WONDERFUL ... you guys are great! That worked perfectly
    --cat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •