btadams
Well-known Member
- Joined
- Jan 6, 2003
- Messages
- 1,943
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:
and here's the macro code for the Stack_Columns macro:
Any help greatly appreciated,
Brian
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