AndresHernando
asked on
Force macros while incorporating previous functionality
I want to have code that forces the user to enable macros, while still incorporating the previous functionality that occurs on open and close. I got this code (seen directly below) from Expert’s Exchange and tried to run it on an empty workbook and it seemed to work.
==========================
Option Explicit
Dim blnClosing As Boolean
Private Sub Workbook_BeforeClose(Cance
blnClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("Sheet1").Visib
Worksheets("Sheet2").Visib
Worksheets("Sheet1").Activ
If Not blnClosing Then
Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), Procedure:="ThisWorkbook.S
End If
End Sub
Private Sub Workbook_Open()
blnClosing = False
Worksheets("Sheet1").Visib
Worksheets("Sheet2").Visib
Worksheets("Sheet2").Activ
End Sub
Public Sub ShowCorrect()
Call Workbook_Open
End Sub
==========================
So I adapted it to my application, and seemed to work again. The problem was that after saving and closing it a couple of times, it will then open without forcing me to enable macros.
I am trying to use the above code to force the user to enable macros, while incorporating the functionality of the code seen below. Sheet 1 above would become Sheet 4 (the mostly empty Macro Prompt sheet), while I would want Sheets 1, 2 and 3 from below (the sheets displaying the data) to be used in the same way that Sheet 2 from above is used (to be visible only when macros are enabled).
==========================
Public Sub Workbook_Open()
ThisWorkbook.Protect Password:="locked"
Sheet1.EnableAutoFilter = True
Sheet1.Protect Password:="locked", _
Contents:=True, UserInterfaceOnly:=True
Sheet3.Protect Password:="locked"
End Sub
Private Sub Workbook_BeforeClose(Cance
ThisWorkbook.Unprotect Password:="locked"
Sheet1.Protect Password:="locked", _
Contents:=True, UserInterfaceOnly:=True
Sheet3.Protect Password:="locked"
Sheet2.Visible = xlSheetHidden
End Sub
==========================
I don’t know if the problem is between the version I’m using (Excel 2010) and previous versions, but even if I just used the stripped down code on an empty workbook, it still only works for only 1 or 2 openings. I even tried other code I found to do this same function (http://www.vbaexpress.com/kb/getarticle.php?kb_id=578), and I got the same results. At this point I’m spinning my wheels and would appreciate any help.
Thanks
==========================
Option Explicit
Dim blnClosing As Boolean
Private Sub Workbook_BeforeClose(Cance
blnClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("Sheet1").Visib
Worksheets("Sheet2").Visib
Worksheets("Sheet1").Activ
If Not blnClosing Then
Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), Procedure:="ThisWorkbook.S
End If
End Sub
Private Sub Workbook_Open()
blnClosing = False
Worksheets("Sheet1").Visib
Worksheets("Sheet2").Visib
Worksheets("Sheet2").Activ
End Sub
Public Sub ShowCorrect()
Call Workbook_Open
End Sub
==========================
So I adapted it to my application, and seemed to work again. The problem was that after saving and closing it a couple of times, it will then open without forcing me to enable macros.
I am trying to use the above code to force the user to enable macros, while incorporating the functionality of the code seen below. Sheet 1 above would become Sheet 4 (the mostly empty Macro Prompt sheet), while I would want Sheets 1, 2 and 3 from below (the sheets displaying the data) to be used in the same way that Sheet 2 from above is used (to be visible only when macros are enabled).
==========================
Public Sub Workbook_Open()
ThisWorkbook.Protect Password:="locked"
Sheet1.EnableAutoFilter = True
Sheet1.Protect Password:="locked", _
Contents:=True, UserInterfaceOnly:=True
Sheet3.Protect Password:="locked"
End Sub
Private Sub Workbook_BeforeClose(Cance
ThisWorkbook.Unprotect Password:="locked"
Sheet1.Protect Password:="locked", _
Contents:=True, UserInterfaceOnly:=True
Sheet3.Protect Password:="locked"
Sheet2.Visible = xlSheetHidden
End Sub
==========================
I don’t know if the problem is between the version I’m using (Excel 2010) and previous versions, but even if I just used the stripped down code on an empty workbook, it still only works for only 1 or 2 openings. I even tried other code I found to do this same function (http://www.vbaexpress.com/kb/getarticle.php?kb_id=578), and I got the same results. At this point I’m spinning my wheels and would appreciate any help.
Thanks
ASKER
Which are some of the reasons why I'm so baffled with this issue.
does the strange behavior also take place when you have disabled all add-ins ?
ASKER
The only add-ins that I had selected were the following:
- Visual Basic For Applications
- Miscosoft Excel 14.0 Objects Library
- OLE Automation
- Microsoft Office 14.0 Object Library
I disabled the last 2:
- OLE Automation
- Microsoft Office 14.0 Object Library
When I tried to disable the first 2 it returned a message that these can't be removed.
So, after de-selecting the last 2 add-ins, I tried the basic code for forcing a macro in a workbook without data. It still didn't work.
- Visual Basic For Applications
- Miscosoft Excel 14.0 Objects Library
- OLE Automation
- Microsoft Office 14.0 Object Library
I disabled the last 2:
- OLE Automation
- Microsoft Office 14.0 Object Library
When I tried to disable the first 2 it returned a message that these can't be removed.
So, after de-selecting the last 2 add-ins, I tried the basic code for forcing a macro in a workbook without data. It still didn't work.
these are references which are in use by the VBA code inside a specific spreadsheet, and are also limited to being used inside of the particular spreadsheet they reside in. Add-ins are files that contain vba macro code and will be used throughout excel : in each and every spreadsheet that is open.
in excel 2010 you can find the active add-ins using these steps :
Click the File tab, click Options, and then click the Add-Ins category.
In the Manage box, click Excel Add-ins, and then click Go. The Add-Ins dialog box appears.
In the Add-Ins available box, deselect all check boxes to disable the addins, and then click OK.
For more information, visit microsoft support
in excel 2010 you can find the active add-ins using these steps :
Click the File tab, click Options, and then click the Add-Ins category.
In the Manage box, click Excel Add-ins, and then click Go. The Add-Ins dialog box appears.
In the Add-Ins available box, deselect all check boxes to disable the addins, and then click OK.
For more information, visit microsoft support
ASKER
I checked the add-ins (where you and MSDN said to go) and there are none that are checkmarked. And I also checked the project window in VBA and it doesn't show any add-ins running either.
hi,
Are you running any code (esp code that you end up resetting rather than running to completion) in between opening & closing the file?
Do you get "true" or "false" if you type "?application.enableevents
If you get false, I suggest typing "application.enableevents = true" in the Immediate pane & then pressing [Enter]. Then try closing & opening the file again...
If this fixes it, search all of your code for an unmatched "application.enableevents = false" and then add the corresponding "application.enableevents = true" at the end of the relevant code.
hth
Rob
Are you running any code (esp code that you end up resetting rather than running to completion) in between opening & closing the file?
Do you get "true" or "false" if you type "?application.enableevents
If you get false, I suggest typing "application.enableevents = true" in the Immediate pane & then pressing [Enter]. Then try closing & opening the file again...
If this fixes it, search all of your code for an unmatched "application.enableevents = false" and then add the corresponding "application.enableevents = true" at the end of the relevant code.
hth
Rob
ASKER
Hi Rob,
When I type that in the Immediate pane I get "True."
When I type that in the Immediate pane I get "True."
Hmmm, well that means that my stab in the dark didn't end up being the lucky guess that I was hoping for. Here are some more questions that may help narrow down the cause...
- What do you mean by "it still only works for only 1 or 2 openings", do you mean one or two times opening & closing the file repeatedly in the same instance of excel?
- Or do you mean opening the file, closing it at the same time as closing the instance of excel, and then re-opening the file in a new instance of excel?
- The positioning of "Dim blnClosing As Boolean" above the Sub's makes it a global/public variable and, although I can't see what difference it would be making to your issue, it is possible that it is losing its state/value. This could be overcome by using a defined Name which contains "True" or "False" & is hidden to prevent user interference. Does the use of a defined Name instead of a global variable stop the issue?
- Do you have any other code in any file that is open at the same time?
- Akoster has given you links & mentioned where to find the the "Excel Addins" section, there are also, "COM Addins" which may (I don't really know?) also be causing the change. What happens if you disable/unload any Com Addins as well as the excel addins?
- Does it make any difference if you open excel using the "safe mode" by pressing [ [Windows button] + r], typing "excel.exe /s" & pressing [ok]?
(see http://support.microsoft.com/kb/291288 for more details)
- I don't have excel 2010 so I can't do any version specific testing, but can you please upload an example file that you have built from a blank file, and we may (or may not!) be able to spot something...?
- Again, I don't have excel 2010 so my shortcuts may not be correct, but can you please list all your settings that are shown under the various options that exist within:
[alt + t + o] - Trust Center - Trust Center Settings...?
I'm sorry I don't have any more ideas. I'll mention this to others and then hopefully someone else can provide an answer.
Rob
- What do you mean by "it still only works for only 1 or 2 openings", do you mean one or two times opening & closing the file repeatedly in the same instance of excel?
- Or do you mean opening the file, closing it at the same time as closing the instance of excel, and then re-opening the file in a new instance of excel?
- The positioning of "Dim blnClosing As Boolean" above the Sub's makes it a global/public variable and, although I can't see what difference it would be making to your issue, it is possible that it is losing its state/value. This could be overcome by using a defined Name which contains "True" or "False" & is hidden to prevent user interference. Does the use of a defined Name instead of a global variable stop the issue?
- Do you have any other code in any file that is open at the same time?
- Akoster has given you links & mentioned where to find the the "Excel Addins" section, there are also, "COM Addins" which may (I don't really know?) also be causing the change. What happens if you disable/unload any Com Addins as well as the excel addins?
- Does it make any difference if you open excel using the "safe mode" by pressing [ [Windows button] + r], typing "excel.exe /s" & pressing [ok]?
(see http://support.microsoft.com/kb/291288 for more details)
- I don't have excel 2010 so I can't do any version specific testing, but can you please upload an example file that you have built from a blank file, and we may (or may not!) be able to spot something...?
- Again, I don't have excel 2010 so my shortcuts may not be correct, but can you please list all your settings that are shown under the various options that exist within:
[alt + t + o] - Trust Center - Trust Center Settings...?
I'm sorry I don't have any more ideas. I'll mention this to others and then hopefully someone else can provide an answer.
Rob
In the original question, if there are only two worksheets in the workbook, then the order of statements in the Workbook_Open sub is incorrect. You can't make all the worksheet xlVeryHidden--as they would be after the third statement in that sub. Instead, it should have been:
I have had issues with public variables retaining their values throughout an Excel session. It appears that a fatal error will cause those variables to lose their values. For this reason, you may want to make blnClosing a named value in the workbook. It's just like a named range, except that it returns either =TRUE or =FALSE rather than a cell reference. These values are retained as long as the workbook is open. You would refer to and change its value with statements like:
MsgBox ThisWorkbook.Names("blnClo
ThisWorkbook.Names("blnClo
Private Sub Workbook_Open()
blnClosing = False
Worksheets("Sheet2").Visible = True
Worksheets("Sheet1").Visible = xlVeryHidden
Worksheets("Sheet2").Activate
End SubI have had issues with public variables retaining their values throughout an Excel session. It appears that a fatal error will cause those variables to lose their values. For this reason, you may want to make blnClosing a named value in the workbook. It's just like a named range, except that it returns either =TRUE or =FALSE rather than a cell reference. These values are retained as long as the workbook is open. You would refer to and change its value with statements like:
MsgBox ThisWorkbook.Names("blnClo
ThisWorkbook.Names("blnClo
ASKER
Rob,
Here are my responses:
Q - What do you mean by "it still only works for only 1 or 2 openings", do you mean one or two times opening & closing the file repeatedly in the same instance of excel? - Or do you mean opening the file, closing it at the same time as closing the instance of excel, and then re-opening the file in a new instance of excel?
A - The 2nd one. I completely close out the instance of Excel before reopening.
Q - Do you have any other code in any file that is open at the same time?
A- Another sheet has code in it. Perhaps that is causing some interference?
Q - Akoster has given you links & mentioned where to find the the "Excel Addins" section, there are also, "COM Addins" which may (I don't really know?) also be causing the change. What happens if you disable/unload any Com Addins as well as the excel addins?
A - I tried all of his suggestions, and they didn't seem to have any effect. But it is quite possible that I was doing something incorrectly.
Q - Does it make any difference if you open excel using the "safe mode" by pressing [ [Windows button] + r], typing "excel.exe /s" & pressing [ok]?
(see http://support.microsoft.com/kb/291288 for more details)
A - That made the stripped down version of the code work.
Q - I don't have excel 2010 so I can't do any version specific testing, but can you please upload an example file that you have built from a blank file, and we may (or may not!) be able to spot something...?
A - I will scrub a workbook so I can upload it for you to review.
Q - Again, I don't have excel 2010 so my shortcuts may not be correct, but can you please list all your settings that are shown under the various options that exist within:
[alt + t + o] - Trust Center - Trust Center Settings...?
A - Trusted Publishers = nothing
Trusted Locations = nothing
Trusted Documents, "Allow documents on a network to be trusted" is checked
Add-ins, "Disable notification for unsigned add-ins" is checked but greyed out
ActiveX Settings, "Prompt me before enabling Unsafe for Initialization (UFI) controls ..." is checked but greyed out
Macro Settings, the "Disable all macros with notification" radio button is selected
Protected View, all boxes are checked, and the "Data Execution Prevention" box is checked but greyed out
Message Bar, only the "Show the Message Bar in all application when active content, such as ActiveX controls and macros, has been blocked" radio button is selected, but is greyed out
External Content, the "Prompt user about Data Connections" and "Prompt user on automatic update for Workbook Links" radio buttons are selected
File Block Settings, the following boxes are checked: Excel 4 Workbooks, Excel 4 Worksheets, Excel 3 Worksheets, Excel 2 Worksheets (greyed out), Excel 4 Macrosheets and Add-in Files, Excel 3 Macrosheets and Add-in Files, Excel 2 Macrosheets and Add-in Files; and the "Open selected file types in Protected View" radio button is selected
Privacy Options, the following boxes are checked: "Connect to Office.com for updated content when I'm connect to the Internet," "Automatically detect installed Office applications...," "Check Microsoft Office documents...," "Allow the Research task pane to check for...," and "Allow sending files to improve file validation."
Thanks for the help. I will try to get the scrubbed workbook up within a few hours from now.
Here are my responses:
Q - What do you mean by "it still only works for only 1 or 2 openings", do you mean one or two times opening & closing the file repeatedly in the same instance of excel? - Or do you mean opening the file, closing it at the same time as closing the instance of excel, and then re-opening the file in a new instance of excel?
A - The 2nd one. I completely close out the instance of Excel before reopening.
Q - Do you have any other code in any file that is open at the same time?
A- Another sheet has code in it. Perhaps that is causing some interference?
Q - Akoster has given you links & mentioned where to find the the "Excel Addins" section, there are also, "COM Addins" which may (I don't really know?) also be causing the change. What happens if you disable/unload any Com Addins as well as the excel addins?
A - I tried all of his suggestions, and they didn't seem to have any effect. But it is quite possible that I was doing something incorrectly.
Q - Does it make any difference if you open excel using the "safe mode" by pressing [ [Windows button] + r], typing "excel.exe /s" & pressing [ok]?
(see http://support.microsoft.com/kb/291288 for more details)
A - That made the stripped down version of the code work.
Q - I don't have excel 2010 so I can't do any version specific testing, but can you please upload an example file that you have built from a blank file, and we may (or may not!) be able to spot something...?
A - I will scrub a workbook so I can upload it for you to review.
Q - Again, I don't have excel 2010 so my shortcuts may not be correct, but can you please list all your settings that are shown under the various options that exist within:
[alt + t + o] - Trust Center - Trust Center Settings...?
A - Trusted Publishers = nothing
Trusted Locations = nothing
Trusted Documents, "Allow documents on a network to be trusted" is checked
Add-ins, "Disable notification for unsigned add-ins" is checked but greyed out
ActiveX Settings, "Prompt me before enabling Unsafe for Initialization (UFI) controls ..." is checked but greyed out
Macro Settings, the "Disable all macros with notification" radio button is selected
Protected View, all boxes are checked, and the "Data Execution Prevention" box is checked but greyed out
Message Bar, only the "Show the Message Bar in all application when active content, such as ActiveX controls and macros, has been blocked" radio button is selected, but is greyed out
External Content, the "Prompt user about Data Connections" and "Prompt user on automatic update for Workbook Links" radio buttons are selected
File Block Settings, the following boxes are checked: Excel 4 Workbooks, Excel 4 Worksheets, Excel 3 Worksheets, Excel 2 Worksheets (greyed out), Excel 4 Macrosheets and Add-in Files, Excel 3 Macrosheets and Add-in Files, Excel 2 Macrosheets and Add-in Files; and the "Open selected file types in Protected View" radio button is selected
Privacy Options, the following boxes are checked: "Connect to Office.com for updated content when I'm connect to the Internet," "Automatically detect installed Office applications...," "Check Microsoft Office documents...," "Allow the Research task pane to check for...," and "Allow sending files to improve file validation."
Thanks for the help. I will try to get the scrubbed workbook up within a few hours from now.
ASKER
Rob,
Here is the sanitized file (please see attached). For some reason, now I'm getting an error on the code for one of the hidden tabs when I open this workbook. But I didn't want to alter anything because at least the prompt tab is showing, and it's the only one. That's what I want to occur when I start it, until I enable macros to make the other tabs appear and the prompt tab disappear.
Thanks
AR-Workbook---10Oct11.xlsm
Here is the sanitized file (please see attached). For some reason, now I'm getting an error on the code for one of the hidden tabs when I open this workbook. But I didn't want to alter anything because at least the prompt tab is showing, and it's the only one. That's what I want to occur when I start it, until I enable macros to make the other tabs appear and the prompt tab disappear.
Thanks
AR-Workbook---10Oct11.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to everyone for helping me on this issue.
AndresHernando,
Could you please post a link to the Experts Exchange question or article that was the source for the code in your original question? I'd like to comment in the original source if it contains the mistake I was describing.
Brad
Could you please post a link to the Experts Exchange question or article that was the source for the code in your original question? I'd like to comment in the original source if it contains the mistake I was describing.
Brad
Hi AndreasHernando,
I'm pleased Brad was able to help you out - it's a slight bummer that you'd already spent the time typing up the answers to all my questions!
Brad,
Thanks for helping out & working through the solution. I completely missed the order of the sheet visibility statements but I'm pleased one of my ideas was on the right track ("it is a global/public variable and ... it is possible that it is losing its state/value.").
btw, I think I may have found the original thread... https://www.experts-exchange.com/questions/20424197/Excel-enable-macro.html - note the original statement order may have moved further afield via Dreamboat's site (I haven't checked). I'll leave the commenting in the original thread up to you as you will probably be more eloquent than me ;-)
Rob
I'm pleased Brad was able to help you out - it's a slight bummer that you'd already spent the time typing up the answers to all my questions!
Brad,
Thanks for helping out & working through the solution. I completely missed the order of the sheet visibility statements but I'm pleased one of my ideas was on the right track ("it is a global/public variable and ... it is possible that it is losing its state/value.").
btw, I think I may have found the original thread... https://www.experts-exchange.com/questions/20424197/Excel-enable-macro.html - note the original statement order may have moved further afield via Dreamboat's site (I haven't checked). I'll leave the commenting in the original thread up to you as you will probably be more eloquent than me ;-)
Rob
hi everyone,
I've just been browsing Chip Pearson's site for answers to other issues & came across the following pages. I know this question is answered but I'm posting these two links to provide alternative approaches for anyone who comes across this thread while searching:
http://www.cpearson.com/excel/EnableMacros.aspx
http://www.cpearson.com/excel/EnableMacros2.aspx
Rob
I've just been browsing Chip Pearson's site for answers to other issues & came across the following pages. I know this question is answered but I'm posting these two links to provide alternative approaches for anyone who comes across this thread while searching:
http://www.cpearson.com/excel/EnableMacros.aspx
http://www.cpearson.com/excel/EnableMacros2.aspx
Rob
Rob,
Thanks for finding pauloaguia's code in that old thread. I added some comments on the pitfalls of using a public variable and having the visibility set in the wrong order. I also included a reference to this thread.
Brad
Thanks for finding pauloaguia's code in that old thread. I added some comments on the pitfalls of using a public variable and having the visibility set in the wrong order. I also included a reference to this thread.
Brad
Good as gold :-)
Rob
Rob
so either the macro's have been removed in between opening and closing, or the spreadsheet has been digitally signed, or the macro protection settings have been lowered.
In any way this cannot be caused by the current code as displayed above.