Closing
 

I have my workbook set so that you can not open it
without enabling macros with;

Private Sub Workbook Open ()
This Workbook.IsAddin = False

It works great except when I close the file, using a
dialog box. If I select "Save & Close" it saves and
closes the file but it sets the workbook so that you can
now open without enabling macros.

If I use the normal Excel Close, it can not be opened
without enabling macros.

Any ideas on what is happening?

Thanks

Closing
 

i have once created a Workbook that was trying to do the same thing you're
trying to achieve.
What I did was to hide all the worksheets except for the "Warning" sheet
before every time it's been saved.
when it's open, if the Macro isn't enabled then only the Warning sheet will
be display.
This works as long as no one change the sheet name from Warning to something
else therefore I set up an error trap before it's saved to warn the end-user
that if the name isn't change back to Warning then they can't save it.
the following are the steps to create this:

I created a blank worksheet named "Warning" with nothing but a warning
message to let the user know that in order to use this they must Enable macros

I then put the follwoing code in the Workbook_Open event

Private Sub Workbook_Open()
Dim s As Worksheet
Application.ScreenUpdating = False
For Each s In ThisWorkbook.Worksheets
If s.Name < "Warning" Then
s.Visible = xlSheetVisible
Else
s.Visible = xlSheetVeryHidden
End If
Next s
ThisWorkbook.Worksheets(1).Select
Application.ScreenUpdating = True
End Sub


Then, I put the following code in BeforeSaved

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim s As Worksheet
On Error GoTo TrapError
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Warning").Visible = xlSheetVisible
For Each s In ThisWorkbook.Worksheets
If s.Name < "Warning" Then
s.Visible = xlSheetVeryHidden
End If
Next s
Application.ScreenUpdating = True
Exit Sub
TrapError:
'this is incase the Warning Sheet name got changed
Cancel = True
MsgBox "The worksheet that was named 'Warning' was changed." & _
" Please change it back then save again", vbCritical, "Save action was
canceled"
End Sub

Matt Chen
Blue Ridge Telecom


"Jon M." wrote:

I have my workbook set so that you can not open it
without enabling macros with;

Private Sub Workbook Open ()
This Workbook.IsAddin = False

It works great except when I close the file, using a
dialog box. If I select "Save & Close" it saves and
closes the file but it sets the workbook so that you can
now open without enabling macros.

If I use the normal Excel Close, it can not be opened
without enabling macros.

Any ideas on what is happening?

Thanks

Closing
 

-----Original Message-----
i have once created a Workbook that was trying to do the
same thing you're
trying to achieve.
What I did was to hide all the worksheets except for
the "Warning" sheet
before every time it's been saved.
when it's open, if the Macro isn't enabled then only the
Warning sheet will
be display.
This works as long as no one change the sheet name from
Warning to something
else therefore I set up an error trap before it's saved
to warn the end-user
that if the name isn't change back to Warning then they
can't save it.
the following are the steps to create this:

I created a blank worksheet named "Warning" with nothing
but a warning
message to let the user know that in order to use this
they must Enable macros

I then put the follwoing code in the Workbook_Open event

Private Sub Workbook_Open()
Dim s As Worksheet
Application.ScreenUpdating = False
For Each s In ThisWorkbook.Worksheets
If s.Name < "Warning" Then
s.Visible = xlSheetVisible
Else
s.Visible = xlSheetVeryHidden
End If
Next s
ThisWorkbook.Worksheets(1).Select
Application.ScreenUpdating = True
End Sub


Then, I put the following code in BeforeSaved

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
Dim s As Worksheet
On Error GoTo TrapError
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Warning").Visible =
xlSheetVisible
For Each s In ThisWorkbook.Worksheets
If s.Name < "Warning" Then
s.Visible = xlSheetVeryHidden
End If
Next s
Application.ScreenUpdating = True
Exit Sub
TrapError:
'this is incase the Warning Sheet name got changed
Cancel = True
MsgBox "The worksheet that was named 'Warning' was
changed." & _
" Please change it back then save again",
vbCritical, "Save action was
canceled"
End Sub

Matt Chen
Blue Ridge Telecom


"Jon M." wrote:

I have my workbook set so that you can not open it
without enabling macros with;

Private Sub Workbook Open ()
This Workbook.IsAddin = False

It works great except when I close the file, using a
dialog box. If I select "Save & Close" it saves and
closes the file but it sets the workbook so that you
can
now open without enabling macros.

If I use the normal Excel Close, it can not be opened
without enabling macros.

Any ideas on what is happening?

Thanks

.


Thanks a lot for the TIME and info.