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

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

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 enabledShowSheets 'Call the ShowSheets macro to display the desired worksheetsEnd SubPrivate 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 = TrueEnd SubPrivate 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 itDim SaveAsUI As BooleanIf 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 IfEnd IfThisWorkbook.Saved = True 'Don't display the dialog about saving the workbook before closing itEnd SubSub 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 convenienceDim Sh As ObjectDim flPath As StringDim celHome As RangeDim iFileFormat As IntegerIf 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 fieldEnd IfApplication.ScreenUpdating = False 'Turn off screen updating. Avoids screen flickering. Macro runs faster in Excel 2003 and earlier.Set celHome = ActiveCellWorksheets("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 itemNext ShApplication.EnableEvents = False 'Avoids triggering the Save event macro recursively 'Excel 2007 and later requires FileFormat parameter in SaveAs method. 52 is .xlsm, 56 is .xlsIf LCase(flPath) Like "*.xlsm" Then iFileFormat = 52Else iFileFormat = IIf(Application.Version < 12, xlWorkbookNormal, 56) '.xls file format for Excel 2003End IfIf SaveAsUI Then ThisWorkbook.SaveAs flPath, FileFormat:=iFileFormat 'Save the file using the filename and path chosen by the userIf Not SaveAsUI Then ThisWorkbook.Save 'Save the file using its existing filename and pathApplication.EnableEvents = TrueShowSheets celHome 'Call the ShowSheets macro to display the desired worksheetsEnd SubSub 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 convenienceDim Sh As ObjectApplication.ScreenUpdating = FalseFor 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 SelectNext ShWorksheets("Enable macros").Visible = xlVeryHidden 'Hide the sheet containing the enable macros warning messageIf 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 activatedEnd IfApplication.ScreenUpdating = TrueEnd Sub

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…

The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…