Custom Ribbon Error: Macro not available

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,941
Hello Everybody!

I've created a custom ribbon with some of my favorite macros. All of the ribbon buttons but two are functioning properly and I can't figure out why. I get the "macro not available" error when I click on them. The two buttons not working are the WideToLong and Stack_Columns macros.

Here's the ribbon code:
VBA Code:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" label="HA Macros" insertAfterMso="TabView">
                <group id="customGroup1" label="HA Tools">
                    <button id="customButton1" label="Copy Table/Chart Settings" size="large" onAction="CopyChartTableSettings" imageMso="GroupChartData" />
                    <button id="customButton2" label="Boxplot" size="large" onAction="MakeBoxPlotsFromSelection" imageMso="VerticalSpacingIncrease" />
                    <button id="customButton3" label="Wide to Long" size="large" onAction="WideToLong" imageMso="FieldList" />
                    <button id="customButton4" label="Stack Columns" size="large" onAction="Stack_Columns" imageMso="ObjectsAlignTop" />
                    <button id="customButton5" label="Combine Sheets in File" size="large" onAction="CombineSheets" imageMso="Repaginate" />
                    <button id="customButton6" label="Combine All Excel Files in Folder" size="large" onAction="CombineAllExcelFiles" imageMso="DatabaseRelationships" />
                    <button id="customButton7" label="Conslidate Workbooks In Folder" size="large" onAction="ConslidateWorkbooksInFolder" imageMso="ObjectsAlignDistributeHorizontallyRemove" />
                    <button id="customButton8" label="TBD" size="large" onAction="conUnderlineSub" imageMso="Underline" />
                    <button id="customButton9" label="TBD" size="large" onAction="conUnderlineSub" imageMso="Underline" />
                </group>
                <group id="customGroup2" label="Select">
                  <menu id="MyDropdownMenu" label="My Menu" size="large" imageMso="TextAlignGallery"  >
                    <button id="customButton10" label="Caption 8"  onAction="Macro8" imageMso="TextAlignGallery" />
                    <button id="customButton11" label="Caption 9"  onAction="Macro9" imageMso="TextAlignGallery" />
                    <button id="customButton12" label="Caption 10"  onAction="Macro10" imageMso="TextAlignGallery" />
                    <button id="customButton13" label="Caption 11"  onAction="Macro11" imageMso="TextAlignGallery" />
                    <button id="customButton14" label="Caption 12"  onAction="Macro12" imageMso="TextAlignGallery" />
                  </menu>
                </group>   
            </tab>
        </tabs>
    </ribbon>
</customUI>

and here's the macro code for the Stack_Columns macro:
VBA Code:
Sub Stack_Columns(control As IRibbonControl)
Dim oneColumnHead As Range
Dim columnHeads As Range

    If MsgBox("This macro will stack all columns into a single column. Continue?", vbOKCancel, "Stack Columns") _
        = vbCancel Then Exit Sub

    Range(ActiveSheet.Cells(1, 2), ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft)).Select
    
    With ActiveSheet
        Set columnHeads = Range(.Cells(1, 2), .Cells(1, .Columns.Count).End(xlToLeft))
    End With
    
    For Each oneColumnHead In columnHeads
        With oneColumnHead.EntireColumn
            With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
                .Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.Rows.Count, 1).Value = .Value
            End With
        End With
    Next oneColumnHead
End Sub

Any help greatly appreciated,

Brian
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,941
Well, I found one solution: I renamed those two macros in the VBA editor and then updated them in the xml code and everybody is happy
 

Watch MrExcel Video

Forum statistics

Threads
1,127,758
Messages
5,626,691
Members
416,200
Latest member
Pulsar3000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top