I have a few workbooks which include macros and are occasionally
distributed to individual users.

I have found it necessary to point out to users when they are using
these workbooks with macros disabled.

The only protection is that the projects are passworded. I'm assuming
the macros are disabled by accident. I have no desire to combat
deliberate attempts to crack the workbook.

I therefore propose to create in each workbook a worksheet named
"macrosOff" which just contains instructions to reopen the workbook with
macros enabled. I will then paste some generic code in the Workbook_Open
events and in Workbook_BeforeSave

The code saves the workbook with only "macrosOff" visible, then opens it
with all worksheets except "macrosOff" visible.

The code I've cobbled together is reproduced below.
The only issue I've noticed so far is that the "Save as" functionality
is severely restricted. I can live with that.

Can anyone see anything else in the code that is likely to bite me if I
release it into the real world?

I must admit that I haven't searched for ready-written code on the web,
so if pointing me to a tutorial is more appropriate, that would be a
great help too.

Code follows:

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim fName As Variant
Cancel = True
With Application
.EnableEvents = False
.ScreenUpdating = False
hideSheets
If SaveAsUI Then
fName = .GetSaveAsFilename(, "Excel workbook (*.xls), *.xls")
If fName < False Then
ThisWorkbook.SaveAs fName, ThisWorkbook.FileFormat
End If
Else
ThisWorkbook.Save
End If
showSheets
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Private Sub Workbook_Open()
showSheets
End Sub

Private Sub hideSheets()
Dim ws As Worksheet
With ThisWorkbook
Set ws = .Worksheets("macrosOff")
ws.Visible = xlSheetVisible
For Each ws In .Worksheets
If ws.Name < "macrosOff" Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
End With
End Sub

Private Sub showSheets()
Dim unDirty As Boolean
Dim ws As Worksheet
unDirty = ThisWorkbook.Saved
With ThisWorkbook
For Each ws In .Worksheets
If ws.Name < "macrosOff" Then
ws.Visible = xlSheetVisible
End If
Next ws
Set ws = .Worksheets("macrosOff")
ws.Visible = xlSheetVeryHidden
End With
ThisWorkbook.Saved = unDirty
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''

Hi Steve
without actually testing I would only protect/unprotect your workbook
within Workbook_open and Before_save as additional measurement


--
Regards
Frank Kabel
Frankfurt, Germany


Steve Garman wrote:
I have a few workbooks which include macros and are occasionally
distributed to individual users.

I have found it necessary to point out to users when they are using
these workbooks with macros disabled.

The only protection is that the projects are passworded. I'm assuming
the macros are disabled by accident. I have no desire to combat
deliberate attempts to crack the workbook.

I therefore propose to create in each workbook a worksheet named
"macrosOff" which just contains instructions to reopen the workbook
with macros enabled. I will then paste some generic code in the
Workbook_Open events and in Workbook_BeforeSave

The code saves the workbook with only "macrosOff" visible, then opens
it with all worksheets except "macrosOff" visible.

The code I've cobbled together is reproduced below.
The only issue I've noticed so far is that the "Save as"
functionality
is severely restricted. I can live with that.

Can anyone see anything else in the code that is likely to bite me if
I release it into the real world?

I must admit that I haven't searched for ready-written code on the
web, so if pointing me to a tutorial is more appropriate, that would
be a great help too.

Code follows:

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim fName As Variant
Cancel = True
With Application
.EnableEvents = False
.ScreenUpdating = False
hideSheets
If SaveAsUI Then
fName = .GetSaveAsFilename(, "Excel workbook (*.xls), *.xls")
If fName < False Then
ThisWorkbook.SaveAs fName, ThisWorkbook.FileFormat
End If
Else
ThisWorkbook.Save
End If
showSheets
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Private Sub Workbook_Open()
showSheets
End Sub

Private Sub hideSheets()
Dim ws As Worksheet
With ThisWorkbook
Set ws = .Worksheets("macrosOff")
ws.Visible = xlSheetVisible
For Each ws In .Worksheets
If ws.Name < "macrosOff" Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
End With
End Sub

Private Sub showSheets()
Dim unDirty As Boolean
Dim ws As Worksheet
unDirty = ThisWorkbook.Saved
With ThisWorkbook
For Each ws In .Worksheets
If ws.Name < "macrosOff" Then
ws.Visible = xlSheetVisible
End If
Next ws
Set ws = .Worksheets("macrosOff")
ws.Visible = xlSheetVeryHidden
End With
ThisWorkbook.Saved = unDirty
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''

Thanks for that, Frank. It's certainly worth adding.

Frank Kabel wrote:
Hi Steve
without actually testing I would only protect/unprotect your workbook
within Workbook_open and Before_save as additional measurement


--
Regards
Frank Kabel
Frankfurt, Germany


Steve Garman wrote:

I have a few workbooks which include macros and are occasionally
distributed to individual users.

I have found it necessary to point out to users when they are using
these workbooks with macros disabled.

The only protection is that the projects are passworded. I'm assuming
the macros are disabled by accident. I have no desire to combat
deliberate attempts to crack the workbook.

I therefore propose to create in each workbook a worksheet named
"macrosOff" which just contains instructions to reopen the workbook
with macros enabled. I will then paste some generic code in the
Workbook_Open events and in Workbook_BeforeSave

The code saves the workbook with only "macrosOff" visible, then opens
it with all worksheets except "macrosOff" visible.

The code I've cobbled together is reproduced below.
The only issue I've noticed so far is that the "Save as"

functionality

is severely restricted. I can live with that.

Can anyone see anything else in the code that is likely to bite me if
I release it into the real world?

I must admit that I haven't searched for ready-written code on the
web, so if pointing me to a tutorial is more appropriate, that would
be a great help too.

Code follows:

'''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim fName As Variant
Cancel = True
With Application
.EnableEvents = False
.ScreenUpdating = False
hideSheets
If SaveAsUI Then
fName = .GetSaveAsFilename(, "Excel workbook (*.xls), *.xls")
If fName < False Then
ThisWorkbook.SaveAs fName, ThisWorkbook.FileFormat
End If
Else
ThisWorkbook.Save
End If
showSheets
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Private Sub Workbook_Open()
showSheets
End Sub

Private Sub hideSheets()
Dim ws As Worksheet
With ThisWorkbook
Set ws = .Worksheets("macrosOff")
ws.Visible = xlSheetVisible
For Each ws In .Worksheets
If ws.Name < "macrosOff" Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
End With
End Sub

Private Sub showSheets()
Dim unDirty As Boolean
Dim ws As Worksheet
unDirty = ThisWorkbook.Saved
With ThisWorkbook
For Each ws In .Worksheets
If ws.Name < "macrosOff" Then
ws.Visible = xlSheetVisible
End If
Next ws
Set ws = .Worksheets("macrosOff")
ws.Visible = xlSheetVeryHidden
End With
ThisWorkbook.Saved = unDirty
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''' '''''''''