Floating command button

[Pat]

Can anyone tell me if it possible to place command buttons into a custom
floating toolbar where the button will always remain visible regardless if
scrolling horizontally or vertically?

Thanks
Pat

 

[Frans van Zelm]

Hi Pat,

Perhaps I don't get your point (question). But as far as I know, toolbars
are
always fixed on their latest location in the workbook window. They don't
react to
scrolling or zooming. Even when you risize (not minimize) the window, they
stay
fixed.

To add a button to a toolbar ... Is that the problem?
- Tools-Customize-tab Toolbars and then New
- tab Commands-category Macros
- drag Smiley to bar
- right click and Assign macro

Frans

 

[Pat]

Hello Frans,

That was simple enough, I have created a new toolbar without any great
difficulty. Can I now use the toolbar I have created for a particular
worksheet? When the worksheet is activated the custom toolbar will also
appear, when I move to a different worksheet it will disappear. Is this
possible?

Pat

 

[Frans van Zelm]

Hi Pat,

So that was the point: show / hide a toolbar when you activate a sheet.

Try the code below (Excel XP). It *must* be in the objectmodule for the
specific
sheet: right click the sheet tab and then View Code (or something like
that).

Private Sub Worksheet_Activate()
Application.CommandBars("YourBar").Visible = True
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("YourBar").Visible = False
End Sub

Frans

 

[Tibbs]

I used this to great effect (Many thanks Frans!), but was wondering i
anybody could help me with a modification.

After using the Show/Hide macros, if I open the sheet in the workshee
that has the floating toolbar, it doesn't show up, and also it stays i
I have two workbooks open and toggle between the two. Is there a wa
to:

Make it appear when I open the workbook with that sheet active
Make the toolbar disappear when I ctrl-tab to another workbook.

All help very gratefully received.

Thanks,

Chris

Hi Pat,

So that was the point: show / hide a toolbar when you activate
sheet.

Try the code below (Excel XP). It *must* be in the objectmodule fo
the
specific
sheet: right click the sheet tab and then View Code (or something like
that).

Private Sub Worksheet_Activate()
Application.CommandBars("YourBar").Visible = True
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("YourBar").Visible = False
End Sub

Frans