?
Solved

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

Posted on 2010-11-08
4
Medium Priority
?
336 Views
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
0
Comment
Question by:Erin_J
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

Accepted Solution

by:
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).
0
 
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.
sample-warning.xls
0
 

Author Comment

by:Erin_J
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.
0
 
LVL 81

Expert Comment

by:byundt
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.

Brad
'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
Else
    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

ForcedEnableMacros.xls
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

650 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