Excel : VBA code : Transfeer Userform Data to Excel sheet

[RAKESH230583]

Dear Friends,

I have one excel Userform Where in currently i have 11 TextBox & due to some additional requirement i need to add some 16 additional TextBox to it. So in total there will be 27 TextBox.

I am looking out for some short and simple VBA code - That avoid use of repetative line of quote highlighted below in current VBA CODE in RED, which does transfer data from my userform to table "Data_Dump" in sheet1 ("Data")

Currently all data filled in userform are getting saved in sheet1 "Data" - Where i had created table with table name as "Data_Dump"

Below VBA code was used to transfer data from userform to excel sheet1"Data", under table name "Data_Dump"

Quote

Private Sub Save_Details_Click()
Dim rng As Range
Set rng = Data.ListObjects("Data_Dump").Range
Dim lastrow As Long
lastrow = rng.Find(what:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

rng.Parent.Cells(lastrow + 1, 1).Value = Textbox1.Value
rng.Parent.Cells(lastrow + 1, 2).Value = Textbox2.Value
rng.Parent.Cells(lastrow + 1, 3).Value = Textbox3.Value
rng.Parent.Cells(lastrow + 1, 4).Value = Textbox4.Value
rng.Parent.Cells(lastrow + 1, 5).Value = Textbox5.Value
rng.Parent.Cells(lastrow + 1, 6).Value = Textbox6.Value
rng.Parent.Cells(lastrow + 1, 7).Value = Textbox7.Value
rng.Parent.Cells(lastrow + 1, 8).Value = Textbox8.Value
rng.Parent.Cells(lastrow + 1, 9).Value = Textbox9.Value
rng.Parent.Cells(lastrow + 1, 10).Value = Textbox10.Value
rng.Parent.Cells(lastrow + 1, 11).Value = Textbox11.Value
Userform1.Hide

End Sub

Unquote

 

[Fluff]

How about

Dim i As Long
For i = 1 To 27
   rng.Parent.Cells(lastrow + 1, i).Value = Me.Controls("TextBox" & i).Value
Next i

 

[mikerickson]

One system would be to put the column offset in the .Tag properties for the textboxes at design time. That avoids having to maintain a correspondence between textbox name and eventual cell location.
The .Tag propertys can be set at design time in the Properties window and the code in the UF could look like

Dim oneControl as MSForms.Control

For each oneControl in Me.Controls
    With oneControl
        If (TypeName(oneControl) = "TextBox") And (.Tag <> vbNullString) Then
            rng.Parent.Cells(lastrow + 1, Val(.Tag)).Value = .Text
        End If
    End With
Next oneControl