Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

Floating  Command Bar &/or Buttons

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Don Lowe - 12 May 2008 21:10 GMT
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 - 12 May 2008 22:32 GMT
hi
try this....
Application.CommandBars.Add "MyCB", msoBarFloating, , True

regards
FSt1

> 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
[quoted text clipped - 51 lines]
>     End With
> End Sub
Don Lowe - 13 May 2008 12:46 GMT
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?

> hi
> try this....
[quoted text clipped - 58 lines]
> >     End With
> > End Sub
Jon Peltier - 13 May 2008 14:09 GMT
> 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.
[quoted text clipped - 4 lines]
> 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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
Don Lowe - 13 May 2008 15:02 GMT
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.

> 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.
[quoted text clipped - 9 lines]
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
Jon Peltier - 13 May 2008 19:34 GMT
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> Jon,
>
[quoted text clipped - 16 lines]
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.