Problem between Excel english / french

**Cyberitch** · 08-13-2008, 03: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:

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, 11:53 AM

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, 12:25 PM

Yes it highlight this line.

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

So anyone, any idea?

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

the OnAction property expects a macro name not a range

what exactly is the code trying to do?

**Cyberitch** · 08-21-2008, 06: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?