Ask a Question Sign up for Free 121 Experts currently online. Ask Questions for Free!

Password to hide and unhide sheets - Excel Programming

The only way u can allow selective access to hide r unhide a worksheet is to protect the workbook with a password. If u set a password to protect the workbook, unless u unprotect the workbook u cannot hide r unhide a worksheet. So while opening the workbook the code ...

Results 1 to 4 of 4

  1. #1

    Password to hide and unhide sheets

    The only way u can allow selective access to hide r unhide
    a worksheet is to protect the workbook with a password. If
    u set a password to protect the workbook, unless u
    unprotect the workbook u cannot hide r unhide a worksheet.

    So while opening the workbook the code will be

    workbook.protect "password"

    when the user wants to hide/unhide a sheet

    workbook.unprotect "password"
    worksheet.activate
    worksheet.hidden=true/false
    workbook.protect "password"

    Veera


     
    Veera
  2. #2

    Password to hide and unhide sheets

    There have been quite a few questions this week on this
    topic - try search on the NG

    Basically, hide all the relevent sheets.
    Add a userform - with a text box for the password with
    the PasswordChar set to say asterisks. add another
    textbox for the username. add an OK button & code it to
    test the two entries - success should result in the
    worksheets being made visible, while failure might result
    in what? the workbook closing

    Patrick Molloy
    Microsoft Excel MVP 
    Patrick
  3. #3

    Re: Password to hide and unhide sheets

    I have a wb with the five sheets named below plus a ws named "Control".
    Change the references as needed to meet you needs. I run this in my
    Workbook_Open event for a wb that does what you want:

    Function GetValidInput() As String
    Dim i As String
    i = InputBox("Please enter your name, capitalizing the first letter:")
    Select Case i
    Case Is = ""
    msgBox "You have not entered a valid name. You will have to press OK to
    let this file close and try again."
    ThisWorkbook.Save
    ThisWorkbook.Close
    Case Is = "Margaret"
    Worksheets("Margaret").Visible = True
    Worksheets("Margaret").Select
    ActiveSheet.Range("A1").Select
    Case Is = "Esther"
    Worksheets("Esther").Visible = True
    Worksheets("Esther").Select
    ActiveSheet.Range("A1").Select
    Case Is = "George"
    Worksheets("George").Visible = True
    Worksheets("George").Select
    ActiveSheet.Range("A1").Select
    Case Is = "Lloyd"
    Worksheets("Lloyd").Visible = True
    Worksheets("Lloyd").Select
    ActiveSheet.Range("A1").Select
    Case Is = "Jimmy"
    Worksheets("Jimmy").Visible = True
    Worksheets("Jimmy").Select
    ActiveSheet.Range("A1").Select
    Case Else
    msgBox "You have not entered a valid name. You will have to press OK to
    let this file close and try again."
    ThisWorkbook.Save
    ThisWorkbook.Close
    End Select
    End Function

    --
    Greeting from the Gulf Coast!
    http://myweb.cableone.net/twodays
    "Veera" <com> wrote in message
    news:0ce401c3512a$d9fc4210$gbl... 


    EZ
  4. #4

    Re: Password to hide and unhide sheets

    Veera,

    Another way would be to make the sheet very hidden.

    From the Workbook.Before_Save Event
    Worksheets("Sheet2").Visible = xlVeryHidden
    (Insures that the workbook is always saved with Sheet2 very hidden

    Then you can have a UserForm called up from a Command Button.

    Command Button Code (from the Controls Toolbox) would be:
    UserForm1.Show

    And the UserForm would be set up with a TextBox
    and a CommandButton.
    Coding as follows:

    Private Sub UserForm_Activate()
    If cCount > 2 Then
    Unload UserForm1
    End If
    End Sub
    Private Sub CommandButton1_Click()
    If cCount > 2 Then
    Unload UserForm1
    Else
    If TextBox1.Value = "abc" Then
    Worksheets("Sheet2").Visible = True
    Else
    cCount = cCount + 1
    End If
    End If
    End Sub

    And lastly.....at the top of a regular module.....(not the UserForm
    module).
    Public cCount as Integer

    The above will allow the user three attempts at the password and won't
    give them the option again until they close and reopen the workbook.

    You should then Lock and Password protect the VBA code from prying
    eyes but be aware that an experienced user can cirvent any passwords

    John




    Veera wrote:
     

    John

Similar Threads

  1. Hide Desktop Icons
    By Bruce in forum Windows 7
    Replies: 4
    Last Post: 11-26, 03:07 PM
  2. unhide a column
    By Jack in forum Microsoft Project
    Replies: 8
    Last Post: 12-10, 09:00 PM
  3. Hide and Unhide columns
    By Lena in forum Microsoft Project
    Replies: 3
    Last Post: 07-22, 02:28 PM
  4. No Show/Hide
    By brian in forum Microsoft Word
    Replies: 1
    Last Post: 07-07, 09:38 PM
  5. password protecting a doent...lost password!
    By alisa in forum Microsoft Word
    Replies: 1
    Last Post: 07-06, 05:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100