MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

calculating 'names' of text boxes in a form

  Asked By: Bill    Date: Nov 01    Category: MS Office    Views: 1872

I am currently doing this by using a block of cells in a sheet and
it is easy to calculate the 20 cell addresses containing the data.
However, I'd like to have a dialog box (UserForm) for this.
I would like to have 20 text boxes in a UserForm and be able to
retrieve the contents(values) of all 20 into an array by using a
For...Next loop. This amounts to calculating variable names or more
correctly for VBA, calculating Object.property-names.

It would be "equivalent" to doing something like this:

UserForm Code...
Dim Array(19) As String

For x = 0 To 19
Array(x) = Me.("textbox" & Cstr(x)).Value

I'd like to avoid this:
Array(0) = Me.textbox0.Value
Array(1) = Me.textbox1.Value
Array(2) = Me.textbox2.Value
Array(19) = Me.textbox19.Value



4 Answers Found

Answer #1    Answered By: Abrianna Rossi     Answered On: Nov 01

It looks as though it should work like you said. What goes wrong?

Answer #2    Answered By: Damon Jones     Answered On: Nov 01

Got both responses.

Yikes! So the input routine is a general purpose string parser,
eh? I sort-of tried it, but only half-heartedly since I didn't think
it was valid. I don't think I actually tried the Parens "()"... just
did that for the posting. Better try again.
In my half-hearted attempt to see if such a construct was possible,
I did get an error and therefore saw that Array is invalid for a
name...must be a key word. I changed to Arr() for my 'investigation'.

However, I am not actually doing that. The Array thing was only for
demo here and as a test vehicle for trying the construct. I did
rename starting at 0 for special reasons. I am putting the TextBox
value into a command string to send out the serial port. The
addresses into which the values go in the external device start at 0,
so subscripting is easier in the loop.

Answer #3    Answered By: Varun Mehta     Answered On: Nov 01

Worked like a charm. Didn't even have to convert the number to a
string. VB does that nicely for us.

This also helps me understand the collection construct.

My functional test routine:

[ x dim'ed elsewhere]
Text Boxes named DTMFName0 ... DTMFName9

Private Sub UserForm_Click()
For x = 0 To 9
Debug.Print x; " "; Controls("DTMFName" & x)
End Sub

Answer #4    Answered By: Vidhya Iyer     Answered On: Nov 01

Array is not a legal name for a variable, I've used "Items". Also, you
don't need Me (it's implied because the code  is inside the user form's

You need the Controls collection, which can be indexed by control name:

Items(X) = Controls("TextBox" & X)

Lastly, Excel starts numbering text  boxes and other controls from 1, not
from zero, so there won't be a TextBox0 unless you specifically call one

Or you can use X as a simple subscript into the collection. In this case,
the subscripts do start at zero.

Items(X) = Controls(X)

Didn't find what you were looking for? Find more on calculating 'names' of text boxes in a form Or get search suggestion and latest updates.