none
Excel 2013 How to stop user's from assigning macros to shapes? RRS feed

  • Question

  • It appears that Microsoft has decided that shapes right-click context menus and controls in Excel 2013 may not be enabled/disabled, modified or replaced - period. Of course, unless there is a way around this obvious design deficiency, every worksheet that has a shapes object inserted into it allows the user to assign a macro to that shapes object unless the shapes object is totally locked down by worksheet/workbook protection (thus disabling the right-click context menu on every shape on the worksheet). 

    I have been trying to figure out a way to stop the user from assigning macros to shapes. But I have found no solution yet - outside of completely dropping the use of shapes in any Excel 2013 workbook where the shapes can't be totally locked down.

    Has anyone else had to deal with this?


    phillfri

    Update: I thank everyone for the workarounds suggested. While I opted for a different approach in this case, I did learn quite a bit from working through the suggestions.

    In the end I decided to go with simplicity and added code to the appropriate Worksheet_SelectionChange event that reassigns the correct macro to the worksheet Shape.OnAction property every time a selection is made. While not foolproof (the user could select or remain on the same activecell on exiting a shape, in which case the Worksheet_SelectionChange event does not fire), but it nevertheless assures that the appropriate macro will likely be reinstated in short order after the user exits the shape. Of course, this solution would be less practical if one had lots of shapes to deal with, but I'm only concerned with 1 protected worksheet with 1 shape textbox where the ControlFormat.LockedText property is set to false.




    • Edited by phillfri Tuesday, December 1, 2015 7:51 PM
    Monday, November 30, 2015 4:18 AM

All replies

  • Re: disable macros assigned to shapes

    Try assigning a Tooltip or a hyperlink to the shape.
    You can still assign a macro to it, but the macro won't run.
    (note: others have said both will work, but I don't believe it)

    The user could still work around your efforts but you will know it was deliberate.

    Sub StopMe()
      Dim shp As Shape
      Set shp = ActiveSheet.Shapes(1)
      ActiveSheet.Hyperlinks.Add shp, "", "", ScreenTip:="No Macros Allowed "
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, November 1, 2016 3:02 AM
    Monday, November 30, 2015 4:42 AM
  • Hello phillfri,

    This is not a recent change by Microsoft. AFAIK it has been around for quite some time (perhaps always).

    You can make it a little harder for the user to make the changes.

    Create all of your subs as Private subs and don't use Sub names like the default of "TextBox1_Click" that are easily guessed.

    Then when you right click the shape to assign the macro you will need to know the sub name because they do not appear in a list for selection. If the sub name is "Private Sub MyTextBox1()" then just edit the default name in the field and enter your sub name "MyTextBox1" in the field

    Next protect the Project in the VBA Editor as follows so the user cannot see the sub names.

    1. Right click the project name in the left column.
    2. Select VBA Project properties.
    3. Select Protection tab.
    4. Check the box against "Lock for viewing"
    5. Enter the password and click OK.
    6. The password protection is not invoked until after you save and close the workbook and then re-open.
    7. After re-opening the workbook, when you use the password to allow access to the Project, the code can be edited and remains that way until you save and close again.
    8. To remove the Project password, repeat the steps to enter a password and simply clear the passwords from the fields.

    Now because all of the subs are private subs, anywhere in your code that you call other subs, do so like the following where "AnotherTest" is a private sub like "Private Sub AnotherTest()"

    Application.Run "AnotherTest"

    Just a little extra info. If calling a private sub in a class module (like a module belonging to a worksheet) then you can use similar syntax but add the module name like the following where Sheet1 is the name of the module.

    Application.Run "Sheet1.AnotherTest"


    Regards, OssieMac

    Monday, November 30, 2015 6:41 AM
  • Thanks for the suggestion. Unfortunately it doesn't work. I've inserted a shape textbox into the worksheet . I want the user to be able to edit the shape textbox, but not assign a macro to it. If the shape textbox ControlFormat.LockedText property has been set to False (to allow editing of the text inside the textbox), the right-click context menu for the shape textbox still appears even if the shape has been assigned a tooltip and/or a hyperlink.

    Interestingly, even if the worksheet is protected, if the ControlFormat.LockedText property has been set to False Excel will still show the shape's right-click context menu on the protected worsksheet.

    As an aside, I intercept all hyperlinks in worksheet cells so I can manage how hyperlinks work myself. While the hyperlink assigned to a cell will invoke a hyperlink event when clicked, a hyperlink assigned to a shape does not invoke a hyperlink event when clicked.


    phillfri


    • Edited by phillfri Tuesday, December 1, 2015 12:05 AM
    Tuesday, December 1, 2015 12:00 AM
  • Re: Try assigning a Tooltip or a hyperlink to the shape.
          You can still assign a macro to it, but the macro won't run.

    Manually assign a hyperlink to the textbox linked to cell behind the textbox (or A1).
    Then use the right click menu to assign a macro.
    In xl2010 the macro will not run.
    '---
    Jim Cone

    Tuesday, December 1, 2015 12:36 AM
  • Sorry for my bad english (lol).

    I just want to tell one alternative how to create command (to be run on Excel) which not recognize as macro on Excel. That's why the code will not be listed as Excel Macro. And even to execute the code we don't need to create an Excel Macro Enable Workbook (so we don't need to worry if the user has Enable the macro Execution through Excel Option or not). The code will run even if the user doesn't enable the macro execution.

    The trick is to use VSTO (not available on Visual Studio Express Edition). You can even create Document Action Pane with all the control and command you need. So if you have no macro (actually you have code written in C# or VB.Net but it don't recognize as macro on Excel) how can someone assign any to a shape...?.

    Tuesday, December 1, 2015 1:05 AM
  • Hello phillfri,

    Did you try the method that I posted? If you follow my guidelines, without getting past the VBA Project password, it is virtually impossible for the user to assign a macro (other than the one initially assigned) to the shape.

    Unfortunately they can remove the currently assigned macro. The user can also re-assign the same macro to the shape (if they remember the sub name) but that cannot assign a different macro without knowing the name of an existing macro and they cannot create a new macro for it.

    I should think that for most applications, while not perfect, the method should be acceptable.


    Regards, OssieMac

    Tuesday, December 1, 2015 1:34 AM