User Forms Basics - Text Boxes to Named Range

**T.J. Lanza** · 09-24-2009, 02:39 PM

So, first time with user form user (redundant much?). I got some help yesterday over in this thread: User Form Basics - Populating Text Boxes. A few tweaks later and its reading the data just fine. Some of the specific form objects and range names have changed, but it's the same logic.

Anyway, this isn't just for display. I need to export the data back to the spreadsheet. So, I plugged this line into the event handler for the "Save & Exit" button on the sample form in the other thread:

Range("intTier1Size").Value = frmRaceLimits.txtTier1Size

Excel responds with...

Run-time error '1004':
Method 'Range' of object '_Global' failed

If I change the code to read:

Range("H5").Value = frmRaceLimits.txtTier1Size

It dumps the value in the cell H5 on the current worksheet.

As before, the named ranges are not cell references. Some are static values, some are formulas. They don't exist anywhere except the Name Manager. To make it more confusing, I've used Range with similar named ranges in quite a few other procedures, and it works just fine. Only here, where it involves a user form, do I have a problem.

Any thoughts?

**royUK** · 09-24-2009, 02:52 PM

You aren't using ranges. Try this

ThisWorkbook.Names.Add Name:="intTier1Size", RefersTo:="=" & _
    Me.txtTier1Size.Value, Visible:=False

This should over rite he name value with the textbox value

**T.J. Lanza** · 09-24-2009, 03:35 PM

Originally Posted by royUK

You aren't using ranges. Try this

ThisWorkbook.Names.Add Name:="intTier1Size", RefersTo:="=" & _
    Me.txtTier1Size.Value, Visible:=False

This should over rite he name value with the textbox value

Absolutely brilliant. Thank you very much.

Strange, that in some ways you can use them like they are a range, and in other ways you can't. I figured there was some way to manipulate the names directly, I just wasn't sure the syntax for it. Gotta remember to look in the "Collections" part of the VBA language reference. I'd seen mention of the Names collection, but I hadn't put two and two together.