Hi Guys

I have a spreadsheet with some logic that:

* does the old "macros must be enabled" trick by only leaving one
"instruction" sheet visible upon saving so that when its opened with
macros disabled the user can not access the actual data sheets - these
are revealed when the workbook is opened with macros enabled (ala
similar discussions on this group - I know this is not crack proof)

* on the Workbook_Open event re-opens the spreadsheet as read-only
if the Application.UserName is not in a defined list - the code looks
like this:

' re-open this workbook in read-only mode
Sub Reopen_Read_Only()
Application.EnableEvents = True
Application.DisplayAlerts = False ' supress message about file
already open as this is intended
' DEBUG MsgBox "EnableEvents: " & Application.EnableEvents
Workbooks.Open Filename:=ThisWorkbook.Path & "\" &
ThisWorkbook.Name, ReadOnly:=True
End Sub

The problem is that when Reopen_Read_Only is called the spreadsheet re-
opens nicely as read-only but Workbook_Open is *not* called as though
events are disabled. This is a problem, of course, due to my "macros
disabled" logic as described above. The weird thing is then when fully
opened events are enabled but Workbook_Open never gets called on the
re-open!!!

Does anyone have a clue for me why this would happen? Can I configure
a workbook to enable events when it opens? I dont think you can.

Cheers for any ideas.
Chrisso

I think I'd drop the reopening as readonly and just use something like:

ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly

ps.

ThisWorkbook.Path & "\" & ThisWorkbook.Name
is the same as:
ThisWorkbook.FullName

Chrisso wrote:

Hi Guys

I have a spreadsheet with some logic that:

* does the old "macros must be enabled" trick by only leaving one
"instruction" sheet visible upon saving so that when its opened with
macros disabled the user can not access the actual data sheets - these
are revealed when the workbook is opened with macros enabled (ala
similar discussions on this group - I know this is not crack proof)

* on the Workbook_Open event re-opens the spreadsheet as read-only
if the Application.UserName is not in a defined list - the code looks
like this:

' re-open this workbook in read-only mode
Sub Reopen_Read_Only()
Application.EnableEvents = True
Application.DisplayAlerts = False ' supress message about file
already open as this is intended
' DEBUG MsgBox "EnableEvents: " & Application.EnableEvents
Workbooks.Open Filename:=ThisWorkbook.Path & "\" &
ThisWorkbook.Name, ReadOnly:=True
End Sub

The problem is that when Reopen_Read_Only is called the spreadsheet re-
opens nicely as read-only but Workbook_Open is *not* called as though
events are disabled. This is a problem, of course, due to my "macros
disabled" logic as described above. The weird thing is then when fully
opened events are enabled but Workbook_Open never gets called on the
re-open!!!

Does anyone have a clue for me why this would happen? Can I configure
a workbook to enable events when it opens? I dont think you can.

Cheers for any ideas.
Chrisso

--

Dave Peterson

Thanks Dave. That rocks - just the tonic.

Chrisso