Assign Custom Toolbar Button to XLA Sub?

[YellowLabPro]

I have a button on the menubar under Macros, this might be called the commandbar, I want to assign this to a Add-in Sub.
Upon opening the menus to assign, I do not see a path to assign it though.
http://www.mrexcel.com/board2/viewtopic.php?t=287292
(no responses as of yet)



thanks,

Doug

[Ken Puls]

Hi Doug,

What is the name of the macro?
What is the name of the xla file?
Was the button created by the xla or outside of it?

[daniel_d_n_r]

if it is a private sub it will not appear

[YellowLabPro]

Hello Ken,
The name of the Module is RevertOpen, the project is GenFunc.xla, the Sub is Revert.
The button, I created this in Excel through the right-click toolbar method and is in Macro Toolbars.

[Bob Phillips]

Add the button within the workbook open event of the addin and destroy it in berforeclose



Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarButton

On Error Resume Next
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("Macro").Controls("Revert").Delete
On Error GoTo 0

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls("Tools").Controls("Macro").Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtl.Caption = "Revert"
oCtl.Style = msoButtonCaption
oCtl.OnAction = "Revert"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("Macro").Controls("Revert").Delete
End Sub

[YellowLabPro]

Where does this go, as a module in the .xla?

[Bob Phillips]

In ThisWorkbook of the xla.

[YellowLabPro]

Bob,
I have added the code to the vbaProject GenFunc.xla, in "ThisWorkbook".
I have saved it, closed and reopened it. The code is in "ThisWorkbook", but I don't see a button for it. I was expecting to see a button "Revert" in the Macro command bar. Should I look somewhere else, or need to perform another step to activate this?

[Bob Phillips]

Doug,

I guess we are talking about a different Macro commandbar, I did think it was odd where I put it. Which commandbar are you talking about exactly?

[YellowLabPro]

I wrote this little bit:

Option Explicit
Sub Revert()
Dim AWb As Workbook
Set ActiveWorkbook = AWb
AWb.Close SaveChanges:=False
Application.RecentFiles(2).Open

End Sub

I want to place a button in the macro menu bar so that it is accessible while the add-in GenFunc is loaded.
RevertOpen is in GenFunc.xla.

[Bob Phillips]

Can't connect that answer to my question?

[YellowLabPro]

Sorry- hmmm- Hope my terminology is correct, what I refer to as the Toolbar; Go to View, Toolbars, Macros

edited: removed "menu" between Toolbars, Macro.

[Bob Phillips]

I don't have such a commandbar on my system, but assuming it is just a toolbar, the code would be



Private Sub Workbook_Open()
Dim oCb As CommandBar

On Error Resume Next
Application.CommandBars("Macro").Controls("Revert").Delete
On Error Goto 0

Set oCb = Application.CommandBars("Macros")
With oCb.Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "Revert"
.Style = msoButtonCaption
.OnAction = "Revert"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Macro").Controls("Revert").Delete
End Sub


But toolbar items look a lot better if you have a faceid not a caption. Do you have a faceid?

[mdmackillop]

Hi Doug,
Bob's code is adding Revert as Tools/Macro/Revert. Are you looking to add it as a button on a Toolbar?

[YellowLabPro]

Yes,
I would like to place a button on the toolbar under the Toolbars menu Macros. Then I would assign a icon to the button.

[mdmackillop]

If it's a toolbar button, you can assign the macro by typing GenFunc.xla!Revert in the Assign Macro dialog.