Simple Floating Toolbar

[sswcharlie]

Hi

On this page:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=921

there is a great floating toolbar. however I would like the buttons to be vertically placed (I will have quite a few). One above th other.

I suspect it is the line:

Set ComBar = .....

Maybe, maybe not. Anyway how do I get button to go verticle ? I can get it to go verticle by dragging the border down, and returns to original next atime wb opened.. But I want it to be always the verticle way.

Thanks

Charlie

[GTO]

Greetings Charlie,

I think this should work. Just keep track of the widest control as they are created and set the commandbar's width to this.

Steve's codes w/slight additions noted:

'.......................................................................... ..........................
'Add all of the following code To a standard module:
Option Explicit
Sub AddNewToolBar()
Dim dblControlWidth
' 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("My Toolbar").Delete

Set ComBar = CommandBars.Add(Name:="My Toolbar", _
Position:=msoBarFloating, _
Temporary:=True)
ComBar.Visible = True
' Create a button with text on the bar and set some properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
.Caption = "Macro1"
.Style = msoButtonCaption
.TooltipText = "Run Macro1"
'the onaction line tells the button to run a certain macro
.OnAction = "Macro1"
'//******//
dblControlWidth = Application.Max(.Width, dblControlWidth)
End With
' Create a button with an image on the bar and set some
' properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
'the facId line will let you choose an icon
' If you choose to use the faceId then the caption is not displayed
.FaceId = 1000
.Caption = "Icon Button"
.TooltipText = "Run Macro2"
'the onaction line tells the button to run a certain macro
.OnAction = "Macro2"
'//*******//
dblControlWidth = Application.Max(.Width, dblControlWidth)
End With
'//*******//
ComBar.Width = dblControlWidth
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub

For what it is worth, I tend to use the workbook's Activate and Deactivate events for adding/deleting a custom commandbar. This way the user doesn't oopsie by clicking something he shouldn't when another wb has the focus.

Mark

[sswcharlie]

Hi Mark

Thanks for above. Really great. Will test it shortly.

Regards from downunder

Charlie

[sswcharlie]

Hi
Thanks for your quick response.
Not working for me at this stage. I have attached my WB.
Have I got the vba in the correct place.

Thanks

Charlie

[GTO]

Hi Charlie,

I am afraid not.

While in VBIDE (the code window), look around and you will note that there are actually (or should be) three windows.

Two smaller windows:

One is called "Project - " (with the name of the VBA Project after the hyphen, normally "VBAProject" by default
This window shows all the existing modules. See where it shows (with everything expanded) Sheet1, Sheet2, Sheet3, and ThisWorkbook? This is because each sheet has its own module, which is where you can select from application supplied events (events like selection_change or activate(ing) the sheet and so on). You stuck the code in Sheet1's module. You want to create a Standard Module and plunk it in there.
The other small window is called the properties window, and this will show some of the properties of whatever module is was double-clicked on in the project window, and who's code is now showing in the main code window.If either of the little windows is not showing, go to View, and click on each one to make sure they are displayed.

Now, look back up at the menubar and from Insert, choose Module (not Class Module). Paste Steve's code (as altered in my post) after making sure you are in the newly created Standard Module. (The properties window will show the name Module1)

Now, go back to the project window, and double-click (or right-click and select View Code) on ThisWorkbook.

Then tack in:

Option Explicit

Private Sub Workbook_Activate()
AddNewToolBar
End Sub

Private Sub Workbook_Deactivate()
DeleteToolbar
End Sub

Before we leave VBIDE, lets save some later headaches. Go to the menubar, selecting Tools | Options. Select the Editor tab, and tick the checkbox that says 'Require Variable Declaration'. From now on, when you create a module, the Option Explicit will automatically be inserted at the top. This will save you major pains later, tracking down some silly error from any type of typo for a variable's name.

Okay, save and close the workbook.

Next time you open it, the test commandbar will be created and displayed ea time the wb is activated, and deleted ea time another wb is activated, or you close the file.

Hope that helps,

Mark

[sswcharlie]

Hi Mark

Thanks for all the info, looks great and I am learning heaps. Will get to it later today. Playing house doctor at the moment with child sick.

Thanks and regards
Charlie

[sswcharlie]

Hi Mark

Almost there. Everthing is working ok, except when I close the workbook the toolbar remains. That is not deleted off.
On closing of workbook comes up with
Compile Error:
Sub or Function not defined

If I go OK and close vbe window I get:

This command will stop the debugger

I close this and all is ok until next time to close workbook..

Your comments would be appreciated.
Thanks

Charlie

[GTO]

Hi Charlie,

Where did you put this?

'Code to delete the toolbar called by workbook close:
Sub DeleteToolbar()
On Error Resume Next
CommandBars("My Toolbar").Delete
End Sub

I may have been confusing, as I did not include it at post #2. It should be in a Standard Module. I would just stick it in the same Standard Module that you created for AddNewToolbar().

If you step through the code (put break points at the top of ea procedure after opening the wb), you can see what is happening each time the wb is activated or deactivated. The deactivate event is also raised when the wb closes.