iterate through textboxes on sheet
 

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","textboxShapeParamete r2",
"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

iterate through textboxes on sheet
 

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" wrote in message
ups.com...
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","textboxShapeParamete r2",
"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

iterate through textboxes on sheet
 

On 8 Oct, 22:30, "Peter T" <peter_t@discussions wrote:
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" wrote in message

ups.com...



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","textboxShapeParamete r2",
"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- Hide quoted text -

- Show quoted text -

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

Cheers,

Chris