Ensuring Macros Are Enabled

  • Thread starter Thread starter TSW632
  • Start date Start date
T

TSW632

Have any of you successfuly used Chip Pearson's method for this? It is
located at http://www.cpearson.com/Excel/EnableMacros.aspx

In Excel 2003 with SP3. I have tried it most recently with a blank
file:

I name a worksheet "Introduction" and select "xlSheetVeryHidden" for
that sheet in the vba editor, paste the code in ThisWorkbook, replace
the password in the code.

I get a variety of errors when I save and re-open.

I am indeed a novice so if you have made this work yourself, I would
appreciate knowing what, if any, changes you had to make.

Thanks
 
Don't know what you are doing with Chip's code but make sure the code for
Thisworkbook and General modules are in the right place.

Here is a quick and dirty set of code without setting the constants and
passwords as Chip does.

IN Thisworkbook Module you paste this event code...........................

'If Macros are enabled this code runs when workbook opens.

Private Sub Workbook_Open()
UnHideAllSheets
Sheets("Introduction").Visible = False
End Sub


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

In General Module..........................

Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub

Add the code per instructions above.

Save and close the workbook..................All sheets except "Introduction"
will be visible.

If macros are enabled when opening, all sheets will become visible and
Introduction will hide.

If macros are disabled, only Introduction sheet will be visible.

Once you get the above working, take another look at employing Chip's code which
is more secure and error trapped.


Gord Dibben MS Excel MVP
 
That worked. Thanks Gord.



Don't know what you are doing with Chip's code but make sure the code for
Thisworkbook and General modules are in the right place.

Here is a quick and dirty set of code without setting the constants and
passwords as Chip does.

IN Thisworkbook Module you paste this event code............................

'If Macros are enabled this code runs when workbook opens.

Private Sub Workbook_Open()
UnHideAllSheets
Sheets("Introduction").Visible = False
End Sub

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

In General Module..........................

Sub UnHideAllSheets()
    Application.ScreenUpdating = False
    Dim n As Single
    For n = 1 To Sheets.Count
        Sheets(n).Visible = True
    Next n
    Application.ScreenUpdating = True
End Sub

Add the code per instructions above.

Save and close the workbook..................All sheets except "Introduction"
will be visible.

If macros are enabled when opening, all sheets will become visible and
Introduction will hide.

If macros are disabled, only Introduction sheet will be visible.

Once you get the above working, take another look at employing Chip's code which
is more secure and error trapped.

Gord Dibben     MS Excel MVP










- Show quoted text -
Click to expand...
 
Good to hear.

I would suggest again looking at Chip's code which is more robust if you need
that level.


Gord
 
Back
Top