Run time error '1004' when running simple 'ClearContents' command

[StevenK]

I have a very simple Excel spreadsheet with 2 sheets ('Sheet1' and 'Sheet2').
I've added a (Visual Basic) 'CommandButton' onto the first sheet ('Sheet1') with the following code underneath it:

Private Sub CommandButton1_Click()
'
Sheets("Sheet2").Select
Cells.Select
Selection.ClearContents
Range("A1").Select

End Sub

... I've established this code via use of clearing the contents of the second sheet using the recording of a Macros.

When I click the button on 'Sheet1' I get the following 'Microsoft Visual Basic' error message:
Run-time error '1004':
Select method of Range class failed

... what am I doing wrong here?
This is a very simple scenario and I can't seem to make it work correctly.

How can I get the second sheet contents to clear by clicking a CommandButton on the first?

Any help / pointers would be very greatly appreciated.
Thanks in advance.

Steve

 

[nghiapp]

The error could come if sheet2 is protected.
If not try with Sheets("Sheet2").Cells.Select
Hope this help.

 

[xlbo]

If you have used a commandbutton from the Controls Toolbox menu, make sure that the "TakeFocusOnCLick" property is set to false

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question