Am I missing Something????????????????????????

[jedel]

I am trying create an Excell workbook that will manage some data I have imported from an Access database.

This may sound crazy, but I can't get the VBA code to select a sheet, and then select a range.

Here is the code:

Sheets("Sheet1").Activate
    range("A2").Select

the error is:
"Wrong number of arguments or invalid property assignment"

This is on a brand new workbook. Data has nothing to do with this code, at this stage I just want to select a cell on another sheet. What gives??????????????

Jedel

 

[xlbo]

where is the code placed ???
Is it in a sub ??

Is this being called from excel or access ??

which line does it give an error on

throw me a frickin' bone here

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MakeItSo]

Strange thing.
Actually your code should work fine. As xlbo already guesses: It's gotta be in the wrong place.

Just a veeery wild guess:
Do you have a foreign Excel Version? (Then it's not "Sheet1" but anything else)...

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[RobBroekhuis]

Just hazarding a guess, based on the odd spacing of the two lines - any chance VBA is interpreting the two lines as one statement? Since the Activate method takes no arguments, that may give rise to the error message...


Rob

 

[SkipVought]

jedel,

I have found that under mose circumstances (usually if the amount of data from Access to Excel is moderate - less than 15-20 seconds per query) it is easier to PULL the data FROM Access INTO Excel rather than PUSH.

This is accomplished quite simply in most cases via a stored query -- Data/Get External Data... New Database Query. Once the query has been structured and stored, it is a matter of Data/Refresh Data. It can all be done using the Workbook_Open event if so desired.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jedel]

Ok guys, Thanks for your input. I thought that this would be a simple fix. The error arrives on the second line. If I work each line individually, no probs, but as soon as I try and use both lines, the Error pops up.

I've tried every which way I know to get this to work:

1 I created a macro with a shortcut key. This gives the error I stated above.

2. I attached the macro to a control, either a button, label or text box, and I get a 1004 error stating that the select method on the range is invalid. Again, using this method each line used individually works fine, but not together.

I have a sneaking suspicion that this is a bug in Excell 2000. I've used this code extesively in 97 with no probhlems. I'm about to go to work and try it, I'll let you know what happens.

Cheers

Jedel

 

[RobBroekhuis]

Just wondering - if you separate the two statements with an innocuous line of code (or even a comment), do you still get the error?


Rob

 

[jedel]

Nope, Extra code doesn't work. I even tried joining the argument ie

Sheets("Sheet1&quot.Range("A2&quot.Select

This doesn't work either

Jedel

 

[xlbo]

There must be something else happening here. The error on the commandbutton changes and is more likely that you need to set the "TakeFocusOnClick" property of the button to false. As to the initial error, I cannot reproduce it. Is there ANY other code at all ???

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MakeItSo]

Err - just another wild guess:
have you taken a look into your Options?
Check your settings there...

Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[jedel]

Guys,
I'll apologise for being vague earlier if you can make you rexplainations a little clearer.

The code is pretty simple. Here it is in it's entire form:

Sub range()
'
' range Macro
' Macro recorded 12/11/2003 by Jedel
'

'
    Sheets("Sheet1").Select
    range("A2").Select
End Sub

MakeItSo,
What am I looking for in my Options? is it the Options in Excell or VBA???

xlbo,
Where do I fing this 'TakeFocusOnClick' property?? and if that's the problem, then why will the error be the same no matter what I do or how I apply it?

Thanks for you responses

Jedel

 

[jedel]

Gents,
Just Kick me! I found it. It was the NAME of the macro. It had the same name as one of the functions. As you can see in the code I named the macro "range". DOH!!!!

I must learn to get my naming conventions down pat.

Thanks all the same

Jedel

 

[xlbo]

the takefocusonclick property will only affect the code when it is attached to a controls toolbox button. I was basically saying to ignore that error 'cos it's not the same as the original error BUT.......WE have a WINNER !!

The error is cause by naming your sub "Range". This is a restricted keyword and shouldn't be used for sub / function names or variable names. Just change the name of the sub to summat like myRange and all will be well

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MakeItSo]

Damn it, you're right! We were all so fixed on other possible probs that we missed the most simple one...




Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos