Problem between Excel english / french

**Cyberitch** · 08-13-2008, 04:20 PM

This is probably a bug, but Im looking for a way to bypass the problem.

I dynamically (VBA) add dropdown list on top of some cells and set a call to a macro on the OnAction command like this:

Code:

With Worksheets("Page1")
    Set lb = .Shapes.AddFormControl(xlDropDown, Range("$G$2").Left, Range("$G$2").Top - 1, Range("$G$2").Width, 15.75)
    lb.ControlFormat.ListFillRange = "PagePara!$J$2:$J$4"
    lb.ControlFormat.LinkedCell = "$G$2"
    lb.OnAction = "MacroAutoComplete!R1C1:R5C1"
End With

When Excel is in english, no problem, when Excel is in french the OnAction give me an execution error '1004' the formula is to complicated to be affected to the object (translated from french...).

I suspect that in french the reference "MacroAutoComplete!R1C1:R5C1" is not possible. What should be the universal compatible way to set the reference?

Thanks.
~Ritch

**arthurbr** · 08-14-2008, 12:53 PM

Hi Ritch and welcome to the board
Does the debugger give the line with the RC ref as the wrong one?

**Cyberitch** · 08-15-2008, 01:25 PM

Yes it highlight this line.

**Cyberitch** · 08-20-2008, 08:09 PM

So anyone, any idea?

**Andy Pope** · 08-21-2008, 02:58 PM

the OnAction property expects a macro name not a range

what exactly is the code trying to do?

**Cyberitch** · 08-21-2008, 07:08 PM

"MacroAutoComplete" is a Macrosheet 4.0 and "R1C1:R5C1" is the row:column reference where are the macro command on the sheet.

And dont forget its wokring with english excel version, the problem occur only with french version.

So maybe there's another way to give the OnAction event the sheet!row:column reference?

**Andy Pope** · 08-22-2008, 05:23 AM

I'm too young to know about Macro 4 things

Sorry I can help.

**Cyberitch** · 08-22-2008, 11:06 AM

Originally Posted by Andy Pope

I'm too young to know about Macro 4 things

Sorry I can help.

Gosh now I feel old!!!

Its ok its very hard to find info about macro 4.0, and if you wanna know, its used for mobile device like pocket pc. Since you cant run (not yet) VBA on mobile device I use those old macro to do some automated actions.

Is there another way to reference a cell not using R1:C1 ?

**Andy Pope** · 08-22-2008, 11:22 AM

have you tried using a named range?

otherwise the only way I know is A1 notation.

**martindwilson** · 08-24-2008, 11:40 AM

isnt french equivalent of R1C1 ..... L1C1
look in tools options general(what that is in french i have no idea)for where youd expect see the use r1c1 reference style

**Cyberitch** · 08-24-2008, 06:58 PM

Originally Posted by Andy Pope

have you tried using a named range?

Named range?

Originally Posted by martindwilson

isnt french equivalent of R1C1 ..... L1C1
look in tools options general(what that is in french i have no idea)for where youd expect see the use r1c1 reference style

Yes in tools / options / general / ref style its L1C1 in french, whats that? You mean Excel expect L1 instead of R1?