New to VBA, getting unexpected error 1004

[Teodomiro]

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?

[Zurn]

Did you take a 'forms' button?

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

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Teodomiro]

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.

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>