Floating button????

[taylor2k3]

I have a circular autoshape with a macro assigned to it, when clicked it
sorts the excel sheet, but when i scroll down the page the button scrolls
with the text and cannot be seen anymore. is there a way to float the button
above the worksheet and lock it there?

thanks

 

[Jim Rech]

Use a custom toolbar/button. It may not lock on place but it does float.

--
Jim Rech
Excel MVP
|I have a circular autoshape with a macro assigned to it, when clicked it
| sorts the excel sheet, but when i scroll down the page the button scrolls
| with the text and cannot be seen anymore. is there a way to float the
button
| above the worksheet and lock it there?
|
| thanks
|
|

 

[taylor2k3]

I need it to work when other people open it up, would a custom button only
work locally?

 

[Jim Rech]

You can attach the toolbar to the workbook so it appears whenever the
workbook is open (View, Toolbars, Customize, Toolbars, Attach). When doing
this it is a real good idea to delete the toolbar when the workbook closes.
This can only be done by a macro. In the workbook's ThisWorkbook module add
this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
CommandBars("TheNameOfYourToolbar").Delete
End Sub

Also, one thing to be aware of... After you attach a toolbar to a workbook,
if you make changes to the toolbar, you have to reattach the toolbar. What
gets attached is a picture of the toolbar at a point in time.

--
Jim Rech
Excel MVP
|I need it to work when other people open it up, would a custom button only
| work locally?
|
| | > Use a custom toolbar/button. It may not lock on place but it does
float.
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > |I have a circular autoshape with a macro assigned to it, when clicked
it
| > | sorts the excel sheet, but when i scroll down the page the button
| scrolls
| > | with the text and cannot be seen anymore. is there a way to float the
| > button
| > | above the worksheet and lock it there?
| > |
| > | thanks
| > |
| > |
| >
| >
|
|

 

[Doug Van]

Right click on the shape, then format auto shape, then click on the
properies tab, select the option you want. I think you are looking for the
"don't move or resize with cells" option

Doug

 

[Jim Rech]

Another option (simpler) is to go with the shape you have, putting it in the
top, left corner of the sheet. Then use Window, Split on the row just below
and the column just to the right of the shape. It should then always be in
view.

--
Jim Rech
Excel MVP

| You can attach the toolbar to the workbook so it appears whenever the
| workbook is open (View, Toolbars, Customize, Toolbars, Attach). When
doing
| this it is a real good idea to delete the toolbar when the workbook
closes.
| This can only be done by a macro. In the workbook's ThisWorkbook module
add
| this code:
|
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
| On Error Resume Next
| CommandBars("TheNameOfYourToolbar").Delete
| End Sub
|
| Also, one thing to be aware of... After you attach a toolbar to a
workbook,
| if you make changes to the toolbar, you have to reattach the toolbar.
What
| gets attached is a picture of the toolbar at a point in time.
|
| --
| Jim Rech
| Excel MVP
| ||I need it to work when other people open it up, would a custom button
only
|| work locally?
||
|| || > Use a custom toolbar/button. It may not lock on place but it does
| float.
|| >
|| > --
|| > Jim Rech
|| > Excel MVP
|| > || > |I have a circular autoshape with a macro assigned to it, when clicked
| it
|| > | sorts the excel sheet, but when i scroll down the page the button
|| scrolls
|| > | with the text and cannot be seen anymore. is there a way to float the
|| > button
|| > | above the worksheet and lock it there?
|| > |
|| > | thanks
|| > |
|| > |
|| >
|| >
||
||
|
|