Hi all,

I'm having difficulty understanding why some code created by the Macro
Recorder will not work for a Command Button Click Event.

To illustrate the problem, I created the following macro (in Excel
2000):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/4/2006 by Teodomiro
'

'
Sheets("Sheet2").Select
Range("A1:B20").Select
End Sub

Running it as a macro, it works fine. But when I create a Command
Button, and copy the code into it's Click event, I get:

Run-time error '1004':
Select method of Range class failed

I'm just trying to select a range on a different sheet. Can anyone
explain what's going on here?


--
Teodomiro


------------------------------------------------------------------------
Teodomiro's Profile: http://www.excelforum.com/member.php...o&userid=33140
View this thread: http://www.excelforum.com/showthread...hreadid=529515

Did you take a 'forms' button?

Right click and you can immediately assign your macro to the button

--
Zur
-----------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...fo&userid=1464
View this thread: http://www.excelforum.com/showthread.php?threadid=52951

Presumably the button is on sheet1?

The problem is that although Sheet2 is selected, the code is in Sheet1, so
the Range statement is still referring to Sheet1 implicitly, and cannot
select that as Sheet1 is not now active. Just use

Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1:B20").Select

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Teodomiro" wrote
in message ...

Hi all,

I'm having difficulty understanding why some code created by the Macro
Recorder will not work for a Command Button Click Event.

To illustrate the problem, I created the following macro (in Excel
2000):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/4/2006 by Teodomiro
'

'
Sheets("Sheet2").Select
Range("A1:B20").Select
End Sub

Running it as a macro, it works fine. But when I create a Command
Button, and copy the code into it's Click event, I get:

Run-time error '1004':
Select method of Range class failed

I'm just trying to select a range on a different sheet. Can anyone
explain what's going on here?


--
Teodomiro


------------------------------------------------------------------------
Teodomiro's Profile:
http://www.excelforum.com/member.php...o&userid=33140
View this thread: http://www.excelforum.com/showthread...hreadid=529515

Bob Phillips Wrote:
Presumably the button is on sheet1?

The problem is that although Sheet2 is selected, the code is in Sheet1
so
the Range statement is still referring to Sheet1 implicitly, an
cannot
select that as Sheet1 is not now active. Just use

Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1:B20").Select

End Sub


--
HTH

Bob Phillips


Thanks Bob, that's got it. I had tried just the one line
sheets("sheet2").range("a1.b20").select, and got the same error. I
seems redundant to specify the sheet twice, but I'm in favor of simpl
solutions

--
Teodomir

-----------------------------------------------------------------------
Teodomiro's Profile: http://www.excelforum.com/member.php...fo&userid=3314
View this thread: http://www.excelforum.com/showthread.php?threadid=52951

That wouldn't work because you would still be on Sheet1 and select has to be
on the activesheet.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Teodomiro" wrote
in message ...

Bob Phillips Wrote:
Presumably the button is on sheet1?

The problem is that although Sheet2 is selected, the code is in Sheet1,
so
the Range statement is still referring to Sheet1 implicitly, and
cannot
select that as Sheet1 is not now active. Just use

Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1:B20").Select

End Sub


--
HTH

Bob Phillips


Thanks Bob, that's got it. I had tried just the one liner
sheets("sheet2").range("a1.b20").select, and got the same error. It
seems redundant to specify the sheet twice, but I'm in favor of simple
solutions.


--
Teodomiro


------------------------------------------------------------------------
Teodomiro's Profile:
http://www.excelforum.com/member.php...o&userid=33140
View this thread: http://www.excelforum.com/showthread...hreadid=529515