Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


I Need to make the security warning for enable macros stand out more

Posted on 2010-11-08
Medium Priority
Last Modified: 2012-05-10
I have a workbook on our file server that our company has started using emphatically.  My problem is I don't have a digital signature and many times someone will open it up and not see the security warning and so they won't enable the macros and therefore the calculations don't work.  If they enable them it all works great.
What I'd like to do is somehow force them to enable macros with a message of some kind or if I can change the security warning to be a different color say red.  Then it would be easier to see.  Any thoughts?  I know getting a digital cert would probably be easier but it's not in the budget right now and I don't understand it well enough to convence them to put it in the budget.
Thanks Erin
Question by:Erin_J
LVL 77

Accepted Solution

peter57r earned 1000 total points
ID: 34085798
Make a first sheet that has an enormous message on it to tell them to enable macros.
In your macro move to a different sheet as the first step.
(Save the workbook with the message sheet visible).
LVL 20

Assisted Solution

by:Ardhendu Sarangi
Ardhendu Sarangi earned 1000 total points
ID: 34086043
Hi Erin,
Can you please see attached sheet. This is a template I have created to be used in our organization and has worked perfectly so far.

Author Comment

ID: 34086325
Both solutions seem like good work arounds.  I'm trying them to see which will fit better in our situation.  Thanks for your replies.
LVL 81

Expert Comment

ID: 34086807
A somewhat stronger method of getting people to enable macros is to open the workbook with all worksheets hidden except one displaying a message encouraging them to enable macros. If the workbook is opened with macros enabled, then the normal worksheets display. You'll need to reverse the process when the workbook is saved. Code for this purpose (shown in snippet below) would be stored in the ThisWorkbook code pane.

The sample workbook shows how it might work.

'The five subs work together to force macros to be enabled when the workbook is opened. If you don't, you'll _
    see instructions on how to enable macros. All other worksheets will be completely hidden.
'Event subs (Workbook_Open, Workbook_BeforeSave and Workbook_BeforeClose) have prescribed names and parameters. _
    You must use the names and parameters exactly as shown--they won't work otherwise. _
    You may have only one event sub of each type in the code pane. If you find yourself wanting two, the code must be _
    integrated into a single event sub.
'The three event subs must be stored in the ThisWorkbook code pane. They won't work at all if installed anywhere else. _
    The two other subs (SpecialSave and ShowSheets) could be installed either in ThisWorkbook or else in a regular module sheet.
Private Sub Workbook_Open()
'This event sub runs when the workbook is opened and macros have been enabled
ShowSheets      'Call the ShowSheets macro to display the desired worksheets
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'This event sub runs when a file save is initiated. It runs before the file is actually saved.
Cancel = True           'Don't save the file after running this macro--it will already have been saved by SpecialSave macro.
SpecialSave SaveAsUI    'Call the SpecialSave macro to save the file (displaying the file selector browser if desired)
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This runs when a file close is initiated. The code avoids an endless loop when you close the workbook without having saved it
Dim SaveAsUI As Boolean
If ThisWorkbook.Saved = False Then
    If MsgBox("Do you want to save the workbook before closing it?", vbYesNo) = vbYes Then
        If Not ThisWorkbook.Name Like "*.xl*" Then SaveAsUI = True  'If filename has no extension (i.e. never been saved), then display the file selector browser
        SpecialSave SaveAsUI
    End If
End If
ThisWorkbook.Saved = True   'Don't display the dialog about saving the workbook before closing it
End Sub

Sub SpecialSave(SaveAsUI As Boolean)
'Saves the file and displays the Save As dialog if appropriate
'Hides all sheets except one titled "You must enable macros", then restores the visibility of those sheets
'This sub could have been stored in a regular module sheet, but I put it here for convenience
Dim Sh As Object
Dim flPath As String
Dim celHome As Range
Dim iFileFormat As Integer
If SaveAsUI Then
        'The FileFilter parameter eliminates the double quotes surrounding the default file name in the GetSaveAsFilename dialog, _
            provided that the existing file extension matches the default filter. _
            You get the text for the FileFilter from the File...Save As dialog. Look in the "Save as type" field. _
            Feel free to change the text before the comma. The comma and text following it must match exactly, however!
    flPath = Application.GetSaveAsFilename(ThisWorkbook.Name, FileFilter:="Excel workbook (*.xls),*.xls,Macro-enabled workbook (*.xlsm),*.xlsm", _
        FilterIndex:=IIf(ThisWorkbook.Name Like "*.xls", 1, 2))
            'You don't need the FilterIndex parameter if there is only one filter type. _
                The Iif function is like the worksheet IF function. It defaults the file type according to the current file name.
    If flPath = "False" Then Exit Sub   'GetSaveAsFilename returns "False" if user clears the file name field
End If

Application.ScreenUpdating = False      'Turn off screen updating. Avoids screen flickering. Macro runs faster in Excel 2003 and earlier.
Set celHome = ActiveCell
Worksheets("Enable macros").Visible = True   'Avoids fatal error if enable macros sheet is the last one in the workbook (they can't all be hidden)
For Each Sh In ThisWorkbook.Sheets
   If Sh.Name <> "Enable macros" Then Sh.Visible = xlVeryHidden  'xlVeryHidden worksheets aren't listed in the Format...Sheet...Unhide menu item
Next Sh

Application.EnableEvents = False    'Avoids triggering the Save event macro recursively
    'Excel 2007 and later requires FileFormat parameter in SaveAs method. 52 is .xlsm, 56 is .xls
If LCase(flPath) Like "*.xlsm" Then
    iFileFormat = 52
    iFileFormat = IIf(Application.Version < 12, xlWorkbookNormal, 56)   '.xls file format for Excel 2003
End If
If SaveAsUI Then ThisWorkbook.SaveAs flPath, FileFormat:=iFileFormat    'Save the file using the filename and path chosen by the user
If Not SaveAsUI Then ThisWorkbook.Save          'Save the file using its existing filename and path
Application.EnableEvents = True
ShowSheets celHome             'Call the ShowSheets macro to display the desired worksheets
End Sub

Sub ShowSheets(Optional celHome As Range)
'Hides the worksheet with instructions to enable macros and displays all the rest (with a couple of exceptions)
'This sub could have been stored in a regular module sheet, but I put it here for convenience
Dim Sh As Object
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Sheets
    Select Case Sh.Name
    Case "Top Secret", "Confidential"    'These worksheets must remain hidden. List as many as you like, separated by commas.
    Case Else
        Sh.Visible = True   'If you don't need to hide any worksheets, then keep this statement and delete the rest of the Select Case block.
    End Select
Next Sh
Worksheets("Enable macros").Visible = xlVeryHidden     'Hide the sheet containing the enable macros warning message
If Not celHome Is Nothing Then
    If celHome.Worksheet.Visible = xlSheetVisible Then Application.Goto celHome       'If you don't pass celHome, then the first visible sheet is activated
End If
Application.ScreenUpdating = True
End Sub

Open in new window


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question