Defined Name error in VBA

[alpine_21]

Hello,

I'm using Excel 2003 on XP Pro.

I'm creating a basic user form which will allow users to view and edit key variables in a large workbook. The workbook uses defined names for these variables.

When I assign the value of a defined name with a certain format to a text box, I receive an error message. This appears to only happen with defined names that include 2 or more underbars (e.g. _2011_Tax). Names with one underbar (e.g., _2011Rev) work fine.

-----

a = Evaluate(ThisWorkbook.Names("_2011Rev").RefersTo)
UserForm1.TextBox1.Value = a

(This works fine)

-----

b = Evaluate(ThisWorkbook.Names("_2011_Tax").RefersTo)
UserForm1.TextBox2.Value = b

(This fails with an error of: )

Run-time error '-2147352571 (80020005)':
Could not set the Value property. Type mismatch.

-----

Has anyone ever seen this? The defined names function fine in the worksheets, just not with VBA. What am I missing?

Any help appreciated,

Thanks,
Brian

PS - Its been awhile since I've used VBA...

 

[bensonsearch]

Hello,

I'm using Excel 2003 on XP Pro.

I'm creating a basic user form which will allow users to view and edit key variables in a large workbook. The workbook uses defined names for these variables.

When I assign the value of a defined name with a certain format to a text box, I receive an error message. This appears to only happen with defined names that include 2 or more underbars (e.g. _2011_Tax). Names with one underbar (e.g., _2011Rev) work fine.

-----

a = Evaluate(ThisWorkbook.Names("_2011Rev").RefersTo)
UserForm1.TextBox1.Value = a

(This works fine)

-----

b = Evaluate(ThisWorkbook.Names("_2011_Tax").RefersTo)
UserForm1.TextBox2.Value = b

(This fails with an error of: )

Run-time error '-2147352571 (80020005)':
Could not set the Value property. Type mismatch.

-----

Has anyone ever seen this? The defined names function fine in the worksheets, just not with VBA. What am I missing?

Any help appreciated,

Thanks,
Brian

PS - Its been awhile since I've used VBA...

its the variable type you are using.

what is it now? try string, in my opinion if all else fails declare as variant

 

[Jerry Sullivan]

Hi Brian - Welcome to the Board!

I was able to use your code with the named range "_2011_Tax" without getting an error. I tested this on both xl2007 and xl2003.

I tried several ways to generate errors and found that Excel gives a Type Mismatch error when you try to assign an error value such as #DIV/0! or #N/A to TextBox.Text. Perhaps the range you were referencing had an error value at that point in the testing?

BTW..Other tries were:
Reference to a range of more than one Cell: 1004 Error
Merged Cell: Surprisingly worked without error.

Is it possible the problem was something other than the two underbars and that it was coincidence that the error came up at the point in your testing?