XLAM for global macros cannot be run

[F W Green]

Excel 2007 on Vista Business

I have created an XLSM file with a set of macros that I wish to deploy to
all computers in our firm. The XLSM file has the macros and a new Ribbon Tab
that I built with the Custom UI Editor. All the buttons on the Tab are
linked to the proper macros.

I saved the XLSM file as an XLAM and activated the XLAM as an addin. When I
open Excel, the new tab is visible on the Ribbon but when I click any of the
buttons, I get the error message:
"Cannot run the macro 'X'. The macro may not be available in this workbook
or all macros may be disabled.

I have pasted a sample of 1 of the macros from the VBA code at the bottom.

I have Macro Security set as:
- enable all Macros
- Trust access to the VBA project object model

If my process is incorrect or I have missed anything, your comments are most
appreciated.

Sample Macro:
'Callback for customButton1 onAction
Sub Preparer_signoff(control As IRibbonControl)
ActiveSheet.Range("B1:B3").Font.Size = 8
ActiveSheet.Range("B1").Activate
ActiveCell.Value = "PREPARER"
ActiveCell.Interior.ColorIndex = 45
ActiveSheet.Range("B2").Activate
ActiveCell.Font.Bold = True
'ActiveCell.Font.Color = RGB(0, 0, 200)
'1 black, 2 white, 3 red, 4 green, 5 blue, 6 yellow, 7 pink
'black
ActiveCell.Font.ColorIndex = 1
ActiveCell.Value = Application.UserName
ActiveSheet.Range("B3").Activate
ActiveCell.Font.Bold = True
'ActiveCell.Font.Color = RGB(0, 0, 0)
ActiveCell.Font.ColorIndex = 1
ActiveCell.NumberFormat = "yyyy-mm-dd;@"
ActiveCell.HorizontalAlignment = xlLeft
ActiveCell.Value = Date
End Sub

Thank you in advance.

Florian

 

[Jim Rech]

The problem I'd guess is not in the macro but in the RibbonX. Show us the
line of XML for the button that calls this code. You can send me your
add-in if you want. I've done a lot of ribbons.
--
Jim
| Excel 2007 on Vista Business
|
| I have created an XLSM file with a set of macros that I wish to deploy to
| all computers in our firm. The XLSM file has the macros and a new Ribbon
Tab
| that I built with the Custom UI Editor. All the buttons on the Tab are
| linked to the proper macros.
|
| I saved the XLSM file as an XLAM and activated the XLAM as an addin. When
I
| open Excel, the new tab is visible on the Ribbon but when I click any of
the
| buttons, I get the error message:
| "Cannot run the macro 'X'. The macro may not be available in this
workbook
| or all macros may be disabled.
|
| I have pasted a sample of 1 of the macros from the VBA code at the bottom.
|
| I have Macro Security set as:
| - enable all Macros
| - Trust access to the VBA project object model
|
| If my process is incorrect or I have missed anything, your comments are
most
| appreciated.
|
| Sample Macro:
| 'Callback for customButton1 onAction
| Sub Preparer_signoff(control As IRibbonControl)
| ActiveSheet.Range("B1:B3").Font.Size = 8
| ActiveSheet.Range("B1").Activate
| ActiveCell.Value = "PREPARER"
| ActiveCell.Interior.ColorIndex = 45
| ActiveSheet.Range("B2").Activate
| ActiveCell.Font.Bold = True
| 'ActiveCell.Font.Color = RGB(0, 0, 200)
| '1 black, 2 white, 3 red, 4 green, 5 blue, 6 yellow, 7 pink
| 'black
| ActiveCell.Font.ColorIndex = 1
| ActiveCell.Value = Application.UserName
| ActiveSheet.Range("B3").Activate
| ActiveCell.Font.Bold = True
| 'ActiveCell.Font.Color = RGB(0, 0, 0)
| ActiveCell.Font.ColorIndex = 1
| ActiveCell.NumberFormat = "yyyy-mm-dd;@"
| ActiveCell.HorizontalAlignment = xlLeft
| ActiveCell.Value = Date
| End Sub
|
| Thank you in advance.
|
| Florian

 

[F W Green]

I have posted the first 2 XML button actions and the last 2. The last 2 are
posted because the Generate Callbacks did not 'generate' for the last
action-customButton16.

First 2:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="MyCustomTab" label="Paperless" insertAfterMso="TabDeveloper">

<group id="customGroup1" label="Preparer">
<button id="customButton1" label="Preparer" size="normal"
onAction="Preparer_signoff" />
<button id="customButton2" label="Highlight" size="normal"
onAction="Highlight_Preparer" imageMso="AppointmentColor10" />
</group>



Last 2:
<group id="customGroup7" label="Merge Cells">
<menu id="MyDropdownMenu2" label="Merge" size="normal" >
<button id="customButton15" label="Merge and Wrap"
onAction="MergeFormat" />
<button id="customButton16" label="UnMerge" onAction="UnMerge"
/>
</menu>
</group>

</tab>
</tabs>
</ribbon>
</customUI>


Acknowledge: the xml code was downloaded from Rod deBruin samples.

Florian

 

[Jim Rech]

I don't see anything wrong with your XML. Re not generating callbacks it
seems that the Custom UI Editor doesn't like labels and onactions being the
same value. Try making them different like changing to label="Un Merge" for
example.

--
Jim
|I have posted the first 2 XML button actions and the last 2. The last 2
are
| posted because the Generate Callbacks did not 'generate' for the last
| action-customButton16.
|
| First 2:
| <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
| <ribbon>
| <tabs>
| <tab id="MyCustomTab" label="Paperless"
insertAfterMso="TabDeveloper">
|
| <group id="customGroup1" label="Preparer">
| <button id="customButton1" label="Preparer" size="normal"
| onAction="Preparer_signoff" />
| <button id="customButton2" label="Highlight" size="normal"
| onAction="Highlight_Preparer" imageMso="AppointmentColor10" />
| </group>
|
|
|
| Last 2:
| <group id="customGroup7" label="Merge Cells">
| <menu id="MyDropdownMenu2" label="Merge" size="normal" >
| <button id="customButton15" label="Merge and Wrap"
| onAction="MergeFormat" />
| <button id="customButton16" label="UnMerge" onAction="UnMerge"
| />
| </menu>
| </group>
|
| </tab>
| </tabs>
| </ribbon>
| </customUI>
|
|
| Acknowledge: the xml code was downloaded from Rod deBruin samples.
|
| Florian
| "Jim Rech" wrote:
|
| > The problem I'd guess is not in the macro but in the RibbonX. Show us
the
| > line of XML for the button that calls this code. You can send me your
| > add-in if you want. I've done a lot of ribbons.
| > --
| > Jim
| > | > | Excel 2007 on Vista Business
| > |
| > | I have created an XLSM file with a set of macros that I wish to deploy
to
| > | all computers in our firm. The XLSM file has the macros and a new
Ribbon
| > Tab
| > | that I built with the Custom UI Editor. All the buttons on the Tab
are
| > | linked to the proper macros.
| > |
| > | I saved the XLSM file as an XLAM and activated the XLAM as an addin.
When
| > I
| > | open Excel, the new tab is visible on the Ribbon but when I click any
of
| > the
| > | buttons, I get the error message:
| > | "Cannot run the macro 'X'. The macro may not be available in this
| > workbook
| > | or all macros may be disabled.
| > |
| > | I have pasted a sample of 1 of the macros from the VBA code at the
bottom.
| > |
| > | I have Macro Security set as:
| > | - enable all Macros
| > | - Trust access to the VBA project object model
| > |
| > | If my process is incorrect or I have missed anything, your comments
are
| > most
| > | appreciated.
| > |
| > | Sample Macro:
| > | 'Callback for customButton1 onAction
| > | Sub Preparer_signoff(control As IRibbonControl)
| > | ActiveSheet.Range("B1:B3").Font.Size = 8
| > | ActiveSheet.Range("B1").Activate
| > | ActiveCell.Value = "PREPARER"
| > | ActiveCell.Interior.ColorIndex = 45
| > | ActiveSheet.Range("B2").Activate
| > | ActiveCell.Font.Bold = True
| > | 'ActiveCell.Font.Color = RGB(0, 0, 200)
| > | '1 black, 2 white, 3 red, 4 green, 5 blue, 6 yellow, 7 pink
| > | 'black
| > | ActiveCell.Font.ColorIndex = 1
| > | ActiveCell.Value = Application.UserName
| > | ActiveSheet.Range("B3").Activate
| > | ActiveCell.Font.Bold = True
| > | 'ActiveCell.Font.Color = RGB(0, 0, 0)
| > | ActiveCell.Font.ColorIndex = 1
| > | ActiveCell.NumberFormat = "yyyy-mm-dd;@"
| > | ActiveCell.HorizontalAlignment = xlLeft
| > | ActiveCell.Value = Date
| > | End Sub
| > |
| > | Thank you in advance.
| > |
| > | Florian
| >
| >
| >

 

[F W Green]

Thanks, Jim.
Changing the label of the last action did allow the Callback Generate to work.

As for the macro security issue, I will keep reading my VBA/Excel
Programming book and hoping that someone else has some suggestions.

Florian

 

[F W Green]

Continuing to try things:
I recreated the macro steps for the first action button as a macro within a
sheet. I was brave enough to give it the same name as the macro in the VBA.
This is the macro to match the first "customButton1" below.

The 2 versions of the macro are not 100% equal in that I did not reproduce
the steps exactly. However, executing the macro from MACROS on the Developer
portion of the ribbon works but from my portion of the ribbon gives the "not
found" error.

The macro in the VBA that gives the error of not found is:
'Callback for customButton1 onAction
Sub Preparer_signoff(control As IRibbonControl)
ActiveSheet.Range("B1:B3").Font.Size = 8
ActiveSheet.Range("B1").Activate
ActiveCell.Value = "PREPARER"
ActiveCell.Interior.ColorIndex = 45
ActiveSheet.Range("B2").Activate
ActiveCell.Font.Bold = True
'ActiveCell.Font.Color = RGB(0, 0, 200)
'1 black, 2 white, 3 red, 4 green, 5 blue, 6 yellow, 7 pink
'black
ActiveCell.Font.ColorIndex = 1
ActiveCell.Value = Application.UserName
ActiveSheet.Range("B3").Activate
ActiveCell.Font.Bold = True
'ActiveCell.Font.Color = RGB(0, 0, 0)
ActiveCell.Font.ColorIndex = 1
ActiveCell.NumberFormat = "yyyy-mm-dd;@"
ActiveCell.HorizontalAlignment = xlLeft
ActiveCell.Value = Date
End Sub

The macro in the worksheet macros that does execute properly is:
Sub Preparer_signoff()
'
' Preparer_signoff Macro

Range("B1:B3").Select
With Selection.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("B1").Select
ActiveCell.FormulaR1C1 = "PREPARER"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Florian"
Range("B3").Select
Selection.NumberFormat = "yyyy-mm-dd;@"
ActiveCell.FormulaR1C1 = Date
End Sub


I have even pasted a macro from the VBA into a new macro in a sheet but I
get the error "wrong number of arguments or invalid property assignment".

Any suggestions will be graciously accepted.

Florian

 

[F W Green]

I have pasted the new macro code into the VBA and still get the not found
error.

I pasted the VBA code back into a new macro and it runs.

Therefore, my issue would seem to be that the linkage between the Ribbon (my
CustomUI) and the macros inside the VBA that is failing.

I am not sure if that makes my search for a solution easier or more
perplexcing.

Florian

 

[Jim Rech]

As I said yesterday, if you send me you're XLAM I'll try to figure it out.
You could even if you want make a slimmed down version that exhibits the
problem. jrrechAThotmailDOTcom

--
Jim
|I have pasted the new macro code into the VBA and still get the not found
| error.
|
| I pasted the VBA code back into a new macro and it runs.
|
| Therefore, my issue would seem to be that the linkage between the Ribbon
(my
| CustomUI) and the macros inside the VBA that is failing.
|
| I am not sure if that makes my search for a solution easier or more
| perplexcing.
|
| Florian
|
|
|
| "Jim Rech" wrote:
|
| > I don't see anything wrong with your XML. Re not generating callbacks
it
| > seems that the Custom UI Editor doesn't like labels and onactions being
the
| > same value. Try making them different like changing to label="Un Merge"
for
| > example.
| >
| > --
| > Jim
|

 

[F W Green]

Based on this comment from Jim Rech:
"I meant to ask whether you mistakenly put the code in the workbook or
worksheet module." , we have put my VBA code into a Module in the XLSM
instead of in the workbook.

I am most grateful for the assistance as things are now working.

Florian