Do not load if you dont enable macros

[Mr. Burton]

Hi, I have a marco I found from this discussion group, its about locking cells.
I need to lock the cells so they dont get tampered with.

My problem is every time the document is opened you have to enable the macro
to run. This is obviously not very helpful as they can just click no and
delete what they want.

My question is: Is there a way to NOT load the document if the macro is not
enabled? (i.e nothing appears if you do not click enable macros)

That way everything is safe.

Thanks

 

[Mike H]

Hi,

You can't. But you can implement certain techniques to make the workbook
pretty useless unless they are enabled. This can still be defeated but it's
better than nothing. have a look at the sites below.

http://www.j-walk.com/SS/excel/tips/tip100.htm

http://www.cpearson.com/excel/EnableMacros.aspx

Mike

 

[John C]

Another option too would be to add a digital signature to your macros, and
then add your digital signature to the users computers as approved. This will
allow the macros to be 'enabled' even when security setting is set to HIGH.

 

[Gord Dibben]

Why do you need a macro to set sheet protection?

Just lock the desired cells and protect the sheet(s) then save.

If you do need code to lock cells.......e.g. maybe you have event code to
lock cells as you fill them, then you have to provide a contingency plan for
when users disable macros.

One method is to hide the sheets when the workbook is closed.

When users open the workbook with macros diabled, the workbook is useless to
them.

If they enable macros the code runs and sheets are available.

Here is a sample................

Create a sheet named Dummy with a large message typed in the middle.

"You have disabled Macros and this workbook is useless without them. Please
close and re-open with macros enabled"

Then add these two event codes to Thisworkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = True
Sheets("Dummy").Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[Mr. Burton]

Thats perfect thanks alot.

The code works really well,

Great stuff, Helped alot.

 

[Mr. Burton]

I need to lock cells after data has been entered into them so others cant
change it, thats what the macro is for.
But if they dont enable the macros then they can delete what they want.

But the code Mike H suggested works perfectly.

Brillant, Job done.