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.

"Gord Dibben" wrote:

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

On Mon, 6 Oct 2008 02:48:00 -0700, Mr. Burton
wrote:

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