Re: Force Macros to be enabled upon opening the file

Option Explicit

Option Explicit

Private Sub Workbook_Open()
     
    With Application
         'disable the ESC key
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
         
        Call UnhideSheets
         
        .ScreenUpdating = True
         're-enable ESC key
        .EnableCancelKey = xlInterrupt
    End With
     
End Sub
'
Private Sub UnhideSheets()
     '
    Dim Sheet As Object
     '
    Worksheets("Detail").Visible = xlSheetVisible
    Worksheets("Summary").Visible =
xlSheetVisible
    Worksheets("Prompt").Visible =
xlSheetVeryHidden
     '
    Application.Goto Worksheets(1).[A1], True '<
Optional
     '
    Set Sheet = Nothing
    ActiveWorkbook.Saved = True
     
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
         
        Call HideSheets
         
        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With
End Sub

Private Sub HideSheets()
     '
    With Worksheets("Prompt")
         '
         'the hiding of the sheets constitutes a change that
generates
         'an automatic "Save?" prompt, so IF the
book has already
         'been saved prior to this point, the next line and
the Lines
         'relating to .[A100] below bypass the
"Save?" dialog...
        If ThisWorkbook.Saved = True Then
.Range("A100").Value = "Saved"
         '
        
        .Visible = xlSheetVisible
        Worksheets("Detail").Visible =
xlSheetVeryHidden
        Worksheets("Summary").Visible =
xlSheetVeryHidden
         '
        If .Range("A100").Value =
"Saved" Then
            .Range("A100").ClearContents
            ThisWorkbook.Save
        End If
         '
    End With
     '
End Sub


----- Original Message ----- 
From: "Dold Roger D Civ AFPC/DPIEB"
<Roger.DoldRANDOLPH.AF.MIL>
To: <EXCEL-GPEACH.EASE.LSOFT.COM>
Sent: Wednesday, February 28, 2007 9:34 PM
Subject: Force Macros to be enabled upon opening the file


I have a shared workbook with several sheets in it.  I have
incorporated
some VBA tricks and tips that I have seen you experts write.
 And they
work marvelously. I have it discussed a few times, and via
GOOGLE I
found code to force other users to enable macros upon
opening a
workbook.  The code and comments read as follows below.  My
problem is
that I have two Sheets, one called "Detail" and
the other called
"Summary" that I want to display after the
"Prompt" sheet is hidden.  In
addition to the "Prompt" sheet described in the
code, I have three other
sheets that I wish to remain hidden.  If I have left
anything out, the
code and instructions come from   

 http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

Option Explicit 

Private Sub Workbook_Open() 
     
    With Application 
         'disable the ESC key
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 
         
        Call UnhideSheets 
         
        .ScreenUpdating = True 
         're-enable ESC key
        .EnableCancelKey = xlInterrupt 
    End With 
     
End Sub 
'
Private Sub UnhideSheets() 
     '
    Dim Sheet As Object 
     '
    For Each Sheet In Sheets 
        If Not Sheet.Name = "Prompt" Then 
            Sheet.Visible = xlSheetVisible 
        End If 
    Next 
     '
    Sheets("Prompt").Visible = xlSheetVeryHidden 
     '
    Application.Goto Worksheets(1).[A1], True '<
Optional
     '
    Set Sheet = Nothing 
    ActiveWorkbook.Saved = True 
     
End Sub 

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    With Application 
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 
         
        Call HideSheets 
         
        .ScreenUpdating = True 
        .EnableCancelKey = xlInterrupt 
    End With 
End Sub 

Private Sub HideSheets() 
     '
    Dim Sheet As Object '< Includes worksheets and
chartsheets
     '
    With Sheets("Prompt") 
         '
         'the hiding of the sheets constitutes a change
that
generates
         'an automatic "Save?" prompt, so IF the
book has
already
         'been saved prior to this point, the next line and
the
lines
         'relating to .[A100] below bypass the
"Save?" dialog...
        If ThisWorkbook.Saved = True Then .[A100] =
"Saved" 
         '
        .Visible = xlSheetVisible 
         '
        For Each Sheet In Sheets 
            If Not Sheet.Name = "Prompt" Then 
                Sheet.Visible = xlSheetVeryHidden 
            End If 
        Next 
         '
        If .[A100] = "Saved" Then 
            .[A100].ClearContents 
            ThisWorkbook.Save 
        End If 
         '
        Set Sheet = Nothing 
    End With 
     '
End Sub
 
Please bear in mind you are working with a VBA beginner. 
TIA!


//SIGNED// 
ROGER D. DOLD, CIV, USAF 
Data Analysis Branch, DSN 665-2926 ex 3167, COM
(210)565-2926
Caution:  This message may contain competitive or other
non-public
information protected by federal law from disclosure and not
intended
for disclosure outside official government channels.  Do not
disseminate
this message without the approval of the originating office.
 If you
received this message in error, please notify the sender by
reply email
and delete all copies of the message.




------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

__________ NOD32 2085 (20070228) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G