Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Really Hide a sheet (2003 sp2)

    greetimgs,
    I have a file where I have hidden a sheet. Within the workbook I have a macro the unhides, copies data to another workbook, then re-hides the sheet. I really need to keep prying eyes from the data. How can I hide the sheet, allow the macro to function, and allow only myself to access the sheet?


    Thanks,
    Brad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Really Hide a sheet (2003 sp2)

    In the code that hides the sheet, set the Visible property of the sheet to xlSheetVeryHidden. If you do this, the sheet won't be listed in the Window | Unhide... dialog. The sheet can only be unhidden using code or in the Properties pane of the Visual Basic Editor. If you set a password on the VBA project (Tools | VBAProject Properties, Security tab), the user won't be able to unhide the sheet.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Really Hide a sheet (2003 sp2)

    This is what I did, however I received an error for this line when the code was executed

    ActiveWindow.SelectedSheets.Visible = xlSheetVeryHidden

    Brad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Really Hide a sheet (2003 sp2)

    You can only set the Visible property for individual sheets, not for a group of sheets.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Really Hide a sheet (2003 sp2)

    Ok, now you lost me....

    How does the above work into what I have?

    Application.ScreenUpdating = False
    Sheets("Data sheet").Visible = True
    Sheets("Data sheet").Select
    Range("A1:E30").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False

    ActiveWorkbook.SaveAs Filename:= _
    "C:Tool Files" & Range("a2") & ".csv", FileFormat:=xlCSV _
    , CreateBackup:=False

    ActiveWindow.Close

    Sheets("Data sheet").Select
    ActiveWindow.SelectedSheets.Visible = xlSheetVeryHidden


    End Sub

    Thanks,
    Brad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Really Hide a sheet (2003 sp2)

    Change the line

    ActiveWindow.SelectedSheets.Visible = xlSheetVeryHidden

    to the code proposed by Legare (between Sub Test and End Sub):

    Dim I As Long
    For I = 1 To ActiveWindow.SelectedSheets.Count
    ActiveWindow.SelectedSheets(I).Visible = xlSheetVeryHidden
    Next I

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Really Hide a sheet (2003 sp2)

    Got it ! Thanks..... works great.


    Brad

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Really Hide a sheet (2003 sp2)

    So.... When I need to view the hidden sheet, Do I have to comment out the code to get to it?

    Thanks,
    Brad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Really Hide a sheet (2003 sp2)

    To unhide a 'very hidden' sheet, set its Visible property to xlSheetVisible. You can do this in the Visual Basic Editor, either by changing the property manually in the Properties pane, or by executing VBA code, either in a macro, or from the Immediate window.

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Really Hide a sheet (2003 sp2)

    what happens if you select ALL sheets?

    Doesn't Excel require at least one sheet to be visible?

    zeddy

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Really Hide a sheet (2003 sp2)

    > Doesn't Excel require at least one sheet to be visible?

    Yes. You cannot hide all sheets of a workbook. (But you can hide the workbook as a whole using Windows | Hide). You'll get an error if only one sheet is visible and you try to hid that one.

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Really Hide a sheet (2003 sp2)

    ActiveWindow.SelectedSheets returns a collection which does not have the visible property. If you want to be able to hide all selected sheets, you will need something like this:

    Public Sub Test()
    Dim oSh() As Worksheet
    Dim I As Long
    ReDim oSh(1 To ActiveWindow.SelectedSheets.Count) As Worksheet
    For I = 1 To ActiveWindow.SelectedSheets.Count
    Set oSh(I) = ActiveWindow.SelectedSheets(I)
    Next I
    On Error Resume Next
    For I = 1 To ActiveWindow.SelectedSheets.Count
    oSh(I).Visible = xlSheetVeryHidden
    Next I
    On Error GoTo 0
    End Sub

    Edited by Legare to fix two problems. First, I discovered that setting a sheet in a multiple sheet selection to very hidden will cause the selection to be reset and the code would fail. Second, the code would get an error if all sheets are selected since all sheets can not be hidden (thanks zeddy).
    Legare Coleman

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Really Hide a sheet (2003 sp2)

    Thanks for pointing that out. In testing for that problem I also discovered another problem. I have edited my original post to fix both.
    Legare Coleman

Posting Permissions

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