Silver
07-06-2016, 12:14 PM
Hi,
I have a userform with 10 textboxes.
I'm using below code to update data on sheet2 from row 2 onwards.
The code is assigned to button Approve
Const F = "dd-mm-yy hh:mm"
Private Sub CommandButton1_Click()
N% = Val(TextBox11.Text)
If N < 1 Then TextBox10.SetFocus: Beep: Exit Sub
V = [{8,11,12,13,16,19,20,21,22,23}]
r& = Sheet2.Cells(Sheet2.Rows.Count, V(1)).End(xlUp)(2).Row
For c% = 1 To UBound(V)
Sheet2.Cells(r, V(c)).Resize(N).Value = Me.Controls("TextBox" & c).Text
Me.Controls("TextBox" & c).Text = ""
Next
UserForm_Initialize
End Sub
Private Sub TextBox2_Enter()
TextBox2.Text = Format$(Now, F)
End Sub
Private Sub UserForm_Initialize()
TextBox3.Text = Format$(Now, F)
TextBox11.Text = "1"
End Sub
Private Sub TextBox4_Enter()
TextBox4.Text = Format$(Now, F)
End Sub
So, when the Approve button is clicked below mentioned columns on sheet2 gets updated with data from row 2 onwards.
V = [{8,11,12,13,16,19,20,21,22,23}]
I have created a second userform which is assigned to button Reject.
I'm using the same above code but the columns are different -
V = [{8,15,16,19,20,21,22,23}]
My problem is -
When the Reject button is clicked I want the macro to update data in the same sheet2..... but from row 20 onwards
I have a userform with 10 textboxes.
I'm using below code to update data on sheet2 from row 2 onwards.
The code is assigned to button Approve
Const F = "dd-mm-yy hh:mm"
Private Sub CommandButton1_Click()
N% = Val(TextBox11.Text)
If N < 1 Then TextBox10.SetFocus: Beep: Exit Sub
V = [{8,11,12,13,16,19,20,21,22,23}]
r& = Sheet2.Cells(Sheet2.Rows.Count, V(1)).End(xlUp)(2).Row
For c% = 1 To UBound(V)
Sheet2.Cells(r, V(c)).Resize(N).Value = Me.Controls("TextBox" & c).Text
Me.Controls("TextBox" & c).Text = ""
Next
UserForm_Initialize
End Sub
Private Sub TextBox2_Enter()
TextBox2.Text = Format$(Now, F)
End Sub
Private Sub UserForm_Initialize()
TextBox3.Text = Format$(Now, F)
TextBox11.Text = "1"
End Sub
Private Sub TextBox4_Enter()
TextBox4.Text = Format$(Now, F)
End Sub
So, when the Approve button is clicked below mentioned columns on sheet2 gets updated with data from row 2 onwards.
V = [{8,11,12,13,16,19,20,21,22,23}]
I have created a second userform which is assigned to button Reject.
I'm using the same above code but the columns are different -
V = [{8,15,16,19,20,21,22,23}]
My problem is -
When the Reject button is clicked I want the macro to update data in the same sheet2..... but from row 20 onwards