Get textbox value from custom/dynamically generated form

I have a custom/dynamically generated form with X numbers of textboxes where X is number if elements from WorkSheet1. It also generates an Update button and a Cancel button.

The form is generating fine. For instance if the number of cells with value in WorkSheet1 is 10, then the form generates 10 textboxes and it puts cell value as the value of the textboxes.

Dim MilestoneSheet As Worksheet
Set MilestoneSheet = Worksheets("MIilestoneDates")
Set MilestoneDate = milestoneSheet.Range("A1")
        
While (MilestoneDate.Value <> "")
Dim textbox As Control
Set textbox = MilestoneForm.Controls.Add("Forms.TextBox.1")
Dim MilestoneDate As Date


'Set textbox properties
With textbox
.Height = 20
.Left = 2
.Top = 6
.Width = 90
'Set the name and value of the Textbox
.Name = "TextBox" & MilestoneDate.Row
.Value = MilestoneDate
End With

Set MilestoneDate = MilestoneDate.EntireColumn.Rows(MilestoneDate.Row + 1)
Wend

It starts with A1 of WorkSheet1 and goes down one cell a time and adds that cell value to the textbox value till it reaches an empty cell.

What I need to do now is on the form the value of any textbox can be changed by the user and once when they click the Update button it should then change only those cells in WorkSheet1 that had its textbox value changed by the user.

For instances if there are 10 cell with values and the form generated 10 textboxes, it should then change the value of cell 3 and 7 when the value in textbox 3 and 7 are changed by the user in the form.

Can I get some in solving this issue. Thanks in advance.

Good morning sonyidicula

You could set up an array and fill it with all values that are entered dynamically into your textboxes, then before you write the values back to the worksheet do a check to see which ones have changed.

However, given the amount of time it's going to take to write 10 values to a worksheet (ie, not much) why not just loop through all controls and write them all back to the sheet.

I know it's cheating, but you could spend ages coding the check, to gain milliseconds in terms of time.

HTH

DominicB

How do I loop through all controls and write them all back to the sheet. How do I get the value of those textboxes. I used the code below, but its not working.

Dim cCont As Control
Dim newDate As Range
    For Each cCont In MilestoneForm.Controls
          If TypeName(cCont) = "TextBox" Then

           'The line below is wrong
            newDate.Value = TypeName(cCont).Value
            
            'Set newDate array with values the textbox have after clicking Update
            Set newDate = newDate.EntireColumn.Rows(newDate.Row + 1)
            
        End If
    Next cCont
End Sub

Hi sonyidicula

Originally Posted by sonyidicula

How do I loop through all controls and write them all back to the sheet. How do I get the value of those textboxes.

Thanks for remembering to wrap your code.
I set up a userform with 10 TextBoxes (named (as in your example) TextBox1 to TextBox10), with one button (CommandButton1).

This code worked to loop through all textboxes and tranfer the values to the active worksheet :

Private Sub CommandButton1_Click()
For n = 1 To 10
Cells(n, 1).Value = UserForm1.Controls("Textbox" & n).Value
Next n
End Sub

Obviously, n may change each time, but you can trap that easy enough from the row number.

HTH

DominicB