Password Macro Help

[Guest]

Thanks to some of the replys last week, I now have a macro to create a password that will prevent others printing a worksheet.

However, this doesn't work if someone deactivates the macro using macro protection. Is there a way around this?

Many thanks for the help in advance.

Bazil

 

[Dave Hawley]

Hi

There is no sure way of preventing this as it's designed to protect users from possible viruses. I do however have a method than can be used to encourage users to activate macros, if they choose no they are left looking at a blank Worsheet.

Go here:
http://www.ozgrid.com/download/default.htm
and download: EnableMacros.zip

 

[dk]

The only satisfactory I've come across for solving this problem is to do the following.

In your workbook add a new sheet called something like Macros Disabled and put a message on it saying 'This workbook won't function without Macros Enabled. Please close and reopen ensuring you click the Enable Macros button.'

Now run this code.

Sub HideSheets()
For Each sh In ActiveWorkbook.Sheets
    If sh.Name <> "Macros Disabled" Then
        sh.Visible = xlSheetVeryHidden
    End If
Next
End Sub

Now, a sheet hidden with xlSheetVeryHidden can't be unhidden without VBA code (i.e. you can't choose Format, Sheet, Unhide).

The next thing to do is to include some code in your Workbook_Open event to unhide your sheets. Obviously, the Workbook_Open event will only execute if macros are enabled. To get to the workbook code module right click the second Excel icon from the top left and choose View Code. Then select Workbook from the left dropdown box - this will create a blank procedure. Something like this will do the trick:-

Sub HideSheets()
For Each sh In ActiveWorkbook.Sheets
    If sh.Name <> "Macros Disabled" Then
        sh.Visible = xlSheetVeryHidden
    End If
Next
End Sub

You should also include some code in the BeforeClose like this:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Macros Disabled").Visible = xlSheetVisible

For Each sh In ThisWorkbook.Sheets
    If sh.Name <> "Macros Disabled" Then
        sh.Visible = xlSheetVeryHidden
    End If
Next
ThisWorkbook.Save
End Sub

HTH,

Any problems, let me know.

D

 

[dk]

Sorry Dave, I started writing that post before I saw yours. Long time, no see

Regards,
D

 

[Guest]

Many thanks Chaps - I'll have a go.

Bazil