macro problems

Nathalie · May 24th, 2002, 07:15 AM

I created a macro that runs pretty good which, filters my master database, copies and pastes on another sheet. When i attempt to place the code in commandbutton, error reads select method of range class failed, any ideas?

[ This Message was edited by: Nathalie on 2002-05-24 12:40 ]

[ This Message was edited by: Nathalie on 2002-05-24 12:45 ]

Joe Was · May 24th, 2002, 07:33 AM

Most code will work if you use a Form-Button, but will need to be re-written if you use any other button!

View-Toolbars-select "Forms"-then the button icon.

Attach the macro code to the button. Delete the button caption, right click the button, select format, select font-bold, select color, OK. Then type your button caption, re-size the button to your needs. Hope this helps. JSW

dsnbld · May 24th, 2002, 08:49 AM

The select method for the range is failing because an object has not been properly declared.

Rather than moving the code you could try this for now:

Private Sub CommandButton1_Click()
Call Macro1
End Sub

To do this the macro must be a Sub, not Private Sub.

Nathalie · May 24th, 2002, 01:39 PM

I tried the method of calling my macro and tells me that sub or function is not defined???

Call macroname? What could be wrong

[ This Message was edited by: Nathalie on 2002-05-24 12:43 ]

dsnbld · May 24th, 2002, 01:49 PM

It sounds like the Call failed because you are wanting to select a range on another sheet.

If you have the command button on sheet1 and you want to select a range on sheet1 you have this
Range("A1").Select

If you have the command button on sheet2 and you want to select a range on sheet1 you have this

Sheets("Sheet1").Select
Range("A1").Select

Nathalie · May 24th, 2002, 02:13 PM

I think i got it. Thanks for all the help!
Nathalie

Ivan F Moala · May 24th, 2002, 10:50 PM

Quote:

On 2002-05-24 06:15, Nathalie wrote:
I created a macro that runs pretty good which, filters my master database, copies and pastes on another sheet. When i attempt to place the code in commandbutton, error reads select method of range class failed, any ideas?

[ This Message was edited by: Nathalie on 2002-05-24 12:40 ]

[ This Message was edited by: Nathalie on 2002-05-24 12:45 ]

Nathalie
Excel97 ??
If so the there is a problem in calling routines that Act on other Objects called from ActiveX commandbuttons. The workaround for this is to;
1) Just select a cell before the call in your macro OR
Bst option
2) Set the commandbuttons [TakeFocus*******] to False.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 24th, 2002, 07:15 AM	#1
Nathalie New Member Join Date: May 2002 Posts: 48	I created a macro that runs pretty good which, filters my master database, copies and pastes on another sheet. When i attempt to place the code in commandbutton, error reads select method of range class failed, any ideas? [ This Message was edited by: Nathalie on 2002-05-24 12:40 ] [ This Message was edited by: Nathalie on 2002-05-24 12:45 ]

May 24th, 2002, 07:33 AM	#2
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	Most code will work if you use a Form-Button, but will need to be re-written if you use any other button! View-Toolbars-select "Forms"-then the button icon. Attach the macro code to the button. Delete the button caption, right click the button, select format, select font-bold, select color, OK. Then type your button caption, re-size the button to your needs. Hope this helps. JSW

May 24th, 2002, 08:49 AM	#3
dsnbld Board Regular Join Date: May 2002 Posts: 206	The select method for the range is failing because an object has not been properly declared. Rather than moving the code you could try this for now: Private Sub CommandButton1_Click() Call Macro1 End Sub To do this the macro must be a Sub, not Private Sub.

May 24th, 2002, 01:39 PM	#4
Nathalie New Member Join Date: May 2002 Posts: 48	I tried the method of calling my macro and tells me that sub or function is not defined??? Call macroname? What could be wrong [ This Message was edited by: Nathalie on 2002-05-24 12:43 ]

May 24th, 2002, 01:49 PM	#5
dsnbld Board Regular Join Date: May 2002 Posts: 206	It sounds like the Call failed because you are wanting to select a range on another sheet. If you have the command button on sheet1 and you want to select a range on sheet1 you have this Range("A1").Select If you have the command button on sheet2 and you want to select a range on sheet1 you have this Sheets("Sheet1").Select Range("A1").Select

May 24th, 2002, 02:13 PM	#6
Nathalie New Member Join Date: May 2002 Posts: 48	I think i got it. Thanks for all the help! Nathalie