Assign click functionality to dynamically created textboxes?

[Ouka]

Hi all,

I have a userform that has textboxes that are created and populated dynamically at runtime.

i.e.: based on the number of cells found to be populated with data on a spreadsheet, I create a userform with textboxes with values cooresponding to those cells

I want to add the ability to double click on these textboxes and have the value changed to the current date being displayed by a calander control that is also built into my userform.

But since don't know how many textboxes are going to be created on a given execution of the code, I can't just make the subs that would normally control click actions.

Say there were 2 textboxes being created, I'd then need:

Sub Textbox1_dblclick(ByVal Cancel as MSForms.ReturnBoolean)

Textbox1 = calendar1

End sub
-----------------------------------
Sub Textbox2_dblclick(ByVal Cancel as MSForms.ReturnBoolean)

Textbox2 = calendar1

End sub

But if I have 5 textboxes, I'd need 5 subs. If 10, then 10. etc.

Is there any way to include a variable as part of a sub's name?

Like say:

Sub Controls("Textbox" & N)_dblClick(ByVal Cancel As MSForms.ReturnBoolean)

with the value for N being passed by another procedure during the intial building of the userform?

[instruite]

One way of adding script to form at run time
You will have to modify the code for your application

Set fAuto_Form = ThisWorkbook.VBProject.VBComponents.Add(3)
fAuto_Form.properties("Width") = 800
With fAuto_Form.CodeModule
X = .CountOfLines
.InsertLines X + 1, "Sub CommandButton1_Click()"
.InsertLines X + 2, " GETOPTION_RET_VAL=False"
.InsertLines X + 3, " Unload Me"
.InsertLines X + 4, "End Sub"

.InsertLines X + 5, "Sub CommandButton2_Click()"
.InsertLines X + 6, " Dim ctl"
.InsertLines X + 7, " GETOPTION_RET_VAL = False"
.InsertLines X + 8, " For Each ctl In Me.Controls"
.InsertLines X + 9, " If ctl.Tag <> """" Then If ctl Then GETOPTION_RET_VAL = ctl.Tag"
.InsertLines X + 10, " "
.InsertLines X + 11, " Next ctl"
.InsertLines X + 12, " MsgBox battery.value"
.InsertLines X + 13, " dvalue1 = battery.value"
.InsertLines X + 14, " stcomponent = component.caption"
.InsertLines X + 15, " Unload Me"
.InsertLines X + 16, "End Sub"
End With

[Ouka]

Thanks for the reply.

I tried using that code modified, but it didn't seem to work for me. My fault I'm sure...

Is there any way to wite If/then code to catch clicks on objects? Like:

If Controls("Textbox" & N)_DblClick Then
Controls("Textbox" & N) = Calendar1
End if

It would make what I'm trying to do a lot easier...

[Ouka]

Well, found another way around the problem through trial and error in case anyone has a simalr problem and a search turns up this thread:

Used:

Please Login or Register  to view this content.

When the function is called, a runtime-genreated form is generated. The code of that useform is generated by the code = code "" statements. To create dynamic code for the useform, I put the For loop inside the codeblock for the useform that looped for each instance of a textbox.