Example file to dl for floating toolbar?

[StargateFan]

Can anyone point me to an example file that actually creates a
floating toolbar with, hopefully, at least 2 working buttons on it?
I've googled the archives of this ng and all the examples I've tried
bring up an extra box along the top menu, mimicking the upper
left-hand corner of any sheet along the top (the one that just
restores, minimizes and closes). Where I first saw this floating
toolbar concept is in an invoice sheet that is completely locked down
so I can't see the working code and how it does what it does.

Pls, any help would be appreciated. I googled for this and only one
example came up (in German <g>), which I dl scanned for viruses and
tried out but it wasn't the right thing, either.

Thanks so much! D

 

[Bernie Deitrick]

SF,

Try the code below. Copy it, paste it into a codemodule, and run the macro:

AddFloatingCommandbarWith3Buttons

HTH,
Bernie
MS Excel MVP


Option Explicit

Dim myBar As CommandBar
Dim myButton As CommandBarButton
Const myName As String = "MyFloatingCBar"

Sub AddFloatingCommandbarWith3Buttons()

On Error Resume Next
Application.CommandBars(myName).Delete

Set myBar = Application.CommandBars.Add(myName)
With myBar
.Position = msoBarFloating
.Left = 100
.Top = 200
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Goodbye"
.Style = msoButtonIcon
.FaceId = 138
.Enabled = True
.OnAction = "SayGoodbye"
End With
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Kill Me"
.Style = msoButtonIcon
.FaceId = 132
.Enabled = True
.OnAction = "KillMe"
End With
End With

End Sub

Sub SayHello()
MsgBox "Hello"
End Sub

Sub SayGoodbye()
MsgBox "Goodbye"
End Sub

Sub KillMe()
On Error Resume Next
Application.CommandBars(myName).Delete
End Sub

 

[StargateFanFromWork]

Oh, thank you!! That's great. Now I can see what is going on. I also had
had two questions which seeing the code answered, one concerned the buttons.
I see that one can change the button image very easily and it's with the
good old FaceID references (for which I have a nifty template that lists
them all along with the pix!). Also, a second concern was the position of
the toolbar. Very easy to do as I can see from this code. My first view of
a floating toolbar was one I used last week. It's the one with the locked
code so I couldn't go in to see if the position can be changed. Good to
know it's easy as in this particular case, an annoyance is the toolbar sits
by default on top of one of the fields to edit so we have to keep dragging
it out of the way. Yet very easy to set this via the "left" and "top"
numbers for the position in the code below.

I now have something to work with. Will go back and google the archives of
this ng to try different things out.

Thanks a million! D

 

[StargateFan]

Can anyone point me to an example file that actually creates a
floating toolbar with, hopefully, at least 2 working buttons on it?
I've googled the archives of this ng and all the examples I've tried
bring up an extra box along the top menu, mimicking the upper
left-hand corner of any sheet along the top (the one that just
restores, minimizes and closes). Where I first saw this floating
toolbar concept is in an invoice sheet that is completely locked down
so I can't see the working code and how it does what it does.

Pls, any help would be appreciated. I googled for this and only one
example came up (in German <g>), which I dl scanned for viruses and
tried out but it wasn't the right thing, either.

Thanks so much! D

Isn't this always the way - when I was looking for examples of
commandbar, nothing came up. Tonight I was googling for instructions
on using custom icons with a commandbar and this page came up with
quite a few examples. Though not all commandbar ones, there was
closely related ones, too.
http://www.erlandsendata.no/english/index.php?d=endownloadcommandbars

Cheers! D

p.s., opted not to go with a custom icon for one of the buttons as
it's beyond me right now and too much work right at this time. I have
now added a floating toolbar to an older time sheet file which I'll
take to work tomorrow. Using it as an example I'll be able to modify
the newer version of this time sheet. It should work a treat. One of
the buttons on the new floating toolbar will create a copy of a hidden
"template" sheet which was on a button found on this older file
initially and which would necessarily get replicated with each new
sheet copy. I was concerned that over time, this would add a lot of
unncessary extra bloat to the file size. With the floating toolbar,
this problem has been eliminated completely. Tx.

 

[StargateFan]

SF,

Try the code below. Copy it, paste it into a codemodule, and run the macro:

AddFloatingCommandbarWith3Buttons

HTH,
Bernie
MS Excel MVP


Option Explicit

Dim myBar As CommandBar
Dim myButton As CommandBarButton

[snip]

This was such neat code to work with. I stayed up late a couple of
hours and came up with examples to incorporate today at work when I
have a free moment. It's going to be great.

I modified your original code a little bit. I set it to Auto_Open and
I changed the icons used. I put a smiley face for the "hello" button,
a hand for the "goodbye" one and a delete image from the FaceID for
the original "KillMe" portion (which I renamed to "ExitAndDelete"
<g>):

********************************************************
Option Explicit

Dim myBar As CommandBar
Dim myButton As CommandBarButton
Const myName As String = "FloatingCBar"
Sub Auto_Open()

On Error Resume Next
Application.CommandBars(myName).Delete

Set myBar = Application.CommandBars.Add(myName)
With myBar
.Position = msoBarFloating
.Left = 425
.Top = 150
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 59
.Enabled = True
.OnAction = "SayHello"
End With
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Goodbye"
.Style = msoButtonIcon
.FaceId = 51
.Enabled = True
.OnAction = "SayGoodbye"
End With
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Exit"
.Style = msoButtonIcon
.FaceId = 2087
.Enabled = True
.OnAction = "ExitAndDelete"
End With
End With

End Sub
Sub SayHello()
MsgBox "Hello!"
End Sub
Sub SayGoodbye()
MsgBox "Goodbye!"
End Sub
Sub ExitAndDelete()
On Error Resume Next
Application.CommandBars(myName).Delete
End Sub
********************************************************

It was so very, very easy once I saw working code and what it does.
D I just opened up a FaceID word template that I'd dl a couple
years back and looked for the icon reference #'s to use. Very easy.
Thank you!