PDA

View Full Version : Activate range on a different sheet



arrun
06-24-2008, 10:42 AM
Hi all,

I have created a commandbutton and following codes are associated with that :

Private Sub CommandButton1_Click()
Sheets("Prob-1").Activate
Range("a1").Activate
End Sub



However when I run this code then I get following error :

Run time error '1004'
activate method of range class failed.

In the mean time I have gone through the help page corresponding to that error. And as par it's advise I have checked "trust access to visual basic project" under tool -> macro -> security. But still that error exists.

Can anyone tell me where is the error on that? Your help will be highly appreciated.

Regards,

greymalkin
06-24-2008, 10:53 AM
I am going to assume the code for the button click event is within a different sheet object than Sheet "Prob-1". If this is the case then you will need to fully qualify:

Sheets("Prob-1").Activate
Sheets("Prob-1").Range("A1").Select (or activate)

If you don't want to have to fully qualify you can run your code in a module and reference that module in the click event of the sheet object, or there's probably another more elegant way of doing it..this is just all I know :).

Ago
06-24-2008, 10:54 AM
Range("A1").Select

activate is only (as far as i know) used on sheets.
select is used on cells

arrun
06-24-2008, 10:55 AM
I also have tried with "select" however getting same error :(

Ago
06-24-2008, 11:02 AM
did you use a capital A in Range("A1").Select?

grichey
06-24-2008, 11:07 AM
did you use a capital A in Range("A1").Select?

shouldn't matter. I just did the same routine and it worked fine with

Range("a1").select

Make sure you have a sheet named the same as the one you're trying to activate!

arrun
06-24-2008, 11:10 AM
Hi greymalkin (http://vbaexpress.com/forum/member.php?u=10777), your suggestion really worked, thanks

xld
06-24-2008, 12:46 PM
Range("A1").Select

activate is only (as far as i know) used on sheets.
select is used on cells

You can activate a range as well.

R1C1
06-24-2008, 01:31 PM
Ago,

Activate is not confined to Sheets. Activate may be used with Chart and ChartObjects, Worksheet objects, OLEObject objects, Pane objects, Range objects, Window objects, & Workbook objects. A cell is a Range object.

Alan

lucas
06-24-2008, 01:43 PM
Hi aarun, I changed the title of your thread. Please try to give your threads meaningful names so people can find this problem with a search.

arrun
06-24-2008, 11:08 PM
Hi Lucas, ya I agree this should be a better idea:whistle: