Validation List Definition Fails for Some Regional Settings

[Randy]

We are using VBA to define an Excel validation list using the code extract shown below

With rngReportRow(1, 7).Resize(1, 45).Validatio
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=offset($P$1198,1,0,$P$1198,1)

This code works fine for some Regional Settings on the computer running the VBA code. (E.g. "English (United States)", "English (United Kingdom)", "Japanese".) But when the Regional settings are either "Dutch (Netherlands)" or "German (Germany)" the Add method fails with an error #1004 (Application-defined or object-defined error).

To add to the curious nature of this problem, it disappears if a breakpoint is set on the Add method line. This might suggest a timing problem, however we have tried a number of different techniues to introduce an artificial delay prior to the Add method without success. (E.g. DoEvents, Wait, Loop, MsgBox.

I would greatly appreciate any suggestions for a solution or workaround to this problem. For reference, this problem was observed on systems running Excel 2000 (9.0.6926 SP-3) on Windows 2000 (5.00.2195 Service Pack 4)

Thanks for any help that you can offer

Cheers
Randy MacDonal

 

[Frank Kabel]

Hi
works for me (Excel 2003 / German version). I only get the #1004 error
if a validation exist before. You may change your code to
With rngReportRow(1, 7).Resize(1, 45).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop,Operator:=xlBetween, _
Formula1:="=offset($P$1198,1,0,$P$1198,1)"

HTH
Frank

 

[Jim Vita]

Hi Randy,

I think your problem is related to the known issue described in the
following KB article:

320369 BUG: "Old Format or Invalid Type Library" Error When Automating Excel
http://support.microsoft.com/?id=320369

Please have a look and see if this is relevant and if it provides any
relief.

Thanks,

Jim

Jim Vita
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Randy]

Hello, Frank

Thanks for the suggestion. I was very hopeful when I gave this a try, but unfortunately deleting a previous validation did not have any impact on my problem. And unfortunately I can't control the versions that our clients use, so moving to a German Excel 2003 isn't an option for me

But many thanks for trying this out for me

Cheers
Rand


----- Frank Kabel wrote: ----

H
works for me (Excel 2003 / German version). I only get the #1004 erro
if a validation exist before. You may change your code t
With rngReportRow(1, 7).Resize(1, 45).Validatio
.Delet
.Add Type:=xlValidateList
AlertStyle:=xlValidAlertStop,Operator:=xlBetween,
Formula1:="=offset($P$1198,1,0,$P$1198,1)

HT
Fran

 

[Randy]

Hello, Jim

Thanks for the reference.

It's not clear to me that this is relevant in my situation since I'm not using any of the ".Net" tools mentioned in the article.. If it is relevant though, I still have problems, because the "System" object referred to in the solution is not something that is available to me from Excel 2000 VBA

Does the bug discussed affect Excell 2000 VBA? If so, is there some VBA alternative to monkeying the "CultureInfo" on the current thread

Thanks for any additional suggestions that you can give

Cheers
Rand


----- Jim Vita wrote: ----


Hi Randy

I think your problem is related to the known issue described in the
following KB article

320369 BUG: "Old Format or Invalid Type Library" Error When Automating Exce
http://support.microsoft.com/?id=32036

Please have a look and see if this is relevant and if it provides any
relief

Thanks

Ji

Jim Vit
Microsoft Developer Suppor

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jim Vita]

Hi Randy,

Sorry, didn't realize VB6 wasn't covered in that article. You may try
calling the following windows API calls in your VB application. Get the
Current LCID, change it, then restore it to the original.

Declare Function SetThreadLocale Lib "kernel32" Alias "SetThreadLocale"
(ByVal Locale As Long) As Long
Declare Function GetThreadLocale Lib "KERNEL32" Alias "GetThreadLocale" ()
As Long

The following KB article also has some more information about using these
API (GetThreadLocale as least)

217751 HOWTO: Get the Current User Locale ID in a VB EXE Without Restarting
http://support.microsoft.com/?id=217751

Thanks,

Jim

Jim Vita
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.