Controls
Posted by Jeff on September 12, 2001 1:57 PM
Is there any way to place a button on a cell to perform the following task:
When a user clicks the button on A3, the text on A2 is copied and pasted to Sheet2, A2?
If so, how do I program this task?
JB
Posted by Tom Urtis on September 12, 2001 4:06 PM
Assuming you are new to VB, follow these steps:
Click on Tools > Macro > Visual Basic Editor.
Click Insert > Procedure.
In the Name filed, enter CopyThis (no spaces).
Select Sub Type, Public Scope, click OK
Paste this where the cursor is flashing, assuming your source sheet is called Sheet1:
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range("A2").Copy
Sheets("Sheet2").Select
Range("A2").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("A2").Select
Application.ScreenUpdating = True
Hit Alt+Q
Assuming the Forms toolbar is not visible,
Click Insert > Toolbard > Forms
Find the "Button" icon on the toolbar, click on it, then go onto the worksheet and with your left mouse button pressed, drag the mouse to form the shape of the button you want.
The Assign Macro dialogue box will appear, double click on the Macro Name called CopyThis.
Click anywhere on the sheet to deselect the button, or click on it while selected (with the fuzzy border around it) first to edit the wording on the button, to change it from Button 1 to Copy or whatever you want to name it.
Then go grab a Budweiser.
Hope this helps!!!
Tom Urtis
