Excel Disabling Macros in Secondary Workbook Only from UNC

Question

We have an xlsm with an Auto_Open that opens an xlsx that has buttons pointing back to the macros in the xlsm. Those buttons / macros are used to populate the sheet, then they are deleted and the sheet is sent to the client.

Double clicking the xlsm in a Windows Explorer window browsing a mapped drive everything works perfectly.

When navigating to that same folder using the UNC and double clicking the xlsm, the Auto_Open macro in the xlsm runs because the xlsx is opened. HOWEVER, The buttons that run macros from the xlsm file are DISABLED and can't be run.

Why would the macros in the xlsm be enabled but not a the buttons in the xlsx that was OPENED by the xlsm and is there any way around this??

Thank you,
Ken

Answer 1

The macro security settings in Excel may be preventing the buttons from running. To check this, go to File > Options > Trust Center > Macro Settings. Make sure that the "Trust all installed macros" checkbox is selected. The buttons may be disabled by default. To check this, right-click on a button and select "Edit." In the "Button Properties" dialog box, make sure that the "Enabled" checkbox is selected. The buttons may be disabled by code in the xlsm. To check this, open the xlsm in a text editor and search for the following code: Sub DisableButtons()

'Disable all buttons on the active sheet

For Each button In ActiveSheet.Buttons

button.Enabled = False

Next

End Sub

If this code is present, delete it. Make sure that the xlsx file is saved in the same folder as the xlsm file. and restart.

Answer 2

I'm not seeing a checkbox with that exact wording, but in the Trust Center "Enable all macros..." is already checked, which I should have noted, sorry.

And per the original post, everything works fine when the file is opened from a mapped drive. The buttons in the 2nd workbook are only disabled when the 1st workbook is opened from a UNC. And that's universal whether I browse with Windows Explorer and double click or use the File > Open right in Excel.

I've also since noticed that closing and re-opening the xlsx file STILL doesn't work, so the problem isn't that the xlsm file opened the xlsx file. AND, from the "Macro" menu (Alt+F8) I can run the macros from the xlsm.

Answer 3

Hi Ken,

As per everything that I read in this post (including the comments and answers), it seems to be a specific problem related to the buttons in the xlsx file.

Here are a few things you can try to troubleshoot the issue:

1. Check the button properties: Ensure that the buttons in the xlsx file have the correct macro assigned to them. Right-click on a button, select "Assign Macro," and verify that the macro name associated with the button is correct and matches the macros in the xlsm file.
2. Check the button settings: Right-click on a button, select "Edit Text" or "Edit Button," and ensure that the button is not set to "Disabled" or "Locked" in the button properties. Make sure the buttons are set to be interactive and enabled.
3. Verify macro accessibility: Check if the macros in the xlsm file are declared as "Public" and not "Private." Private macros can only be accessed within the same module, while Public macros can be accessed from other files.
4. Try recreating the buttons: Delete the existing buttons in the xlsx file and recreate them using the "Insert" menu and selecting the appropriate button control. Assign the macros to the new buttons and test if they work when the xlsm file is opened from a UNC path.

Also, maybe try opening the xlsm and xlsx files from a UNC path on a different machine to see if the issue is specific to the machine you are currently experiencing the problem on.

Best Regards.

Answer 4

Hi,

I suggest you read this is article and check if this problem is related to the security in Office:

https://learn.microsoft.com/en-us/deployoffice/security/internet-macros-blocked

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

OK, so it turns out that Excel isn't blocking things, it's trying to open the xlsm "again" but can't because it's already opened from the "other" path (UNC or mapped). I knew you couldn't open 2 Excel files of the same name, what I didn't realize is in the next paragraph.

When assigning a button to a macro on another sheet that is opened in Excel, the path of that file is not shown in the dialog but it is internally stored.

The macro only works if the xlsm file is opened from the SAME PATH (UNC or mapped) as it was when the macro was assigned. So:

1. Open xlsm from the UNC.
2. Assign xlsx button to one of it's macros.
3. Save and close everything.
4. Reopen the xlsm, from the UNC, which then opens the xlsx, buttons works fine.

BUT, opening the xlsm from the mapped drive, the button in the xlsx says it can't find the macro or it's blocked, which I think is a bit of a misleading message. I think what is actually happening is that Excel already HAS the xlsm file open, so it can't open it "again" from the other location.

I thought I needed the xlsm open for the buttons in the xlsx to work, but (as more knowledgeable Excel people likely already know) turns out Excel will just open it for you. [face-palm]

So, instead of opening the xlsm and having it open the xlsx, we just need to open the xlsx and Excel will open the xlsm automatically when the buttons are pushed. Oy-vey. [2nd face-palm]

Thank you, everyone, for chiming in!! Hopefully my long-winded explanation will help someone, sometime.

All Best,
Ken