Floating command button

Pat · 01-06-2005, 09:06 AM

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 · 01-06-2005, 09:06 AM

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" <glass_patrick@hotmail.com> wrote in message
news:41dd3369$0$14602$ed2619ec@ptn-nntp-reader01.plus.net...
> 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
>
>

Pat · 01-06-2005, 11:06 AM

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" <fhvzelm@hotmail.com> wrote in message
news:uTeBNA$8EHA.2568@TK2MSFTNGP10.phx.gbl...
> 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" <glass_patrick@hotmail.com> wrote in message
> news:41dd3369$0$14602$ed2619ec@ptn-nntp-reader01.plus.net...
>> 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 · 01-06-2005, 11:06 AM

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

"Pat" <glass_patrick@hotmail.com> wrote in message
news:41dd4639$0$14624$ed2619ec@ptn-nntp-reader01.plus.net...
> 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
>
> etc.

**Tibbs** · 04-07-2006, 08:28 AM

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

After using the Show/Hide macros, if I open the sheet in the worksheet that has the floating toolbar, it doesn't show up, and also it stays if I have two workbooks open and toggle between the two. Is there a way 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

>>Original Post by Frans:

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

>>Ends

**DaveVoss** · 08-31-2012, 03:49 PM

I found the simplest solution to be to create an extra row at the top of the worksheet and put command buttons there. Include that row in Freeze Panes and the buttons are always there.