Macro Activation

  • Thread starter Thread starter terilad
  • Start date Start date
T

terilad

Hi,

i am looking for a code to set up an excel workbook, so that when the
workbook is opened sheet 1 will always open to report macros are disabled
when macros are disabled on the pc, but when macros are enabled on the pc the
workbook will always open at sheet 2, please note that if macros are not
enabled the workbook must open at sheet 1 and the user cannot access any
other sheets until macros have been enabled, and also sheet 1 must be hidden
from the user.

Can anyone help me with this task.

Regards


Mark
 
Entered in Thisworkbook module.

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Sheet1" Then
sht.Visible = True
Sheets("Sheet1").Visible = xlVeryHidden
End If
Next sht
Sheets("Sheet2").Activate
Application.ScreenUpdating = True
End Sub

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

On Sheet1 place a large message like "You have disabled macros which renders
this workbook useless. Close and re-open with macros enabled"


Gord Dibben MS Excel MVP
Gord Dibben MS Excel MVP
 
Many thanks works a treat.

Regards

Mark

Gord Dibben said:
Entered in Thisworkbook module.

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Sheet1" Then
sht.Visible = True
Sheets("Sheet1").Visible = xlVeryHidden
End If
Next sht
Sheets("Sheet2").Activate
Application.ScreenUpdating = True
End Sub

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

On Sheet1 place a large message like "You have disabled macros which renders
this workbook useless. Close and re-open with macros enabled"


Gord Dibben MS Excel MVP
Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top