User Form Basics - Populating Text Boxes

**T.J. Lanza** · 09-23-2009, 12:49 PM

If anybody has a link to a good tutorial on user forms, it would be greatly appreciated. Failing that...

I am trying to create a user form to edit some named ranges. The VBA user form designer is basically the same as Borland Delphi, so building the form itself was easy. What I can't figure out is how to populate the user form with the data from the spreadsheet.

I have a number of text boxes on the form with names like txtTier1Slots, txtTier1Ceiling, txtTier1Floor, etc up to 4.

I have a defined name that corresponds with each item, Tier1Slots, Tier1Ceiling, Tier1Floor, and so on. I'm using defined names because they're referenced in Conditional Formatting on one of the worksheets in my project. They don't exist as actual cells, just name references.

I want to get the form populated with those values. I've tried about twenty variations of the following code, using ThisWorkbook, Workbooks, Cells, Range, Name, and just about every cell reference method I can think of. I've so many different error messages, my head is spinning.

Please Login or Register  to view this content.

Any thoughts?

**davegugg** · 09-23-2009, 01:00 PM

Please Login or Register  to view this content.

**royUK** · 09-23-2009, 01:01 PM

The names mus refer o a range or cell. Can you attach a small example workbook?

**T.J. Lanza** · 09-23-2009, 01:21 PM

Originally Posted by royUK

The names mus refer o a range or cell. Can you attach a small example workbook?

The names are defined directly in the Name Manager. The intent of this user form is to allow them to be changed by people who don't know how Name Manager works and shouldn't be mucking around with it.

Anyway, I've attached a stripped down workbook. It's got the form, the defined names, and a single worksheet with a button to activate the form. None of the controls on the form do anything.

I'm trying to find the right syntax to get the text boxes populated. I don't know the combination of objects, methods, and properties to get anything into a user form. Microsoft's own VBA language reference is less than helpful. My feeling is that it's some sort of scope-related issue...

**royUK** · 09-23-2009, 01:38 PM

The first name you mentioned does not exist in the workbook, see list that I have created.

I have put some code in to get you started

**T.J. Lanza** · 09-23-2009, 02:56 PM

Originally Posted by royUK

The first name you mentioned does not exist in the workbook, see list that I have created.

I have put some code in to get you started

Woops! The form objects used "txtTier_Slots", but the Defined Names used "intTier_Size". Sorry 'bout that. I corrected them on my end, and it works like a charm.

Now, another question... What does that square-bracket notation around the Defined Names mean? I don't think I've ever seen that before.