Password Macro Help

G

Guest

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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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.
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:-

Code:
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:-

Code:
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
 
Upvote 0
Sorry Dave, I started writing that post before I saw yours. Long time, no see :)

Regards,
D
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top