Change value of Named Range in VB

Posted by Melanie Swarner on October 26, 2001 12:37 PM

How do I change the value of a named range in VB without selecting the cell? Because of future plans of the project, I need to use a named range and what it refers to could change each time program is run.

I have a list box with multiple selections. As I iterate through it, it changes the value of a cell if an item is selected. However, if I select the cell first, it erases all of my subsequent selections and I only get the results of the first selection.

It seems pretty simple. There's got to be a way to do it, I just don't know it.

Thanks!

Posted by Barrie Davidson on October 26, 2001 12:42 PM

Use something like this

Range("test").Value = 5

where your named range is named "test" and you want that range's value to be 5 (in each cell of the range).

Does this help you?

BarrieBarrie Davidson

Posted by Melanie Swarner on October 27, 2001 1:08 PM

RefersToRange vs Range's value property....

Barry,

This worked fine. This was my original idea, but it didn't seem to work. I was try to use RefersToRange property of "Names" because I'd had problems with a named range before. When do I have to use RefersToRange to for a name and when don't I?

Thanks for your help! I'm having a lot of fun learning this language, but it's hard when you run into a stumbling block. This board has helped me a lot.

Melanie

Posted by Barrie Davidson on October 29, 2001 8:09 AM

Re: RefersToRange vs Range's value property....

Melanie, from VBA's Help:

"Returns the Range object referred to by a Name object. Read-only."

This could be useful, for example, if you wanted to get a named range's address in VBA. Something like:

Sub GetAddress()
Dim RangeAddress As String
RangeAddress = Names("Melanie").RefersTo
End Sub

Regards,
Barrie

Barrie Davidson