Can't run Form-Control-with-argument macro for no discernable reason

Copper Contributor

Update.  I'm 99% sure the issue was somehow caused by me attempting to digitally sign my macro enabled document.  I had done so repeatedly since I kept getting invalid signature errors.  Clearing the signatures didn't fix the issues.  In the end, I gave up and simply remade the document from scratch.

 

Let's say I have a function that takes a string argument.

If I want to run this function using a form control button, I can normally input 'TestFunc "abc"' in the assign macro window.  However I have a particular document where this results in in error when clicking the button.  It says "Cannot run the macro.....  The macro may not be available in this workbook or all macros may be disabled."

 

If I run the document on a different computer, it runs like expected.  If I remove the string input "abc", it works on this computer.  If I create a new document on this computer and run the macro with an argument, it works like expected. 

 

Help figuring out the cause of this would be appreciated.

 

I'm also not running any other Excel docs.  So it's not some weird conflict issue.  Also note that I'm running Office LTSC Professional Plus 2021.

8 Replies

@Marc_G2

Is TestFunc also the name of a module in that specific workbook?

Or could there be another name conflict?

 

As a workaround you could create a macro

Sub MyMacro()
    Call TestFunc("abc")
End Sub

and assign this macro to the command button.

The name of the module is something else. I've also tried moving the function to a different module which has no effect. And yes, I could use that as a workaround.

I'm thinking there might be a weird config problem or something might be corrupted. If go back to a much older version of the document, the problem isn't there.
I created a copy of document and deleted everything except for the test function. I've also removed all library references. The issue persists...

@Marc_G2 

Could you a copy of the workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Here's a link to the example. It's literally nothing but a button. It will mostly likely work fine on your system.
https://docs.google.com/spreadsheets/d/1gmWqB-GkCIXvjpyPaYACsK0k93NZv_KZ/edit?usp=drive_link
My IT support reinstalled Office to see if my install might be broken. It still didn't fix the issue. So now I'm now thinking it might be a config related bug.

@Marc_G2 

Thanks. The button works fine on my PC.

HansVogelaar_0-1697470478416.png

Could macros have been blocked on the PC where it didn't work?

It's definitely not the case of them being blocked. Buttons work just fine as long as I'm not also trying to pass an argument.
I tried running a repair on the document and Excel said it fixed some errors. It didn't fix the problem though.

@Marc_G2 

I doubt we'll ever know what causes the problem...