Forcing Your Clients to Enable Macros

It happens at least once a quarter. A customer calls you to complain that your expertly-created dashboard is doesn’t work “for them”. After a few seconds of questioning, you realize they didn’t enable macros.

.

In order to use any macro in Excel, your users must “Enable” macros. That is, they must give Excel permission to use run macros in their environment. They can do this by clicking on the Security Warning that pops up above the Formula Bar.

.

The bottom line is that you can write all the fancy macros in the world, but they will never run unless your users “Enable Macros”. This begs the question; can you force users to enable macros? The answer is yes – with a little trickery.

.

The idea is relatively simple. You create a sheet named something like “START”. On that sheet, there is nothing more than just a simple warning stating that macros must be enabled.

.

You then hide all sheets in the workbook except for that one “START” Sheet. Finally, you write a simple macro that will unhide all sheets when the workbook is opened.

.

At this point, when the workbook is opened, Excel will ask the user to enable macros. The users will be forced to because all they will see is your “START” sheet. The other sheets will be hidden!

.

You can download a working example of this technique here:

www.datapigtechnologies.com/downloads/Forceenablemacros.xls

.

In order to make this work, you actually need two macros: one macro that hides all but the “Start” sheet when the workbook closes, and another macro that unhides all but the “Start” sheet when the workbook opens.

.

First, let’s tackle the actions that need to happen when the workbook closes.

1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard

2. In the Project window, find your project/workbook name and click the plus sign next to it in order to see all the sheets.

3. Click ThisWorkbook.

4. Select the Before Close event in the Event dropdown.


5. Type or paste this code.

  1. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  2.  
  3. 'Step 1: Declare your variables
  4. Dim ws As Worksheet
  5.  
  6. 'Step 2: Unhide the Starting Sheet
  7. Sheets("START").Visible = xlSheetVisible
  8.  
  9. 'Step 3: Start looping through all worksheets
  10. For Each ws In ThisWorkbook.Worksheets
  11.  
  12. 'Step 4: Check each worksheet name
  13. If ws.Name <> "START" Then
  14.  
  15. 'Step 5: Hide the sheet
  16. ws.Visible = xlVeryHidden
  17. End If
  18.  
  19. 'Step 6: Loop to next worksheet
  20. Next ws
  21.  
  22. 'Step 7: Save the workbook
  23. ActiveWorkbook.Save
  24.  
  25. End Sub

Step 1: We first declare an object called ws. This will create a memory container for each worksheet we loop through.

Step 2: In step two, we ensure that the “START” sheet is visible.

Step 3: In step three, we start the looping, telling Excel we want to evaluate all worksheets in this workbook.

Step 4: In this step we simply compare the name “START” to the sheet that is currently being looped. This will ensure that the actions that come next are applied to all sheets except the “START” sheet.

Step 5: If the sheet names are different, we hide the sheet using the xlVeryHidden property. This property not only hides the sheet, but prevents the user from manually unhiding it using the user interface.

Step 6: In step six we loop back to get the next sheet.

 

Step 7: Once all sheets are evaluated, the macro saves the workbook and ends.

.

.

Now, we’ll have to write a macro to handle all the actions that need to happen when the workbook opens.

1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard

2. In the Project window, find your project/workbook name and click the plus sign next to it in order to see all the sheets.

3. Click ThisWorkbook.

4. Select the Open event in the Event dropdown.


5. Type or paste this code.

  1. Private Sub Workbook_Open()
  2.  
  3. 'Step 1: Declare your variables
  4. Dim ws As Worksheet
  5.  
  6. 'Step 2: Start looping through all worksheets
  7. For Each ws In ThisWorkbook.Worksheets
  8.  
  9. 'Step 3: Unhide All Worksheets
  10. ws.Visible = xlSheetVisible
  11.  
  12. 'Step 4: Loop to next worksheet
  13. Next ws
  14.  
  15. 'Step 5: Hide the Start Sheet
  16. Sheets("START").Visible = xlVeryHidden
  17.  
  18. End Sub

Step 1: We first declare an object called ws. This will create a memory container for each worksheet we loop through.

Step 2: In step two, we start the looping, telling Excel we want to evaluate all worksheets in this workbook.

Step 3: In this step we unhide the sheet that is currently being looped. This will in effect unhide all worksheets as each sheet is made visible.

Step 4: In step four we loop back to get the next sheet.

 

Step 5: Once all sheets are made visible, step five hides the “START” sheet. Again, we use the xlVeryHidden property so that the user will not be able to manually unhide it using the user interface.

.

Once both macros are implemented, you will have a workbook that will only work if the user enables macros!

40 thoughts on “Forcing Your Clients to Enable Macros

  1. Jon

    Fantastic idea!!!… I macro’s don’t have a large enough user base to get too many of those calls, but I see questions on Stack overflow all the time about it.

  2. jeff Weir

    Yeah, I used to do this on a form I whipped up, using the SaveStateAndHide code from http://www.cpearson.com/excel/EnableMacros.aspx

    But then I found it problematic for some reason. So then I just used a big shape placed strategically over the form that not only told them to enable macros, but showed them how to do it:
    http://screencast.com/t/iyGeW11mt6aO

    , and hid it after Macros had been enabled: ActiveSheet.Shapes.Range(Array(“EnableMacros”)).Visible = msoFalse

    …and then brought it up again on workbook close

    ActiveSheet.Shapes.Range(Array(“EnableMacros”)).Visible = msoTrue

    Found it less problematic than that looping though worksheets.

    Note that if users have the VBE open, they don’t get the ribbon warning, but get a pop-up box instead. But hey, if they have the VBE open then they probably know to run macros…

  3. Jon

    For my time card add-in I have the workbook open from the xll file. I also have a short cut that opens the xll file.

    Clever ideas. I like Jeff’s idea too, maybe a combination of both, hide worksheets and have a banner. Unfortunately it wouldn’t work with mine since there is no open workbook. It would probably work for mine if I did that just in case someone opened up the workbook without opening the add-in first like they should.

  4. Jon Peltier

    I hide the sheets in the before_save event procedure, save the workbook, unhide the sheets, and set .Saved to True. This mimics Excel’s closing “do you want to save?” behavior, while always saving the workbook with sheets hidden.

  5. Jeff Weir

    Jon – do you mean something like this:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Sheets("START").Visible = xlSheetVisible
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name "START" Then
    ws.Visible = xlVeryHidden
    End If
    Next ws

    Application.EnableEvents = False
    ActiveWorkbook.Save
    Application.EnableEvents = True

    For Each ws In ThisWorkbook.Worksheets
    If ws.Name "START" Then
    ws.Visible = xlSheetVisible
    End If
    Next ws
    Sheets("START").Visible = xlVeryHidden

    ActiveWorkbook.Saved = True
    Application.ScreenUpdating = True
    Cancel = True

    End Sub

    Private Sub Workbook_Open()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name "START" Then
    ws.Visible = xlSheetVisible
    End If
    Next ws
    Sheets("START").Visible = xlVeryHidden
    Application.ScreenUpdating = True
    End Sub

    If so, on workbook close, I get a message saying “Do you want to save changes” which merrily goes into a loop if I click Yes. If i click No then it’s fine.

    Can you post your code?

  6. Peter van Klinken

    Great idea. Most user know how they need to enable macro’s to use all the functions within the worksheet.
    This addition makes sure all others will know as well.

    Peter

  7. Jeff Weir

    Update: in regards to my comments above, while that additional EnableEvents=False in the Before_Close procedure stops the user getting in a loop where they click ‘yes’ to “Do you want to save changes”, the downside is that events are disabled for any other workbooks open, which is not acceptable.

    So I don’t know how to get around this. Love to see a sample routine of your code, Jon, so I can see how you’re getting around this (or more likely how I’m misinterpreting your approach).

  8. Boxie

    Worked an absolute charm, many thanks – and the best explanation I could find for a ‘VBA Dummy’ by far…

  9. Sidney Falco

    There is a massive bug in this approach which highlights something that all people writing macros should consider.

    1. Open the workbook and ignore the Macros prompt.
    2. Rename the START worksheet STOP
    3. Same the workbook

    Enjoy the mayhem that ensues!

    The macro expect the tab name to be START but the user have renamed it so the macro no longer works.

    The moral is to never refernece objects (in this case sheet visible names) using values that can be changed by the end user.

    There are various ways you can find the correct sheet in a macro without using the visible name for the sheet. I prefer to use the contents of a non-visble cell which is unique to that sheet which cannot be changed by an end user.

  10. Sidney Falco

    To get round the problem with the sheetname being changed by the user I set the name of the worksheet to “MacroWarningSheet” in the VBA editor. This means that the code is not affected if the user ranames the sheet.

    Rather than use the Before_Close event I suggest that you use the Before_Save event as below. This avoids the trouble of asking the user if they want to save changes etc. If Before_Save is called the workbook is aout to be saved.

    I also changed the Workbook_open function to set saved to True after the visibility of the sheets are made as these are not changes made by the user so can be suppressed. If the user does not make any changes to the workbook they will not be prompted to save the workbook and Before_save will not be called.
    .
    Private Sub Workbook_Open()

    Dim ws As Worksheet

    ‘ Make all hidden sheets visible first

    For Each ws In ThisWorkbook.Worksheets

    If ws.CodeName “MacroWarningSheet” Then
    ws.Visible = xlSheetVisible
    End If

    Next ws

    ‘ Now hide the macro warning sheet – cannot hide first as Excel does not allow all sheets in a workbook to be hidden

    For Each ws In ThisWorkbook.Worksheets

    If ws.CodeName = “MacroWarningSheet” Then
    ws.Visible = xlSheetVeryHidden
    End If

    Next ws

    ‘ Disregard the above changes as real modification in the workbook
    ‘ If the user makes any changes then BeforeSave will be called
    ‘ if/when they save the workbook

    Me.Saved = True

    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim ws As Worksheet

    ‘ Make sure that the macro warning sheet is visible

    For Each ws In ThisWorkbook.Worksheets

    If ws.CodeName = “MacroWarningSheet” Then
    ws.Visible = xlSheetVisible
    End If

    Next ws

    ‘ Now, very hide all but the macro warning sheet

    For Each ws In ThisWorkbook.Worksheets

    If ws.CodeName “MacroWarningSheet” Then
    ws.Visible = xlSheetVeryHidden
    End If

    Next ws

    End Sub

  11. Sidney Falco

    Two lines in the above post have lost the “” test. (Not equal to)

    If ws.CodeName ?? “MacroWarningSheet” Then

    The ?? needs to be replaced with the not equal to characters! “” combined.

    I’ve seen this problem with posts on other sites.

    Hope this makes sense!

  12. Carol Corley

    I have a similiar problem and haven’t found discussion of this. Can any of you help me?

    I have an Excel 2010 macro enabled template used for employees to fill in cells as they are talking to clients. I found some great very short code to allow spellcheck to work in the protected sheet that unlocks the sheet with the password, spellchecks it and sets the password again.

    It works wonderful in the template. However, once it is saved, the macro will not run again.

    How do I force the Save As button on the quick launch toolbar to set the file type to Macro Enabled Workbook. I am very sure if this is left up to the employees that step will be omitted when they are in a hurry.

    I need this ASAP, or sooner.

    Thank you in advance for your help.
    Carol

  13. datapig

    Carol:
    You can use the BeforeSave workbook event to hijack the save process.
    This code will disable the standard SaveAs dialog box and will force the user to SaveAs an XLSM using your own dialog box.
    Enter this code into the Workbook_BeforeSave event.

    1. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    2.  
    3. Dim fName As String
    4.  
    5. If SaveAsUI Then
    6.     Cancel = True
    7. End If
    8.  
    9.  
    10. fName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
    11. If fName = "False" Then
    12. MsgBox "Action Cancelled", vbOKOnly
    13. Cancel = True
    14. Exit Sub
    15. End If
    16.  
    17. Application.EnableEvents = False
    18. ThisWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    19. Application.EnableEvents = True
    20. End Sub
  14. Carol Corley

    Thank you so much for your quick reply. I have been trying to get this to work, but I must be missing something. It seems to only work if I run it from the VB window.

    From the template it doesn’t matter if I click File/Save As, Click the Save button, or Ctrl/S, I still get the Save As box with the file type as Excel Workbook – not macro enabled.

    And the macro is not listed to assign it to a button to replace the Save button, which is my ultimate goal. The employees need to save the resulting file and I know they will not take time to change file type.

    Thank you in advance for your help.
    Carol

  15. Charlie

    I wrote a solution like this. I called my first sheet “Macro Alert”. I provided images of what to click on to enable macros. Don’t forget to also put the SheetsHide subroutine in Workbook_BeforeSave. Otherwise, if the user clicks “Save” and then exits Excel without saving over it, the workbook will not be saved in the Macro Alert state. You will also need a button on the Macro Alert sheet to do a SheetsUnhide once they workbook is saved, so the users can return to doing their work. They will complain about this but, too bad. Thanks Microsoft, for changing the “Enable Macros” pop-up from 2003.

  16. Liezl

    Firstly, this is fantastic, thank you!

    I do however have a question and hope someone could help. I know every little about macro’s and are pretty much only able to copy and paste code which I was able to do successfully based on the good instructions on this page.
    Having said that, my problem is that I do not want all sheets in the workbook to open once macro is enabled.
    At the moment the code suggest all ws to un-hide, how can I change the code to allow for only 2 of my 8 tabs to unhide?

    Many thanks and great stuff!

  17. Jeff Weir

    This should do it. Just copy these into the ThisWorkbook code module (replacing any previous Workbook_Open or Workbook_Close routines) and then change the sheet names from “SomeSheetNameThatNeedsToBeHidden” and “AnotherSheetNameThatNeedsToBeHidden” in the code to whatever your sheetnames are.

    (Or even better, refer to Mike’s latest post about using sheet CODE names rather than sheet names. THat way, if someone changes the sheetnames of either of those two tabs, this code will still work.)


    Private Sub Workbook_Open()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
    Case "SomeSheetNameThatNeedsToBeHidden", "AnotherSheetNameThatNeedsToBeHidden"
    ws.Visible = xlSheetVisible
    Case Else
    ws.Visible = xlSheetVeryHidden
    End Select
    Next ws

    End Sub

    Private Sub Workbook_Close()

    Dim ws As Worksheet
    Worksheets("Start").Visible = xlSheetVisible
    For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
    Case "SomeSheetNameThatNeedsToBeHidden", "AnotherSheetNameThatNeedsToBeHidden"
    ws.Visible = xlSheetVeryHidden
    End Select
    Next ws

    End Sub

  18. Tracy

    I was able to force the enable macro with a code and works perfectly; however, I also need to disable the “Save As” feature. Its seems to be one or the other….is there another code to use for the BeforeSave that will allow both?
    This is the code to disable the Save As:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True Then
    MsgBox “The ‘Save As’ function has been disabled.”
    Cancel = True
    End If
    End Sub

  19. Denise

    Hi all. I have created a macro in a workbook to force users to enable macros. What I have done basically

    – workbook open, all sheets except for welcome page hidden with password and a different password to load workbook
    – once correct password is entered, all sheets are shown..wrong password, workbook shuts down
    – before workbook closes, different password to save and if correct password is entered, it saves and then hide all sheets except for welcome page…if wrong password, workbook doesn’t save

    They all work fine but there is a flaw. By using workbook_beforesave, everytime I make changes halfway through and need to save it but not exit the workbook, all sheets get hidden and I have to exit workbook, reopen it before I can continue.

    Is it possible to alter the macro to recognise when it is just saving, to prompt a password box and if password is correct, all sheets remain shown….and only when save and exit, the sheets get hidden when correct password entered?

    Hope my lengthy explanation makes sense!

    Thanks heaps,
    Denise

  20. a_27826

    If you email the workbook from within Excel once it has been opened and macros enabled, does the code work for the recipient of the mail?

  21. random person

    I’ve been fiddling around with this and i edited Sidney Falco’s answer a bit. Basically i am displaying all the sheets on opening the worksheet and after saving it, and aim hiding the worksheets before saving. just remember to rename the worksheet to “MacroWarningSheet” in the VBA editor.

    my code follows:

    Option Explicit

    Private bSaved As Boolean '-- used to pass value from BeforSave to AfterSave

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    bSaved = Me.Saved '-- remember the current Saved status
    If Me.Saved Then Me.Saved = False '-- assume the workbook was not Saved

    'MsgBox "BeforeSave"
    '-- Code for BeforeSave here if required
    Dim ws As Worksheet
    'Make sure that the macro warning sheet is visible
    For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName = "MacroWarningSheet" Then
    ws.Visible = xlSheetVisible
    End If
    Next ws
    'Now, very hide all but the macro warning sheet
    For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName "MacroWarningSheet" Then
    ws.Visible = xlSheetVeryHidden
    End If
    Next ws

    Application.OnTime Now, "ThisWorkbook.Workbook_AfterSave"
    '-- the call to Workbook_AfterSave will be delayed until the saving is
    '-- completed or cancelled
    End Sub

    Private Sub Workbook_AfterSave() '-- a fake event that will be called by BeforeSave
    If Me.Saved = False Then '-- Save has been cancelled either in code or in SaveAsUI
    If bSaved Then Me.Saved = True '-- reset to previous Saved status
    Exit Sub '-- Save was not occured so cancel AfterSave
    End If

    'MsgBox "AfterSave"
    '-- Code for AfterSave here
    Dim ws As Worksheet
    'Make all hidden sheets visible first
    For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName "MacroWarningSheet" Then
    ws.Visible = xlSheetVisible
    End If
    Next ws
    'Now hide the macro warning sheet – cannot hide first as Excel does not allow all sheets in a workbook to be hidden
    For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName = "MacroWarningSheet" Then
    ws.Visible = xlSheetVeryHidden
    End If
    Next ws

    End Sub

    Private Sub Workbook_Open()
    Dim ws As Worksheet
    'Make all hidden sheets visible first
    For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName "MacroWarningSheet" Then
    ws.Visible = xlSheetVisible
    End If
    Next ws
    'Now hide the macro warning sheet – cannot hide first as Excel does not allow all sheets in a workbook to be hidden
    For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName = "MacroWarningSheet" Then
    ws.Visible = xlSheetVeryHidden
    End If
    Next ws
    End Sub

  22. random person

    in my code above you need to change:
    If ws.CodeName “MacroWarningSheet” Then
    into
    If ws.CodeName <> “MacroWarningSheet”

  23. Dave Sheppard

    Hi everybody. I have been using a similar technique for a couple of years now with no problems arising… until today. This is worth sharing if it can help somebody else avoid a messy situation with a forced-save command in a BeforeClose event. Previous to using the forced-save in the BeforeClose event, I had issues of users sidestepping the unhiding/hiding/forcing macros routine by saving the file when the are finished working on it, then closing and selecting “Don’t Save”.

    Some specifics of my setup:
    1) When file opens, the “START” sheet is the only one visible with an ‘enable macros’ instruction image covering an “Open File” command button. Once macros are enabled, the image disappears and gives access to the command button.

    2) By pressing the “Open File” button with macros enabled, working sheets are made visible, and the start sheet is hidden.

    3) BeforeClose, start sheet is set as visible, working sheets set to hidden, and the ‘enable macros’ instruction image restored to provide instructions when the file is opened again. And, a forced-save command in the BeforeClose event to ensure the sheets are set up properly to force enabling macros upon reopening.

    HOWEVER, today I had a client go through a couple pages of entries which turned out to be incorrect. They noticed the error and thought to close the workbook without saving to clear their entries and start again (which is reasonable, as this is common practice for a lot of people). But the forced-save command in the BeforeClose event removed their option to select “Don’t Save” upon closing… and has saved all the entries they intended to NOT save by closing the file.

    The lesson learned is to be cautious with a forced save if there is any chance that a user may need to exit the file without saving to avoid leaving a mess behind.

    Has anybody else had this experience? If so, how did you modify your approach to deal with it?

    Cheers!

  24. Jeff Nagel

    I have been grappling with the same issue of forcing a save when the user might not want to save his changes.

    I am developing a work around that takes into account the possibility that the user saves the workbook at least once while using it and decides that changes after the last save should be ignored. If the user never saves the workbook at all, and you do not default to saving it, the workbook will revert back to the state of only having the START page visible.

    Since my workbook has only two sheets that the user has access to, my approach is as follows:
    1) Every time the user saves the workbook, I copy the data and formatting to sheets that now hold the last saved changes.
    2) In the Workbook_Close event I replace the Excel warning message to save the workbook with my own. (a) If the user wants to save the workbook, I delete the Last Saved Changes sheets and save the workbook. (b) If the user does not want to save the workbook, I copy the data and formatting from my Last Saved Changes sheets into the user sheets, delete Last Saved Changes sheets and save the workbook (to be able to hide the sheets).

    The above approach is not very efficient, especially if the workbook was very large. Anyone have a better way to do this?

    Thanks.
    Jeff

  25. Dave Sheppard

    Jeff, that is a great idea. I have some simple templates that get distributed for data entry by various people that your suggestion is perfect for. Fast and easy to implement, and efficient enough for small data sets. Thanks for sharing!
    -Dave

  26. David Whitney

    @Jeff I know this is an old thread but I was looking for something specific and had to read the whole thing.

    You do not have to store the last saved changes on worksheets. Consider using dictionary objects or arrays. Each lend themselves to being pasted into ranges.

    Meanwhile, I was looking for what to do when user’s Excel setting is enableevents=false on startup. I want to force enableevents to true so the rest of the code will work.

    Thanks,

    David

  27. Petros

    I would like to examine the contents of a workbook with all sheets very-hidden except one, before enabling macros.

    VBE and workbook structure are password protected in 2013 and password hacking is not an option.

    Any ideas of what to do next?
    Thanks
    Petros

  28. Spoony

    I have been using this idea for some time but a common way I’ve found of people sidestepping the save code is by using Send and selecting email when they are finished.

    This leaves the workbook with the sheets on show with macros then disabled for the recipient.

    Anybody have any ideas how to stop this?

  29. Hasan

    Hi all. I struggle for some time with the problem which Dave Sheppard described in his post (April 15, 2014 at 7:29 pm).

    As I have a workbook with lots of sheets and user entry possibilitites, the suggested workarounds would not work for me.

    What do you think of utilising the ReadOnly mode?

    So for the case where user made changes but wants to close without saving… I have tried the following in a simple file and it seems to work:

    Sub revertBackToLastSavedVersion()

    Dim wbk As Workbook
    Dim sFileNameMaster As String

    Set wbk = ActiveWorkbook
    sFileNameMaster = wbk.FullName

    wbk.Saved = True ‘prevents the alert
    wbk.ChangeFileAccess Mode:=xlReadOnly

    Workbooks.Open sFileNameMaster

    End Sub

    Now that I have the file version, which user wants to keep, I can hide all sheets except for the one with the message “You must enable macros” and close the file.

    Any feedback highly appreciated!

    Hasan

  30. Wim Gielis

    Or… if you don’t care about showing a custom page, you can do it very quickly and without all the hassle.

    Just create a simple UDF in VBA.
    In your main worksheet (or a cover page), call the function. If you use IFERROR you can trap the fact that macro’s are disabled – the VBA UDF would return a NAME error. Hence, in case of error, show a descriptive message like :”Please ENABLE macro’s to enjoy using this workbook”

    Done.

  31. Brendan

    The above code works great with my Excel 2007, but I loaded the spread sheet onto another computer with a later Excel version and got run time error 1004

  32. Ramesh Kumar

    Hai,

    I have face one major problem in this macro for auto-saved and close option.
    I am set the time in 10Sce for file automatically save and closed. But again re-open the file, it not show the index file (Macro enable warning sheet) directly show the main sheet for without enabled macro.

    So pls help and solve this issues.

    Below codes are I am used…,,,,

    Code is This workbook

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ‘Step 1: Declare your variables
    Dim ws As Worksheet

    ‘Step 2: Unhide the Starting Sheet
    Sheets(“START”).Visible = xlSheetVisible

    ‘Step 3: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets

    ‘Step 4: Check each worksheet name
    If ws.Name “START” Then

    ‘Step 5: Hide the sheet
    ws.Visible = xlVeryHidden
    End If

    ‘Step 6: Loop to next worksheet
    Next ws

    ‘Step 7: Save the workbook
    ActiveWorkbook.Save

    End Sub

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Reset
    End Sub

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Reset
    End Sub

    Private Sub Workbook_Open()
    Reset

    ‘Step 1: Declare your variables
    Dim ws As Worksheet

    ‘Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets

    ‘Step 3: Unhide All Worksheets
    ws.Visible = xlSheetVisible

    ‘Step 5: Loop to next worksheet
    Next ws

    ‘Step 6: Hide the Start Sheet
    Sheets(“START”).Visible = xlVeryHidden

    End Sub

    —————————————

    Code in Module…,,,,

    Sub Reset()
    Static SchedSave
    If SchedSave 0 Then
    Application.OnTime SchedSave, “SaveWork”, , False
    End If
    SchedSave = Now + TimeValue(“00:00:10”) ‘ 10 seconds
    Application.OnTime SchedSave, “SaveWork”, , True
    End Sub
    Sub SaveWork()
    ThisWorkbook.Save
    ThisWorkbook.Close
    End Sub

    Anybody resolved my problems….? and update the code.

    Regards,
    Ramesh Kumar.

  33. Vince

    Hi
    I am using some code fairly similar to the above but wondered if it is possible to make a list of the visible worksheets when the workbook is closed? Basically my workbook has many sheets some of which the user can hide if they are not needed for a particular project they are working on. I am trying to avoid the user having to re-hide the sheets once the workbook is opened up with the macros enabled. Hope that makes sense!
    Any help will be much appreciated.
    Regards
    Vince

  34. Chittaranjan

    Hello All,

    Need help on one thing, Whenever i open Excel which containing macro an error occurs as “Security Warning Macro have been Disable. Enable Content” . After clicking on Enable content button the excel open in good manner.
    Now I have to disable any cell value or change color to while, let say cell F5 having text “Best of Luck” which is in black color. I record the macro as to change it to white color/it should be disabled. OK.

    My Question is, whenever we click on Enable content button, the text color “Best of Luck” of cell F5 should be change to white/ disappear.

    So How to do, need help.

Leave a Reply

Your email address will not be published. Required fields are marked *