Copying Command Button to New Sheet

[olympus]

I have a command button on sheet2 that runs a macro on sheet2. I would like to copy the command button to sheet1 so that the user does not have to ever look at sheet2. I've tried copy,paste special and I've tried to add a macro that first goes to sheet2 before executing the command button macro. Needles to say neither method worked for me. Thanks in advance for your help.
John

 

[calculus]

Go to design mode first to make this happen (View>Toolbars>Control ToolBox) and click the blue triangle icon in the upper left corner.

Then simply cut and paste your command button where you want it. Get out of design mode the same way you got in it.

calculus

 

[SkipVought]

Hi,

Copying the CommandButton really does not accomplish what you want. The associated macro does not copy.

Just activate sheet1, activate the Control Toolbax toolbar, insert the control, double click the control and insert the code to run the macro in the Click Event.

BTW, if you run the macro from sheet1 that acts on sheet2 data, you may have some problems UNLESS your range references INCLUDE the sheet2 object!



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[olympus]

Skip, You are correct. The copied command button and its macro will not run after being copied from sheet2 to sheet1. Do you have any idea how to correct this. I'm very very new at VBA so I'd appreciate any help that you can provide. The code that the command button runs was very kindly supplies to me on this forum by TonyJollans and is as follows:

Private Sub CommandButton1_Click()
Me.PageSetup.PrintArea _
= Me.Parent.Names(Me.Range("B3").Value).RefersToRange.Address
ActiveWindow.SelectedSheets.PrintOut
End Sub

I've tried to attach a macro to a button on sheet run that clicks on command button1, but this macro fails to run commandbutton1. I have also tried to use the call function to call a macro called "goto2" that selects sheet2, but this did not work either. Thanks.

 

[PHV]

You can try something like this:
Sheets(2).PageSetup.PrintArea _
= ActiveWorkbook.Names(Sheets(2).Range("B3").Value).RefersToRange.Address

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244