runtime texbox creation in VBA and access to textbox data

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

Hi folks,
Part A) I was trying out certain application in Excel VBA where i wanted
to create textbox in a form run time depending on the user input number.
Part B) Once i had the textbox created i wanted to later add the
information user entered in the textbox to a array for string datatype.

Well some how i could get part A, but i unable to get Part B. I created
a control object OtxtCri for textbox and the textbox i created i named
the "txtCri"&i

Set OTxtCri = frmCriteriaDataEntry.Controls.Add("Forms.TextBox.1",
"txtCri" & i, True)

but now i am uable to code the software to get the user entered data
from these textbox to the array as i dont knwo how to identify the newly
created textbox in the form.

please i appreaciate a lot for the help on this.

thanks
robin

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Hi Robin

This worked for me

Dim OTxtCri As Object

Private Sub CommandButton1_Click()
MsgBox OTxtCri
End Sub

Private Sub UserForm_Click()
i = 1
Set OTxtCri = frmCriteriaDataEntry.Controls.Add _
("Forms.TextBox.1", "txtCri" & i, True)
End Sub


If you need to access the new Control via a Standard sub you will need
to Dimmension it as Public at the top of any Standard Module, eg

Public OTxtCri As Object

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
When you create the textboxes, put *them* into a dynamic array of type
MSForms.TextBox. When you need to refer to them later (e.g. to put
their values into an array), use your array of textboxes.
 
Just a thought:

Instead of adding the textbox control at run time, how about having that textbox
in your form--but have it invisible. Just show it if you need it.

And it might make things easier in the later steps.
 
Back
Top