Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Command Bar &/or Buttons
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Command Bar &/or Buttons
hi
try this.... Application.CommandBars.Add "MyCB", msoBarFloating, , True regards FSt1 "Don Lowe" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Command Bar &/or Buttons
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? "FSt1" wrote: hi try this.... Application.CommandBars.Add "MyCB", msoBarFloating, , True regards FSt1 "Don Lowe" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Command Bar &/or Buttons
"Don Lowe" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Command Bar &/or Buttons
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" wrote: 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 _______ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Command Bar &/or Buttons
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 he Private Sub Workbook_Open() MakeMyMenu End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Don Lowe" wrote in message ... 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" wrote: 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 _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fixed or floating command buttons | Excel Programming | |||
How to get buttons on floating control to pick up colour | Excel Programming | |||
Floating Command button | Excel Programming | |||
Floating command button | Excel Discussion (Misc queries) | |||
My Command Buttons keep floating on the sheet??? | Excel Programming |