Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Make worksheet unavailble (Excel 2002)

    Hi

    Is it possible to prevent someone opening a specific worksheet within a workbook, I understand that you can protect a worksheet, but I only want the worksheet to be seen by accounts and not sales.
    So accounts would need to enter a password to open the worksheet.

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Make worksheet unavailble (Excel 2002)

    You can set the Visible property of the worksheet to xlSheetVeryHidden in the Visual Basic Editor. This means that the sheet can only be made visible using VBA code; it will not be listed in Format | Sheet | Unhide...
    You will need a command button on a visible sheet, or a custom toolbar button, to unhide the sheet. Using a command button from the Control Toolbox, you could use this On Click code:

    Private Sub cmdToggleHide_Click()
    With Worksheets("HiddenSheet")
    If .Visible = xlSheetVeryHidden Then
    If Not (InputBox("Enter password", "Hidden sheet") = "Secret") Then
    Beep
    Exit Sub
    End If
    .Visible = xlSheetVisible
    .Activate
    Me.cmdToggleHide.Caption = "Hide"
    Else
    .Visible = xlSheetVeryHidden
    Me.cmdToggleHide.Caption = "Unhide"
    End If
    End With
    End Sub

    I have attached a demo workbook. The password is Secret (case sensitive). To avoid the end user looking at the code, there is also a password on the VBA code. This is also Secret. The code is in the Sheet1 worksheet module.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Make worksheet unavailble (Excel 2002)

    HI Hans

    Thank you for your prompt reply I will endeavour to apply this.

    Thanks Again

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Make worksheet unavailble (Excel 2002)

    In addition to what Hans said, you might want to consider putting the following in the Workbook Before Save event routine. This will keep a user from saving the workbook with the hidden sheet visible, which would allow anyone who opened it to see the hidden sheet.

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("HiddenSheet").Visible = xlSheetVeryHidden
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Make worksheet unavailble (Excel 2002)

    Hi Legare

    Thank you very much for your follow up advice. I have not been able to use Hans advice yet as I am having difficulty setting it up, but I will keep trying.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Make worksheet unavailble (Excel 2002)

    Anything we can help with?
    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Make worksheet unavailble (Excel 2002)

    HI Legare

    I will have another try tomorrow, and will come back if I am still struggling.

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Make worksheet unavailble (Excel 2002)

    Hi Legare

    I am replying as a courtesy in resopnse to you offfer of help Re-Hans code, I have managed to adapt the code using my own worksheet names and it works very well.

    Thanks to both yourself and Hans for your kind assistance.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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