iterate through textboxes on sheet

[christian_spaceman]

Hi there,

I run a model which takes a *lot* of parameters. The paramters are
entered into a large number of textboxes on an excel worksheet. THe
textboxes have names like
"textboxShapeParameter1","textboxShapeParameter2",
"textboxShapeParameter3" and so on.

I have set up my code so that all of these parameters get put into
various places in arrays. Is there a way I can iterate through the
textboxes to initialise them?

I want to write something like the following:

dim testArray (3) as double

for it = 0 to ubound(testArray)

testArray(it) = textboxShapeParameter & it.value

next it

I suspect this isn't possible, but is there any other way to go
through each of the textboxes on the sheet without *manually* writing
the code for each one?

Thanks in advance

Chris

 

[Peter T]

Hi Chris,

I take it your textboxes are ActiveX controls from the Control toolbox
toolbar. Try something like this -

With ActiveSheet.OLEObjects
for it = 0 to ubound(testArray)
testArray(it) = .item("textboxShapeParameter" & it).Value
next
end with

You will probably want to validate each textbox's .value is a string that
can be coerced to a double to match your array, also ubound(testarray)
textboxes exist on the sheet each having an anticipated name.

Regards,
Peter T

 

[christian_spaceman]

Hi Chris,

I take it your textboxes are ActiveX controls from the Control toolbox
toolbar. Try something like this -

With ActiveSheet.OLEObjects
for it = 0 to ubound(testArray)
testArray(it) = .item("textboxShapeParameter" & it).Value
next
end with

You will probably want to validate each textbox's .value is a string that
can be coerced to a double to match your array, also ubound(testarray)
textboxes exist on the sheet each having an anticipated name.

Regards,
Peter T
















- Show quoted text -

genius - thanks Peter, this looks to be exactly what I was after

Cheers,

Chris