Accessing defined names in Excel from VBA

[sdiverdan]

I'm having a problem accessing the defined names of my worksheet. I have names defined in my workbook and can access them like this:

Private Sub Introduction_Click()
MsgBox Evaluate("Itext"), vbInformation + vbOKOnly, Evaluate("Ititle")
End Sub

This is a simple message box showing the introduction title and text by clicking on an intro button. However when I want to assign a value to a defined name or place the range of a defined name in a cell I cannnot get it to work.

this works:
Sheets("Tables").Cells(9, 5) = "Types"
but I have a name for this cell called "FName". How do I assign "Types" to FName?

I also have a name that defines a range called "FRange" which is A3:A10. I can fill a listbox in a userform initialization with:
With Me.ListBox1
.Clear
.List = Sheets("Tables").Range("A3:A10").Value End With

But .List = Sheets("Tables").Evaluate("FRange").Value
does not work and I cannot assign the value of FRange which shows as A3:A10 to a cell or to a cell called FRangeName.

These two issues are related to how to reference Excel defined names from VBA and to assign values to them. Any help would be appreciated.

Dan

 

[macropod]

Hi Dan,

Did you try:
.List = Sheets("Tables").Range("FRange").Value


Cheers
[MS MVP - Word]

 

[sdiverdan]

Yes I did. I receive a compile error stating that it's an invalid or unqualified reference. That's basically the problem: how to reference defined names.

Dan

 

[PHV]

a compile error{/i]
Really ?

 

[DaveInIowa]

Maybe?

    Sheets("Tables").Names("FName").Value = "Type"
    Sheets("Tables").Names("FRange").RefersTo = Sheets("Tables").Range("A3:A10")

PHV: Debug > Compile VBAProject

 

[PHV]

Dave, I know that, but I expected a RunTime error ...

 

[DaveInIowa]

PHV - You're correct, and I should have known what you meant by that. Until I actually tested the statement, I wouldn't have known whether it would produce a compile-time or run-time error. It appears sdiverdan took macropod's suggestion literally and did not enclose

.List = Sheets("Tables").Range("FRange").Value

in a With..End With block, causing the aforementioned "invalid or unqualified reference" error.

 

[sdiverdan]

macropod was right! I forgot the with statement as DaveInIowa pointed out. When something's not working you try all kinds of things and I must have deleted my with/end with statement with a cut and paste. Stupid!!

.List = Sheets("Tables").Range("FRange").Value works

However, Sheets("Tables").Names("FName").Value = "Type"
gets a Runtime 1004 Application defined or object defined error. Replacing "Names" with "Range" fixed that.

We're half way there: How to defined a range to a defined name.
Sheets("Tables").Names("FRange").RefersTo = Sheets("Tables").Range("A3:A10") gets a run time 451 error.
How to set FRange to "A3:A10"?
Sheets("Tables").Range("FRange").Value = "A3:A10"
places the address in the cell but the the list statement gets a runtime error 381 'could not set list property'. Tried .value and .address with same error.

Maybe I'm being too complex. I'm trying to use one userform to display several different lists. Using the .click subs to set the name and range and then using the userform to be initialized using the variable name and range.


Dan

 

[sdiverdan]

Got it! Finally figured it out. Simple really when you use the Evaluate. Here's the final code:

in my main form:
Private Sub VType_Click() 'view type
Sheets("Tables").Range("FName").Value = "Types"
Sheets("Tables").Range("FRange").Value = Evaluate("TypeName").Address 'TypeName is a defined name in Tables
UserForm1.Show
End Sub

in my userform:
Private Sub UserForm_Initialize()
Me.Caption = "View"
Me.TextBox1.Value = Evaluate("FName").Value
With Me.ListBox1
.Clear
.List = Sheets("Tables").Range(Evaluate("FRange").Value).Value
End With
End Sub

Thank to macropod and DaveInIowa, I couldn't have done it without you two! And sorry I can't figure out how Dave in Iowa created the nice code box.

Dan

 

[SkipVought]

Hi,

Check out the Names Collection in the Workbook and each worksheet...

dim ws as worksheet, nm as name
for each nm in thisworkbook.names
  msgbox nm.name
next
for each ws in worksheets
  for each nm in ws.names
    msgbox ws.name & nm.name
  next
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]