+ Reply to Thread
Results 1 to 4 of 4

VBA Security and WorkSheet Copy Puzzle

  1. #1
    Registered User
    Join Date
    04-21-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    8

    VBA Security and WorkSheet Copy Puzzle

    An Excel VBA Addin is running the following code in a regular module, modifying another workbook which contains a sheet called "Master List".

    Whether the code makes it to MsgBox "1" or not depends whether macros are enabled in the workbook (not the Addin (!)) If Macros are not enabled, it prints a "0" and then stops (with no error conditions) I know it's made a copy because I see "Master List (2)" as a sheet. But it does not print "1" (!) If Macros are enabled (either with a user permission, or perhaps the workbook is in a trusted folder) the code proceeds through MsgBox "1" and beyond. The sheet Master List does contain events code, if that helps. On the other hand I've turned events off. I've double checked the right workbook is referenced via Application.WorkBooks(1) (so that it's not referring to the AddIn, but rather the "ActiveWorkBook") What's going on???

    Please Login or Register  to view this content.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,110

    Re: VBA Security and WorkSheet Copy Puzzle

    Whether the code makes it to MsgBox "1" or not depends whether macros are enabled in the workbook (not the Addin (!))

    If Macros are enabled (either with a user permission, or perhaps the workbook is in a trusted folder)
    Macros are enabled in the program (ecxcel), not in a workbook, or by using any specific folders. If macros have not been enabled - and depending on the setting - all you will get is an error message telling you macro's have been disabled
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-21-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    8

    Re: VBA Security and WorkSheet Copy Puzzle

    Thanks for the reply. Let me expand a bit on situation:

    (1) The Addin scans workbooks loaded into Excel (via a App_WorkbookOpen(ByVal Wb As Workbook) event in the Addin's ThisWorkBook object). If it finds that a certain sheets exists, it will then proceed to modify that workbook. Such a workbook contains vba code itself.
    (2) It may be that the loaded workbook is being loaded for the first time, and Excel's default is to turn macros off and notify the user.
    (3) So in that event, macros run in the Addin, but Excel notifies me that "Macros have been disabled" which I take to refer to the loaded workbook, since my macros in the Addin continue to run, through a bunch more Addin code (many lines) however:
    (4) If I don't give permission to enable macros (by responding to the "Macros have been disabled" notifiation bar) the code hangs at the indicated place (just stops). It stops specifically just after the worksheet.copy code.

    So it seems that vba code is running in the Addin code, although (so Excel says) disabled in the loaded workbook. Does this make any sense?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,110

    Re: VBA Security and WorkSheet Copy Puzzle

    My understanding is that, unless macro's have been spefically enabled (through the "More Commands/Trust Center/Trust Center Settings/Macro Settings", macros will not run at all, doesnt matter if they are "home made" or from an add-in

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy Range puzzle
    By jinxtt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-15-2013, 05:45 PM
  2. Worksheet Security
    By Wanabe in forum Excel General
    Replies: 3
    Last Post: 08-30-2010, 06:53 PM
  3. Worksheet Change Puzzle
    By Dan_Dollar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-03-2007, 11:32 AM
  4. Worksheet Security
    By adey in forum Excel General
    Replies: 0
    Last Post: 11-23-2005, 01:01 PM
  5. worksheet security
    By Nancine! in forum Excel General
    Replies: 1
    Last Post: 08-18-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1