Can I Make Macros Available on A Floating Toolbar

[cheman]

I have three macros to sort data on a very large worksheet that I hav
made launchable by pushing a button on the worksheet. Because th
worksheet is so large I am consatntly moving the buttons around just t
keep them in the relevant field of view. Is there a way to put th
launch buttons onto a floating toolbar add it to one of the existin
toolbars

 

[JEyon]

You can make a brand new toolbar by (1) selecting
Tools/Customize, then (2) selecting the Toolbar tab, then
(3) pressing the "New" button. Give it a name and let it
float or park it anywhere.

It's a little tricky to associate that toolbar with the
particular spreadsheet. I recommend keeping all the time,
but hiding it when it's not in use.

Putting the macro buttons on the new toolbar isn't the
most intuitive thing Microsoft came up with. (a) In the
same dialog box (Tools/Customize) select the "Commands"
tab and in the left list, select "Macros". (b) See the
Smiley face? Drag that to the new toolbar and release it.
(c) Then right click on that new smiley button. At the
bottom of the menu is "Assign Macro...". You should be
able to do that yourself and figure out how to give it a
name.

 

[Bob Phillips]

Here is some sample code to create a toolbar button on the Formatting as
suggested. This code would go in the ThisWorkbok code module of the addin.

I would also add my usual corollary that to see what FaceIds are available,
visit John Walkenbach's site at http://j-walk.com/ss/excel/tips/tip67.htm

Question for you. How can it be a floating toolbar added onto another
existing toolbar.

Option Explicit

Dim sMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

sMenu = "myButton"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Dim newMenu As Object 'CommandBarControl
Dim ctrlButton As Object 'CommandBarControl

sMenu = "Margin Calculator"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Formatting")
Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)

With oCtl
.BeginGroup = True
.Caption = sMenu
.FaceId = 197
.Style = msoButtonIconAndCaption
.OnAction = "myMacro"
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)