Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Thanked 0 Times in 0 Posts

    Message to Users (Excel 2000)

    Hi, this may not be able to be done ... I have hidden sheets for data entry. I've created macros/buttons that unhide the sheets. For example, if the Contract people want to open their data sheet, they click on a button that opens theirs. I also need to share the entire workbook. Can I ask the user to provide a password before opening the sheet?

    I tried protecting the sheet itself. But found that the autofilters do not work when the sheet is protected and shared. Can anyone help?

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

    Re: Message to Users (Excel 2000)

    Something like this?

    <pre>Sub UnHideSheet()
    Dim sPassword As String

    sPassword = InputBox("What is the password?")

    If sPassword <> "drowssap" Then
    MsgBox ("That is the WRONG pasword")
    Exit Sub
    Sheets("Sheet2").Visible = True
    End If
    End Sub</pre>

    Also, You can autofilter with protection:

    Turn on autofilter - (data - filter -autofilter)
    Turn on Protection (tools - protection - protect sheet) add a password if desired

    Open VB (alt-f11)
    Open "project explorer" (Ctrl-R)

    In the "explorer window" dbl-click on the object for the protected sheet with the autofilter

    In the macrocode pane (usually on the right) add this code:

    <pre>Private Sub Worksheet_Activate()
    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect contents:=True, userInterfaceOnly:=True
    End Sub</pre>

    Close and return to excel (Alt-Q)


  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Message to Users (Excel 2000)

    You can ask the user to provide a password in the macro that unhides the sheet. The simplest way is to use the InputBox function; for a more professional look, use a UserForm.
    Lock the VBA project of the workbook for viewing (Tools | Project Properties, Security tab); that way they won't be able to view the code.

    Note: there have been several threads during the last months about the weakness of Excel security. You can only use your passwords for keeping people from inadvertently viewing/editing data; someone determined to crack the password can do so with no great exertion.

Posting Permissions

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