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
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