Floating Command Bar &/or Buttons

[Don Lowe]

I have an excel file that will be shared by a couple of dozzen people. In
this file I need to have a floating command bar &/or buttons. I have tried
creating a custom toolbar and it works great for my computer but does not
show up for anyone else.

I have taken an example someone recently gave me making modifications to fit
what I had to create a Floating Command Bar (Shown Below). I am guessing that
I have done something wrong, because I get nothing. Please Help!!!

Sub CommandButton97_Click()
'
' CommandButton97_Click Macro
' Macro recorded 5/6/2008 by g141183
'

Application.Goto Reference:=Worksheets("Master Report").Range("A1"),
Scroll:=True

End Sub

-----------------------------------------------------------------------------------------
Sub Scroll31Right()
ActiveWindow.SmallScroll Toright:=31
End Sub

-----------------------------------------------------------------------------------------
Sub Scroll31Left()
ActiveWindow.SmallScroll Toleft:=31
End Sub

-----------------------------------------------------------------------------------------
Sub MakeFloatingCommandbar()
On Error Resume Next
CommandBars("MyCB").Delete
On Error GoTo 0
CommandBars.Add "MyCB", msoBarFloating, , True
With CommandBars("MyCB")
With .Controls.Add(msoControlButton)
.Caption = "Right"
.FaceId = 80
.OnAction = "Scroll31Right()"
End With
With .Controls.Add(msoControlButton)
.Caption = "Left"
.FaceId = 81
.OnAction = "Scroll31Left()"
End With
With .Controls.Add(msoControlButton)
.Caption = "Main"
.FaceId = 83
.OnAction = "CommandButton97_Click()"
End With
.Visible = True
End With
End Sub

 

[FSt1]

hi
try this....
Application.CommandBars.Add "MyCB", msoBarFloating, , True

regards
FSt1

 

[Don Lowe]

FSt1,

Thank you that made the floating bar. I have two problems though.
1. How do you get the floating Command Bar to start up automaticly when
entering this file?

2. How do I get the buttons to automaticly associate with their designated
macro.
I thought that the command:

..OnAction = "Scroll31Right()"

would automaticly run that macro once the button is clicked. Why is it not
working?

 

[Jon Peltier]

FSt1,

Thank you that made the floating bar. I have two problems though.
1. How do you get the floating Command Bar to start up automaticly when
entering this file?

Include the code that builds the command bar into the Workbook_Open event
procedure in the ThisWorkbook code module, or call a startup procedure from
this event procedure.

2. How do I get the buttons to automaticly associate with their designated
macro.
I thought that the command:

.OnAction = "Scroll31Right()"

would automaticly run that macro once the button is clicked. Why is it not
working?

Does the procedure not get called, or get called but not run? Put a
breakpoint inside the sub to see if code ever stops there.

You could make it more likely to find the right sub:

..OnAction = "'" & ThisWorkbook.Name & "'!Scroll31Right"

- Jon

 

[Don Lowe]

Jon,

Currently, I am having to click a button to run the macro for the floating
command bar. I would like it to appear automaticly when opening this file.

By the way thank you for the code that portion works now.

 

[Jon Peltier]

With the workbook open, right click the Excel icon to the left of the File
menu, and choose View Code. This opens the "ThisWorkbook" code module for
the open workbook. Select the Workbook object in the left dropdown. The Open
method is selected in the right dropdown, and this procedure shell appears
in the code module:

Private Sub Workbook_Open()

End Sub

When the workbook open event occurs (i.e., the workbook is opened), this
event procedure runs. Place the menu creation code within it, or better,
call the menu creation procedure from here:

Private Sub Workbook_Open()
MakeMyMenu
End Sub

- Jon