[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

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(Cancel As Boolean)
    blnClosing = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("Sheet1").Visible = True
    Worksheets("Sheet2").Visible = xlVeryHidden
    Worksheets("Sheet1").Activate
    If Not blnClosing Then
        Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), Procedure:="ThisWorkbook.ShowCorrect", Schedule:=True
    End If
End Sub

Private Sub Workbook_Open()
    blnClosing = False
    Worksheets("Sheet1").Visible = xlVeryHidden
    Worksheets("Sheet2").Visible = True
    Worksheets("Sheet2").Activate
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(Cancel As Boolean)
                                           
    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
0
AndresHernando
Asked:
AndresHernando
  • 7
  • 5
  • 4
  • +1
1 Solution
 
Arno KosterCommented:
if the excel sheet contains macro's, has not been digitally signed, and the macro protection settings remain the same, excel will ask you to enable macro's every time you open the worksheet.

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.
0
 
AndresHernandoAuthor Commented:
Which are some of the reasons why I'm so baffled with this issue.
0
 
Arno KosterCommented:
does the strange behavior also take place when you have disabled all add-ins ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
AndresHernandoAuthor Commented:
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.
0
 
Arno KosterCommented:
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
0
 
AndresHernandoAuthor Commented:
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.
0
 
broro183Commented:
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" in the Immediate pane of the VBE & press [Enter]?
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
0
 
AndresHernandoAuthor Commented:
Hi Rob,

When I type that in the Immediate pane I get "True."

0
 
broro183Commented:
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
0
 
byundtCommented:
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:
Private Sub Workbook_Open()
    blnClosing = False
    Worksheets("Sheet2").Visible = True
    Worksheets("Sheet1").Visible = xlVeryHidden
    Worksheets("Sheet2").Activate
End Sub

Open in new window


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("blnClosing").Value           'Will return either "=FALSE" or "=TRUE"
ThisWorkbook.Names("blnClosing").RefersTo = False
0
 
AndresHernandoAuthor Commented:
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.
0
 
AndresHernandoAuthor Commented:
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
0
 
byundtCommented:
I believe your problem was as I described. Here is the corrected code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Unprotect Password:="locked"
    
    Sheet1.Protect Password:="locked", _
        Contents:=True, UserInterfaceOnly:=True
    
    Sheet3.Protect Password:="locked"
    
    ThisWorkbook.Names("blnClosing").Value = True
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
                                           
    Application.ScreenUpdating = False
    ThisWorkbook.Unprotect Password:="locked"
    Sheet4.Visible = True
    Sheet3.Visible = xlVeryHidden
    Sheet1.Visible = xlVeryHidden
    Sheet4.Activate
    If [blnClosing] = False Then
        Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), Procedure:="ThisWorkbook.ShowCorrect", Schedule:=True
    End If
    ThisWorkbook.Protect Password:="locked"
End Sub


Private Sub Workbook_Open()
    Dim nm As Name
    On Error Resume Next
    Set nm = ThisWorkbook.Names("blnClosing")
    If nm Is Nothing Then
        ThisWorkbook.Names.Add "blnClosing", RefersTo:=False
    End If
        
    Application.ScreenUpdating = False
    ThisWorkbook.Unprotect Password:="locked"
      
    Sheet1.EnableAutoFilter = True

    Sheet1.Protect Password:="locked", _
    Contents:=True, UserInterfaceOnly:=True
    
    Sheet3.Protect Password:="locked"
    
    ThisWorkbook.Names("blnClosing").Value = False
    Sheet3.Visible = True
    Sheet1.Visible = True
    Sheet4.Visible = xlVeryHidden
    Sheet1.Activate
    ThisWorkbook.Protect Password:="locked"
End Sub
        
Public Sub ShowCorrect()
    Call Workbook_Open
End Sub

Open in new window

AR-Workbook---10Oct11Q27383455.xlsm
0
 
AndresHernandoAuthor Commented:
Thanks to everyone for helping me on this issue.
0
 
byundtCommented:
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
0
 
broro183Commented:
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... http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_20424197.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
0
 
broro183Commented:
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
0
 
byundtCommented:
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
0
 
broro183Commented:
Good as gold :-)

Rob
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now