Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Activation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Activation
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 On Thu, 6 Aug 2009 06:53:02 -0700, terilad wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Activation
Many thanks works a treat.
Regards Mark "Gord Dibben" wrote: 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 On Thu, 6 Aug 2009 06:53:02 -0700, terilad wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Activation | Excel Programming | |||
macro activation | Excel Programming | |||
Excel macro activation. | Excel Programming | |||
Excel macro activation. | Excel Programming | |||
Excel macro activation. | Excel Programming |