MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Float on you crazy button


Posted by Dani on September 21, 2001 6:50 PM

Hi, I'm trying to have a button which calls a Macro.
My worksheet is 20 pages long.
Do I have a way to make this button a floating button, so when
I scroll down the button moves down?


Thanks a lot once again.

dani


Posted by Duncan on September 21, 2001 10:16 PM


Put the button in the first row and use Freeze Panes to freeze the first row.

Posted by Tom Urtis on September 22, 2001 12:11 AM

Here's how to do it

Dani,

You can achieve this by creating a custom toolbar and dragging a custom button to it, then assigning your macro to that button. This custom toolbar, which can float or be docked as any toolbar, is created with these 6 steps:

(1) Compose your macro.

(2) Right click on any existing toolbar and left click on Customize (or click View > Toolbars > Customize), then choose the Toolbars tab and click the New button.

(3) In the "Toolbar name" field type in whatever name you want to name your new toolbar or accept the default name and click OK.

(4) Click on the Commands tab of the Customize dialogue box, then select Macros in the Categories pane, and drag the custom button (a smiley face or any button in any category will do) to your new toolbar.

(5) Right click on that newly placed custom button and select Assign Macro, and (you guessed it) assign your macro.

(6) Click the Close button on the Customize dialogue box and now you are good to go.

Tom Urtis

Posted by Tom Urtis on September 22, 2001 1:18 AM

Oh, and one more thing

I forgot to mention that the custom toolbar remains visible when any other workbook is open unless you specify otherwise. To only have this custom toolbar appear when you activate your specific workbook for which this toolbar is meant, right-click on the Excel workbook icon to the left of File on the menu bar, choose View Code, and paste the following code in there (modify your toolbar name as needed):

Private Sub Workbook_Activate()
Toolbars("Custom 1").Visible = True
End Sub

Private Sub Workbook_Deactivate()
Toolbars("Custom 1").Visible = False
End Sub


Tom Urtis

Hi, I'm trying to have a button which calls a Macro.