MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

many textboxes

  Asked By: Jamil    Date: Sep 08    Category: MS Office    Views: 635

I have a form with textbox1 through textbox20. I would like to set
properties for each -- eg textbox1.value = "". I could write 20
statements (textbox2.value = ""; textbox3.value = ""), but it would be
great if I could do something like

For i = 1 to 20
textboxi.value = ""

but of course this wouldn't work. Is there something like this that
would work?



5 Answers Found

Answer #1    Answered By: Hugo Williams     Answered On: Sep 08

There is no collection of TextBoxes, but there is one of all the
controls on a form  and using its typename you could do something like:

For Each ctl In UserForm1.Controls
If TypeName(ctl) = "TextBox" Then ctl.Text = ""

Answer #2    Answered By: Amelia Schmidt     Answered On: Sep 08

No, what you are looking for here is called self modifying code. It
is possible in some computer languages but not VBA. It has something
to do with VBA being a just-in-time compiled language that compiles at
the sub/function level. If it was a JIT compile at the statement
level it could do what you are looking for.

But, there is a way to work  with your textbox properties in a loop:

Private Sub CommandButton1_Click()
Dim ctrl As Variant
Dim i1 As Integer
Dim a1(20) As String
i1 = 1
For Each ctrl In UserForm1.Controls
If InStr(ctrl.Name, "TextBox") > 0 Then
a1(i1) = ctrl.Value
i1 = i1 + 1
End If
End Sub

This code snippit uses a loop to store the values of your userfrom
into an array.

Answer #3    Answered By: Kristen Chavez     Answered On: Sep 08

Actualy with VBA you can create, modify and delete VBA code.
I have used it in a program to convert old data to new data where I had to deal
with a variable number of characteristics which had to be added to series of old
data to get them in a new database.
I don't have it at home but I can send you an example next week.
You will need to check if under Tools > References the Object Library is tagged
otherwise it want work.

Answer #4    Answered By: Jennie Harris     Answered On: Sep 08

Yes, I know... There is a great book that I used to get started with VBA and
Excel a bunch of years ago. It is called Excel 2000 Power Programming with VBA
by Walkenbach... Chapter 27 has a section called using VBA to write  VBA code.
It shows how to create and execute entire functions as text and then execute

But... the original post had a different type of self modifying code... the
poster was trying to create a variable name by concatenating a constant text
string to a integer that changed within a loop. I don't believe that this
kind of self modifying code is possible in VB or VBA but I would love to be
proven wrong. I believe that the only modern Microsoft Language that has this
capability is VFP. In this language it is called Macro Substitution.

BTW... VFP is a great way to automate MS Excel applications.... it lets you
create exe files that operate directly on the Excel Object using a language and
a program construction that is very similar to VBA. As a bonus, your
applications can be distributed without triggering the security warning that
comes with embedded macros.

Answer #5    Answered By: Melissa King     Answered On: Sep 08

I feel like Titans are fighting all around me... I appreciate all the
thought you guys are putting into my problem, and I am struggling to
follow the conversation. I am very new at this.

Actually, I have a later Walkenbach book, and I will see what I can
find in it. Thanks again.

Didn't find what you were looking for? Find more on many textboxes Or get search suggestion and latest updates.