Help with userform to edit existing rows.

[Seacowdaz]

Hello. i hope somebody is able to help me, my knowledge of VBA isn't great to begin with and i havnt been able to find the answer online (pr at least one that i can follow) .

i have a spreadsheet with several userforms. one to add, remove and edit existing users on a sheet. the add and delete ones i have been able to do but i need some help with the VBA for the form to edit. so far i have the below which populates a combo box which you can then use to select which person to edit. The code i have pulls throw the data from the relevant columnsinto the userform, but it won't allow me to edit the data on the form and have that written back to the sheet.

Private Sub ComboBox2_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("Daily Report").Range("AL" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Sheets("Daily Report").Cells(i, "D").Value = (Me.ComboBox2) Or _
Sheets("Daily Report").Cells(i, "D").Value = Val(Me.ComboBox2) Then
Me.TextBox1 = Sheets("Daily Report").Cells(i, "D").Value
Me.TextBox2 = Sheets("Daily Report").Cells(i, "AK").Value
Me.TextBox3 = Sheets("Daily Report").Cells(i, "B").Value
Me.TextBox4 = Sheets("Daily Report").Cells(i, "A").Value
Me.TextBox5 = Sheets("Daily Report").Cells(i, "C").Value
Me.ComboBox1 = Sheets("Daily Report").Cells(i, "AM").Value
Me.TextBox6 = Sheets("Daily Report").Cells(i, "AE").Value
Me.TextBox7 = Sheets("Daily Report").Cells(i, "AF").Value
End If
Next

End Sub

Would someone be able to help me with what i need to add / change to write it back to the sheet?, and if possible help me format so that TextBox 6 and 7 show the time in the right format rather than convert it to text please? Or have it so that if a textbox is blank it doesnt overwrite the existing value on the row i have linked a sample sheet with some dummy data

Thank you in advance for any help

Sample Sheet

 

[Logit]

Here is a sample of code from another project. Study it as well as the workbook itself which you can download for review :

Private Sub cmdupdate_Click()
If Me.cmbslno.Value = "" Then
MsgBox "SL No Can Not be Blank!!!", vbExclamation, "SL No"
Exit Sub
End If
SLNo = Me.cmbslno.Value
Sheets("data").Select
Dim rowselect As Double
Dim msg As String
Dim ans As String
rowselect = Me.cmbslno.Value
rowselect = rowselect + 1
Rows(rowselect).Select
Cells(rowselect, 2) = Me.txtregion.Value
Cells(rowselect, 3) = Me.txtmarket.Value
Cells(rowselect, 4) = Me.txtbNum.Value
Cells(rowselect, 5) = Me.txtcnum.Value
Cells(rowselect, 6) = Me.txtcity.Value
Cells(rowselect, 7) = Me.txtState.Value
Cells(rowselect, 8) = Me.txtpnum.Value
Cells(rowselect, 9) = Me.txtSdate.Value
Cells(rowselect, 10) = Me.txtInum.Value
Cells(rowselect, 11) = Me.txtIdate.Value
Cells(rowselect, 12) = Me.txtSamount.Value
Cells(rowselect, 13) = Me.txtsp.Value
Cells(rowselect, 14) = Me.txtSR.Value
rowselect = rowselect - 1
msg = "Sl No " & rowselect & "  Successfully Updated...Continue?"
Unload Me
ans = MsgBox(msg, vbYesNo, "Update")
If ans = vbYes Then
UserForm1.Show
Else
Sheets("Data").Select
End If
End Sub

Download : doctest.xlsm

The primary change in your code which will 'write' the updated data back to the sheet is this portion :

Me.TextBox1 = Sheets("Daily Report").Cells(i, "D").Value
Me.TextBox2 = Sheets("Daily Report").Cells(i, "AK").Value
Me.TextBox3 = Sheets("Daily Report").Cells(i, "B").Value
Me.TextBox4 = Sheets("Daily Report").Cells(i, "A").Value
Me.TextBox5 = Sheets("Daily Report").Cells(i, "C").Value
Me.ComboBox1 = Sheets("Daily Report").Cells(i, "AM").Value
Me.TextBox6 = Sheets("Daily Report").Cells(i, "AE").Value
Me.TextBox7 = Sheets("Daily Report").Cells(i, "AF").Value

except you will need to reverse it ...

Sheets("Daily Report").Cells(i, "D").Value = Me.TextBox1

Sheets("Daily Report").Cells(i, "AK").Value = Me.TextBox2

etc

etc

There is more coding required to complete the entire process ... but based on what you've already written, you should be able to
reach your goal. Give it a shot and seek assistance when needed.

 

[Seacowdaz]

Hi, thank you for that. i may have been too optimistic but i tried to copy the VBA you provided in the worksheet. this is what i have:

Private Sub CommandButton1_Click()

Agent = Me.ComboBox2.Value
Sheets("Daily Report").Select
Dim rowselect As Double
Dim msg As String
Dim ans As String
rowselect = Me.ComboBox2.Value
rowselect = rowselect + 1
Rows(rowselect).Select
Cells(rowselect, 4) = Me.TextBox1.Value
Cells(rowselect, 37) = Me.TextBox2.Value
Cells(rowselect, 2) = Me.TextBox3.Value
Cells(rowselect, 1) = Me.TextBox4.Value
Cells(rowselect, 3) = Me.TextBox5.Value
Cells(rowselect, 39) = Me.ComboBox1.Value
Cells(rowselect, 31) = Me.TextBox6.Value
Cells(rowselect, 32) = Me.TextBox7.Value
rowselect = rowselect - 1
msg = "" & rowselect & "  Successfully Updated...Continue?"
Unload Me
ans = MsgBox(msg, vbYesNo, "Update")
If ans = vbYes Then
UserForm1.Show
Else
Sheets("Home").Select
End If
End Sub

However when i try to 'write' this back i get an error 'Run Time Error 13 Type Mismatch' on the line:

'rowselect = Me.ComboBox2.Value'

can you help me understand why i get that error i presume it's because i'm doing something stupid

 

[Logit]

Your combo box is not named : ComboBox2

It is called : cmbslno.

 

[Seacowdaz]

So the one in my Workbook is CmoboxBox2 the one in the Doctest sheet you provided was called 'cmbsIno' i assumed (wrongly it seems) that i can't just change it to match what i have on my userform

 

[Logit]

I got turned around with the combobox names. My apologies.

Would you post a copy of your workbook that also has the userform and the comboboxes / textboxes ?

 

[Seacowdaz]

Hi, sorry about the delay in replying. i am not sure how to attach a copy but i have it shared on my google drive using the below link:

Sample Sheet

the userform in question is the EditUser userform.

 

[Logit]

Thanks for your last post with the link to download the workbook. However, that link to the workbook ... it doesn't have the UserForm you are utilizing nor any code.

Please include with the workbook download the UserForm and all of the code you are attempting to use. Thanks.

 

[Seacowdaz]

Hi Sorry about that. you should be able to open it from onedrive:

OneDrive Sample Sheet

 

[Seacowdaz]

So i think i have managed to get it working. using the below code, my question now (and i appreciate that this is slightly off topic) is there anyway to format textboxes 6 and 7 so that when the value is pulled through it shows the time correctly?

Private Sub CommandButton1_Click()

Agent = Me.ComboBox2.Value
Sheets("Daily Report").Select
Worksheets("Daily Report").Unprotect "pse"
Dim rowselect As Double
Dim msg As String
Dim ans As String
rowselect = Me.ComboBox2.ListIndex + 4
rowselect = rowselect + 1
Rows(rowselect).Select
Cells(rowselect, 4) = Me.TextBox1.Value
Cells(rowselect, 37) = Me.TextBox2.Value
Cells(rowselect, 2) = Me.TextBox3.Value
Cells(rowselect, 1) = Me.TextBox4.Value
Cells(rowselect, 3) = Me.TextBox5.Value
Cells(rowselect, 39) = Me.ComboBox1.Value
Cells(rowselect, 31) = Me.TextBox6.Value
Cells(rowselect, 32) = Me.TextBox7.Value
rowselect = rowselect
msg = "  Successfully Updated...Continue?"
Unload Me
ans = MsgBox(msg, vbYesNo, "Update")
If ans = vbYes Then
Sheets("Daily Report").Select
Worksheets("Daily Report").Unprotect "pse"
Else
EditAgent.Show
End If

End Sub