PDA

View Full Version : [SOLVED] Calling Textboxes created in runtime with a form how to reference in a loop



CABBA
12-12-2017, 12:55 AM
I am creating a userform where by which several text boxes (up to 60) are created in runtime based on the number of periods that the user enters.

i used this code to create the text boxes:
Set txtCntrl = .Add("Forms.TextBox.1", "txtDemand" & txtT, True) 'in this case txtT is incrementing up to the number of periods
and so in this case i end up with several textboxes (txtDemand1, txtDemand2, .....)

when the user clicks OK i want to take all the values from the form and put them into a sheet.
i have found that the following line works fine (in this example taking the value in txtDemand1 and putting it into cell "A46"):
Range("A46") = frmAggPlan!txtdemand1.Value

The problem is that i want to automate this some how using a variable and concatenating it. but i dont it seems that the i can not do this in the same way that i created the boxes. For example i would like to set up a looop on user press ok like this.

Dim i As Integer
For i = 1 to txtT
Range ("A46").offset(i-1) = frmAggPlan!txtDemand & i 'i'm not able to concatenate like this here, so looking for a way to have all the runtime created text boxes to output to my sheet

Next i

Any help would be welcome.

snb
12-12-2017, 01:36 AM
first of all: do not complicate your code by creating & formatting controls at runtime
Create the 60 textboxes in design mode and make them invisible.
Put all the code that pertains to the userform in de codemodule of this userform !

At runtime you can use, dependent of anything (but also some user input), this code to make the textboxes visible


Sub M_snb()
for j=1 to val(inputbox("number 1 to 60"))
me("TextBox" & j).visible=true
next
End Sub

To write the result into a worksheet:


Sub M_snb()
redim sn(59,0)

for j=0 to 59
if me("Textbox" & j+1)=visible then sn(j,0)=me("Textbox" & j+1)
next

thisworkbook.sheets(1).cells(1,1).resize(60)=sn
End Sub

Reduce reading/writing from/into a worksheet to the minimum for speed's sake.

p45cal
12-12-2017, 10:23 AM
i want to automate this some how using a variable and concatenating it. but i dont it seems that the i can not do this in the same way that i created the boxes.Something along the lines of:

For i = 1 To 5
Range("A46").Offset(i - 1) = Me.Controls("txtDemand" & i)
Next i?

CABBA
12-12-2017, 09:26 PM
Something along the lines of:

For i = 1 To 5
Range("A46").Offset(i - 1) = Me.Controls("txtDemand" & i)
Next i?

Thanks this worked for me.

snb
12-13-2017, 02:25 AM
But it doesn't comply to:


Reduce reading/writing from/into a worksheet to the minimum for speed's sake.

So why do you ignore http://www.vbaexpress.com/forum/showthread.php?61548-Calling-Textboxes-created-in-runtime-with-a-form-how-to-reference-in-a-loop&p=374083&viewfull=1#post374083 ?

p45cal
12-13-2017, 04:18 AM
Maybe because it didn't work?
If Me("Textbox" & j + 1).Visible Then…
might help.

snb
12-13-2017, 04:23 AM
Yes that would be very helpful.. ;)