Programming command button to execute on a different worksheet

[Ed]

I have made a complex workbook that consists of multiple
command buttons. I an needing the program a command
button to (.ClearContents) of certain cells on a
different worksheet.
Within a work sheet the code is as follows:
Range ("C1:C5").ClearContents
Is there anyone out there that can tell me how to execute
this from one worksheet to another.
Example:
Button is on worksheet "1" and I need to clear contents
on worksheet "2".
Thanks for your help in advance
Ed

 

[Jake Marx]

Hi Ed,

You can (and should in most circumstances) preface the Range with a
Worksheet object:

Worksheets("Sheet2").Range("C1:C5").ClearContents

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Tod]

You can prompt the user to enter a sheet number.

Private Sub CommandButton1_Click()
Dim SheetNum As Integer

'If user enters other than a number, or number too
high, procedure will exit without changes.
On Error GoTo EndIt
SheetNum = InputBox("Enter sheet number", , 1)
ThisWorkbook.Worksheets(SheetNum).Range
("A4:A6").ClearContents
EndIt:
End Sub

One problem is that sheet numbers may not always be in
order. But hopefully this'll give you the idea.

tod

 

[Guest]

Thanks for your help Jake
Sometimes it is the simple things that make no sense
after you stare at the screen long enough.
Thanks again
Ed

-----Original Message-----
Hi Ed,

You can (and should in most circumstances) preface the Range with a
Worksheet object:

Worksheets("Sheet2").Range("C1:C5").ClearContents

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have made a complex workbook that consists of multiple
command buttons. I an needing the program a command
button to (.ClearContents) of certain cells on a
different worksheet.
Within a work sheet the code is as follows:
Range ("C1:C5").ClearContents
Is there anyone out there that can tell me how to execute
this from one worksheet to another.
Example:
Button is on worksheet "1" and I need to clear contents
on worksheet "2".
Thanks for your help in advance
Ed

.

 

[Jake Marx]

Thanks for your help Jake
Sometimes it is the simple things that make no sense
after you stare at the screen long enough.
Thanks again

No problem, Ed - glad to help.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]