Userform code assistance for updating data multiple times and for time and date

[Bimmy]

Hello,

I have setup a userform with 10 labels, 10 textboxes and 1 command button.

Labels are as follows -

Agents
Raised Time (linked to A6)
Initiated Time
End Time
No of Pax
F No
Agency Name
Funds
Approver
Yes No

Below is the code used to update data in specific columns on sheet1

Private Sub CommandButton1_Click()

erow = Sheet1.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
Cells(erow, 8) = TextBox1.Text
erow = Sheet1.Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Row
Cells(erow, 11) = TextBox2.Text
erow = Sheet1.Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).Row
Cells(erow, 12) = TextBox3.Text
erow = Sheet1.Cells(Rows.Count, 13).End(xlUp).Offset(1, 0).Row
Cells(erow, 13) = TextBox4.Text
erow = Sheet1.Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).Row
Cells(erow, 19) = TextBox5.Text
erow = Sheet1.Cells(Rows.Count, 20).End(xlUp).Offset(1, 0).Row
Cells(erow, 20) = TextBox6.Text
erow = Sheet1.Cells(Rows.Count, 21).End(xlUp).Offset(1, 0).Row
Cells(erow, 21) = TextBox7.Text
erow = Sheet1.Cells(Rows.Count, 22).End(xlUp).Offset(1, 0).Row
Cells(erow, 22) = TextBox8.Text
erow = Sheet1.Cells(Rows.Count, 23).End(xlUp).Offset(1, 0).Row
Cells(erow, 23) = TextBox9.Text

'clear the data

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""

End Sub

Requirement -

I -

Macro should update all the datas in their respective columns depending on numbers mentioned in textbox5 (label is No of Pax)

Ex :-

If number entered is 3, then macro should paste all the data thrice in the respective columns.
If number entered is 5, then macro should paste all the data 5 times in the respective columns

II -

Don't know how to get below piece of code to work

TextBox3.Value = Now
TextBox3.Value = Format(TextBox1.Value, "dd-mm-yy hh:mm")
End Sub

Above code will be used to update textbox3 (label is Initiated Time) when the userform is activated, and, when clicked on command button the respective column will reflect the date and time.

III - (If Possible)

Looking for a code that will update date and time in textbox4 (label is End Time) when clicked in the textbox. The format is - dd-mm-yy hh:mm.
(Option should be provided to change the format)

Requesting to provide complete working code.

Have attached sample sheet.

 

[Marc L]

Look at events of UserForm and its elements in VBE !

And you made an error for the TextBox # for I …

Const F = "dd-mm-yy hh:mm"

Private Sub CommandButton1_Click()
    N% = Val(TextBox10.Text)
    If N < 1 Then TextBox10.SetFocus: Beep: Exit Sub
     V = [{8,11,12,13,19,20,21,22,23}]
    R& = Sheet1.Cells(Sheet1.Rows.Count, V(1)).End(xlUp)(2).Row
For C% = 1 To UBound(V)
    Sheet1.Cells(R, V(C)).Resize(N).Value = Me.Controls("TextBox" & C).Text
    Me.Controls("TextBox" & C).Text = ""
Next
    UserForm_Initialize
End Sub

Private Sub TextBox4_Enter()
    TextBox4.Text = Format$(Now, F)
End Sub

Private Sub UserForm_Initialize()
     TextBox3.Text = Format$(Now, F)
    TextBox10.Text = "1"
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Bimmy]

Marc L ... U R A Genius

Your code is SIMPLY PERFECT.

Just need 1 slight addition to the above code.

Hope I can get my explanation correct -

Code should look for next empty row in column K and then keep on adding data

 

[Marc L]

For column K instead of H replace V(1) by V(2) in the calculation line of R
(or directly with the column number) …
​

 

[Bimmy]

Thank you for your Time and Patience....