Creating a floating button?

[Simon Lloyd]

Hi all, is it possible to create an Autoshape or button and have it floating?, when i say floating i mean always in view whether you scroll up, down, left or right!, I know i can create a button on the toolbar but i physically wanted it on a couple of worksheets in a workbook not all of them.

Any ideas?

[stanl]

I know i can create a button on the toolbar but i physically wanted it on a couple of worksheets in a workbook not all of them.
Any ideas?

Could you not create the floating toolbar with a single button and just use the activate/deactivate events for the specific worksheets to make it visible or hidden? Stan

[Simon Lloyd]

Yes Stan, i could creat a floating toolbar, originally i wanted to have a floating Autoshape, i also thought that having a toolbar with one button on does not look as neat as a single floating button hence me posing this question, but thanks for the reply, all input helps!

[Gert Jan]

Would it be an option to put the button in the top left, and then freeze panes?
Not really a floating button, but always visible

Gert Jan

[Simon Lloyd]

No Gert, as the worksheets are already in use and formatted. I tried creating a the autoshape on the fly (i say autoshape as all the other buttons in the workbook are Autoshapes) and then either deleting it and recreating it when the the view is changed but it's tedious and slow, and everytime you create an Autoshape it has a new number! so assigning the macro is a pain. One other problem with a toolbar with one button is that it remains in the place you put it, so it covers cells or data, to access the cell or see the data you have to move the button, so i guess ideally the button should always appear at the last row under all the data.

[Gert Jan]

I came across this http://www.vbforums.com/showpost.php?p=2815603&postcount=6

maybe a start?

[daniel_d_n_r]

VB.NET ?
There doesn't seem to be any options to show a free floating button control.
a VBA userform without caption and squashed down to the same size as the button doesn't look too bad.

reason I menioned VB.NET is that you can easily make a userform with button that has no border.
However I am not too sure if you can use this within a VBA script,or if it would be worth the effort for the sake of a border.
would be interesting to know though.

[Carl A]

http://www.mrexcel.com/archive/Controls/5924.html

The following code is adapted from the example on mrexcel. It isn't exactly what you want but its close.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With ActiveWindow.VisibleRange
' subsitute the name of the Control in question
CommandButton1.Top = .Top + 5
CommandButton1.Left = .Left + .Width - CommandButton1.Width - 45
' 45 seems to be the right number of points--use fewer if you've hidden the scrollbars or the row headings
End With
End Sub