When a sheet turns into a button
Follow these steps:
- Start with a new workbook.
- Press Alt+F11 to activate the VB Editor.
- Insert a new VBA module, and enter this procedure:
Sub SayHello()
MsgBox "Hello!"
End Sub
- Press Alt+F11 to return to your workbook.
- Right-click any toolbar and choose Forms (this displays the Forms toolbar).
- Use the Button icon on the Forms toolbar to add a button to the worksheet. Put the button somewhere near the upper left corner, around cell B2.
- When prompted, assign the SayHello procedure to the button. Click the button to ensure that it executes the macro.
- Right-click the button and choose Format Control. Click the Properties tab and make sure the 'Move and size with cells' option is selected.
- Select rows on the worksheet beginning with Row 1 to well past the button, and. choose Edit - Delete. The button will get sucked up to the top of the worksheet. You can still detect a tiny sliver of the button if you look carefully, just below the column headings.
- Protect the worksheet. (use Tools - Protection, and accept the defaults.)
Now we enter the Twilight Zone of Excel...
Notice your mouse cursor -- it displays a hand, just like it does when it hovers over a button. Now try clicking in any cell on the worksheet. Doing so runs the macro attached to the button. Excel seems to think the button extends over the entire worksheet!
When you scroll down to reveal more cells, those cells behave normally.
(Contributed by Dan Evens)