Assign click functionality to dynamically created textboxes?

[Ouka]

Hi all,

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

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

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

But since don't know how many textboxes are going to be created on
given execution of the code, I can't just make the subs that woul
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 A
MSForms.ReturnBoolean)

with the value for N being passed by another procedure during th
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 The
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 Wit

 

[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 i
case anyone has a simalr problem and a search turns up this thread:

Used:


Code
-------------------
Function GetTextValue()

Dim Code as String

'insert code that controls the building of the userform. In my case, 2 command buttons and a dynamic number of textboxes

Code = ""
Dim tlRow As Long
Dim M As Long
Dim Part1 As String
Dim Part2 As String
Dim Part3 As String
With Worksheets("hidden1")
tlRow = .Cells(Rows.Count, 26).End(xlUp).Row 'where i get the values for the textboxes

' Define 3 string variables and create code blocks that will coorespond to each dynamically created textbox

For M = 1 To tlRow

Part1 = "Sub Textbox" & M & "_dblclick(ByVal Cancel as MSForms.ReturnBoolean)"
Part2 = " Textbox" & M & " = calendar1"
Part3 = "End Sub"

Code = Code & Part1 & vbCrLf
Code = Code & Part2 & vbCrLf
Code = Code & Part3 & vbCrLf

Next M

End With

' Code for CommandButton1
Code = Code & "Sub CommandButton1_Click()" & vbCrLf
Code = Code & "Unload Me" & vbCrLf
Code = Code & "Call Canceled" & vbCrLf
Code = Code & "End Sub" & vbCrLf

'Code for CommandButton2
Code = Code & "Sub CommandButton2_Click()" & vbCrLf
Code = Code & "Unload Me" & vbCrLf
Code = Code & "Call OK" & vbCrLf
Code = Code & "End Sub" & vbCrLf

End Function


-------------------


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 th
codeblock for the useform that looped for each instance of a textbox