"Run-time error '1004' Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command" When using "onLoad" in Ribbon XML

2
votes

I have a feeling I'm using the "onLoad" callback incorrectly. Essentially what I'm trying to do is run some code in VBA immediately after my custom ribbon loads (which is located on an add-in I've created).

This is the XML code:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="spRibbon_onLoad">

Here is the VBA code:

This is a global declaration:

Dim grxIRibbonUI

This is the code I'm attempting to run:

Sub spRibbon_onLoad(ribbon As IRibbonUI)
    Set grxIRibbonUI = ribbon
    Call spCode 'this is the code I'm attempting to run
End Sub

The problem is, if I try and open an existing workbook (when no other instances of excel are open) I get the following error:

Run-time error '1004': Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command.

I then have to select the "End" button twice on the error because it pops up twice.

I guess I can understand why it's throwing that error, my custom add-in hasn't loaded the customized ribbon yet but since the main ribbon has loaded it's attempting to run a macro it can't see. However, if I have a workbook open already with my custom ribbon already visible, it simply won't run the code. Any suggestions would be appreciated.

1
excelribbonxexcel-iribbonuivba
Where is the spCode procedure? In you addin? Is it declared as public? – Excel Developers
@Excel Developers - The spCode is in my Add-in. It's located in the same module as the spRibbon_onLoad procedure. As written above it's not public but I tried that too and received the same results. – Jon Rose

1 Answers

0
votes

After a little more research I found out that a simple time delay of one second accomplished this. It's not exactly what I was looking for but it works so I've decided to answer my own question.

Sub spRibbon_onLoad(ribbon As IRibbonUI)
    Application.OnTime Now + TimeValue("00:00:01"), "spCode"
End Sub