-
Jun 14th, 2014, 08:36 AM
#1
Thread Starter
New Member
Newbie Question: Array traversal fails in VBA under MS Word
Hello
I have to preface this to say that I am a newbie at VBA, but not in programming.
I have a form which I fill out, but needs initialized values.
I have multiple text boxes deployed on the form, say: tb1, tb2, tb3, tb4
I would like to set their text values to show zero (0).
When I attempt to run the code below, I get an error ("Object required") on the For Each line. Won't vba assign an existing text box to an empty text box variable? Is there another way to do this and save myself a ton of repeated code?
Code:
Dim tb
tb = Array(tb1, tb2, tb3, tb4)
Dim t As TextBox
For Each t In tb
t.Value = "0"
Next t
The same error on the For Each linealso happens if I make the array an anonymous one:
Code:
Dim tb As TextBox
For Each tb In Array(tb1, tb2, tb3, tb4)
tb.Value = "0"
Next tb
Thanks. If there is any help you can give, it would be appreciated.
Paul
Last edited by paul.e.king; Jun 14th, 2014 at 04:07 PM.
-
Jun 14th, 2014, 08:43 AM
#2
Thread Starter
New Member
Re: Array traversal fails in VBA under MS Word
I had no edit button until I got onto Firefox. The typo has been fixed in the original post.
Last edited by paul.e.king; Jun 14th, 2014 at 04:06 PM.
-
Jun 14th, 2014, 04:21 PM
#3
Re: Newbie Question: Array traversal fails in VBA under MS Word
maybe this ?
Code:
Private Sub CommandButton1_Click()
Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeName(ctrl) = "TextBox" Then
ctrl.Text = "0"
End If
Next
End Sub
-
Jun 14th, 2014, 05:12 PM
#4
Re: Newbie Question: Array traversal fails in VBA under MS Word
OR
Code:
Dim tb
tb = Array("tb1", "tb2", "tb3", "tb4")
For Each t In tb
me.controls(t).Value = "0"
Next t
Last edited by westconn1; Jun 14th, 2014 at 05:20 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 14th, 2014, 05:26 PM
#5
Re: Newbie Question: Array traversal fails in VBA under MS Word
Originally Posted by paul.e.king
....When I attempt to run the code below, I get an error ("Object required") on the For Each line. Won't vba assign an existing text box to an empty text box variable? Is there another way to do this and save myself a ton of repeated code? ...
You made a logical mistake, but a mistake it is.
Originally Posted by From the Documentation
For Each...Next Statement
Repeats a group of statements for each element in an array or collection.
Syntax
For Each element In group
[statements]
[Exit For]
[statements]
Next [element]
The For...Each...Next statement syntax has these parts:
Part Description
element Required. Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable.
group Required. Name of an object collection or array (except an array of user-defined types).
statements Optional. One or more statements that are executed on each item in group.
Try:
-
Jun 14th, 2014, 07:42 PM
#6
Thread Starter
New Member
Re: Newbie Question: Array traversal fails in VBA under MS Word
IkkeEnGij, this is fine, except that I have other text boxes that do not need to be initialised that way. I have seen that on other sites.
Thanks anyway!
Last edited by paul.e.king; Jun 14th, 2014 at 07:45 PM.
-
Jun 14th, 2014, 07:45 PM
#7
Thread Starter
New Member
Re: Newbie Question: Array traversal fails in VBA under MS Word
This one looks better. I will try it. Thanks, Westconn1
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|