Set object through variable? 2

[littlewoman]

Using Excel 2003-vba, I have a userform with several textboxes for descriptions and named them tbxDescription1 to tbxDescription9

Now I need to loop through them in serveral occassions and tried to accomplish that by following code

dim obj as object
for cnt = 1 to 9
cnt$ = right(str(cnt),1)
objectname = "tbxDescription" + cnt$
set obj = objectname

next cnt

every time I get: object required on the 'set obj =' line

As a newby guessing it was in the syntax I've tried also:
set obj = tbxDescription & cnt$
set obj = [objectnaam]
set obj = objectnaam.value

I'm running out of clues and searching for 3 days now for an answer on a lot of forums but couldn't find an answer for such a simple thing. Everybody posting seem to be much more technical than where I am today.  

Therefor: plse can anyone tell me if there is a way to refer to form-objects without having to write code for every object individually?

 

[TonyJollans]

Hi littlewoman,

What are these descriptions? If they are the names of objects, what type of objects are they? And what do you want to do with them afterwards?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[johnwm]

You can loop through the OLEObjects:

For Each a In Me.OLEObjects
MsgBox a.Name
Next


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[littlewoman]

okay then the long version:

this is a userform used to automate billing
so there is a lot of stuff on it, some things are single as for instance the adress or the billingnumber but
in every bill there are also multiple lines, with descriptions, unitamounts, unitprices, calculated values etc

to keep an overview while making the bill I've kept all these multiple-lines on the form instead of using a simple "1 line" form
and used names as tbxDescription1 till tbxDescription9, tbxUnit1 till tbxUnit9 etc

since all textboxes are strings after the input was made events are triggered for instance to make a value from the string, to change the format, to check the length of the input or to calculate the amount, or even in some instances to delete the input and/or place default entries depending on client-choice. and this all works well so long I use events specifically on one object and that is a lot of code.

yet all these events are the same for every of the descriptions, amounts, units etc
therefor it would make it a simpler and shorter code to loop through these textboxobjects as if they were an array
so if they were tbxDescription(9), tbxUnits(9) etc. by 'pasting' the number to the name and set that name as the object and make the handling in a 'global' procedure.
but that doesn't work, so know I'm clueless

the solution with johnwm gives loops thrue all objects on the form but I need on different occasions just to target one set of objects (but thnx for thinking for me ;-) )

In short I need to catch the name of the object in a global object which in turn can be used in global procedures.
hmpf hope I'm making sense :-s,

 

[TonyJollans]

Hi littlewoman,

I haven't studied that in detail but, if I get the gist of it, you want to reference controls on your userform using the text name. If so you should be able to use something like:

Set obj = Me.Controls("tbxDescription" & cnt)

If you are outside the form you'll have to use an explicit reference to the form instead of Me.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[rmikesmith]

In order to loop through your textboxes you need to reference the Userform's Controls collection. Here is an example, where Userform represents the actual name of your userform.

Sub LoopThroughTextboxes()
Dim Ctl as Control

   For Each Ctl in Userform.Controls
     If TypeName(Ctl) = "TextBox" Then
       [code]
     End If
   next Ctl


End Sub

If you need to further differentiate between the 9 textboxes you indicated and other textboxes on the form, you can inspect the particular textbox name for the 'Description' string or set the Tag property of each of those textboxes to the same string value (e.g. Description) and test for that. Example:

   For Each Ctl in Userform.Controls
     If TypeName(Ctl) = "TextBox" Then
       If Ctl.Tag = "Description" Then       
         [code]
       End If 
     End If
   next Ctl

 

[littlewoman]

thnx guys
one quick try with both instances and they shoot through the code as a new type of concorde. it works great.
controls and typename, so simple yet so hard to figure out.
hahahaha

oh well, the neverending story can continue

love you both

 

[xlbo]

Stars for useful code to the both of you.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question