Message to Users (Excel 2000)

Topic

New Reply

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?
–cat

Reply | Quote

Replies

Something like this?

Sub UnHideSheet()
    Dim sPassword As String
    
    sPassword = InputBox("What is the password?")
        
    If sPassword  "drowssap" Then
        MsgBox ("That is the WRONG pasword")
        Exit Sub
    Else
        Sheets("Sheet2").Visible = True
    End If
End Sub

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:

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

Close and return to excel (Alt-Q)

Steve

Reply | Quote

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.

Reply | Quote