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

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]


Ed wrote:
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

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]


Ed wrote:
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

.

wrote:
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]

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

-----Original Message-----
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

.