Unable to make my custom toolbar 'float' in Excel 2007

[Steve Jacobs]

In my workbook_open event, I call a sub contained in a module that creates a
custom toolbar:

Sub CreateBar()
' This procedure creates a new temporary toolbar.
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
' Create a new floating toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("NavBar").Delete
Set ComBar = CommandBars.Add(Name:="NavBar", Position:=msoBarFloating,
Temporary:=True)
ComBar.Visible = True
' Create a button with text & icon on the bar and set some properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton,
Parameter:="GoBackBtn")
With ComBarContrl
.Caption = "Go &Back"
.Style = msoButtonIconAndCaption
.TooltipText = "Go back to the previously viewed worksheet"
'the onaction line tells the button to run a certain macro
.OnAction = "btnGoBack"
.Width = "85"
.FaceId = 41
End With
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub

I'd like the toolbar to launch 'undocked'. Yet every time I open the .xlsm,
the toolbar is not only undocked, but it is in the 'Add-Ins'
tab/ribbon/whatever, a location I dont' expect many users to find.

What does the msoBarFloating option do, if not this? And if this is what it
does, why isn't it working for me?

Thanks,
Steve

 

[Robert Flanagan]

Steve, Microsoft removed floating toolbars in Excel 2007

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Robert Flanagan]

Steve, just a thought. You can create a modeless userform with buttons on
it. It would look like a toolbar and you could drag and move it around as
needed. Plus, it would be available in all ribbon tabs. You could even set
it up so that you could shrink or expand it.

Bob

 

[Steve Jacobs]

I have no idea how to do that, but it sounds like it might be perfect. I'll
look into it.

Thanks for the tip.

-- Steve

 

[2007fanfoe]

Hi Steve,

Take a look at this tool:

http://www.toolbartoggle.com/

Regards,

Fanfoe

 

[Steve Jacobs]

Thanks Fanfoe, Robert.

Robert - I ended up commenting out my commandbar code, and using a modeless
form, just like you suggested. So far it seems to work just fine for my
purposes.

Thanks again.

 

[Joshua]

How does one do this? I have created in Excel '03 a series of toolbars used
to place images on a spreadsheet as accounting tickmarks. The toolbar button
are all tied to macros. While I can convert the macros to be usable in '07
(do to use of Select command) I am having troubles with the toolbars
themselves. The toolbars I had active in '03 came over under the Add-Ins
tab, but I cannot modify the buttons or add new ones (there are 2 sets of
buttons, 1 red and 1 blue, I have red active but cannot bring up the blue
set). Is there a better way to do this?