Results 1 to 3 of 3

Thread: Excel

  1. #1
    New Lounger
    Join Date
    Sep 2002
    London, Gtr London, England
    Thanked 0 Times in 0 Posts


    I have a workbook with a worksheet for each employee (20 in total) I am trying to write a module which hides all worksheet tabs, shows a dialog box asking for a username and password and then makes only the relevant worksheet available. Could anyone suggest a way of doing this? I am going round in circles and not getting anywhere.

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Thanked 0 Times in 0 Posts

    Re: Excel

    I have a similar need, but the security may not be enough for you as any user could go Tools/Options/Show Tabs to navigate to other sheets. (This purely prevents them accidentally entering expense claims onto the wrong sheet.)
    It also depends on the user being correctly logged in with the Application User data being correct.
    This goes in the 'ThisWorkbook' sheet in VB:

    Private Sub Workbook_Open()
    un = Left$(Application.UserName, 3)
    If un = "Lei" Then Worksheets(3).Activate: ActiveWindow.DisplayWorkbookTabs = True
    If un = "Ann" Then Worksheets(4).Activate: ActiveWindow.DisplayWorkbookTabs = False
    If un = "Pau" Then Worksheets(2).Activate: ActiveWindow.DisplayWorkbookTabs = False
    If un = "Tin" Then Worksheets(1).Activate: ActiveWindow.DisplayWorkbookTabs = False
    If un = "Sal" Then Worksheets(5).Activate: ActiveWindow.DisplayWorkbookTabs = False
    End Sub

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Adelaide, South Australia
    Thanked 0 Times in 0 Posts

    Re: Excel

    In Excel 97, you would need to do something along the lines of:
    1) Hide all of the sheets. If you want to hide them properly, you need to do it programmatically, eg
    for each s in thisworkbook.sheets
    Then the sheets can't be unhidden from the format column. They can only be unhidden by code.

    2) Put a password on your workbook and on your code.

    3) Then, to open the workbook, you need something like:
    sub auto_open()
    HideAllSheets 'Which is the code from 1)
    end sub

    Where UserForm1 is your dialog box.

    4) On UserForm1 you need to insert a username and password field, and an ok and cancel button.

    5) The ok button obviously needs to check that the password is correct, and if so, then unhide the correct sheet, eg
    thisworkbook.sheets(application.username).visible = xlsheetvisible

    Is that enough to help you?


Posting Permissions

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