anish.ms
05-07-2021, 02:13 AM
Hi Experts,
Request your help in the following issue-
I have coded to load the data from excel in user form but the code to update the entries back to excel after changes is not working.
I also need to delete the entry on clicking delete button.
Thanks in advance for your time and support.
On first click, it asks for the serial number and if the serial number matches with user then the data loads to the user form. And on second click it has to update back the entries.
Same way delete button should delete the line item
Private Sub btn_update_Click()
If BlnVal3 = 0 Then
On Error Resume Next
Sln = InputBox("Please enter the serial number you want to update", "Update a saved entry")
CName = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 2, False)
On Error GoTo 0
If Not (CName = txtbx_name.Text) Then
MsgBox "Oops! This serial number doesn't match with your saved entry. " & vbLf & _
"Please download your report and check the serial number."
Else
combb_vertical.Value = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 4, False)
combb_assignment.Value = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 5, False)
txtbx_review_date.Text = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 3, False)
combb_reviewarea.Value = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 6, False)
txtbx_details.Text = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 8, False)
txtbx_review_time.Text = format(Application.VLookup(Sln, DestnWS.Range("Table_DB"), 7, False), "HH:MM")
BlnVal3 = 1
End If
ElseIf BlnVal3 = 1 Then
Call Data_Validation
nRowSavedRecord = Application.WorksheetFunction.Match(Sln, DestnWS.Range("Table_DB[Sl#]"), 0)
With DestnWS
.Cells(nRowSavedRecord, 2) = txtbx_name.Text
.Cells(nRowSavedRecord, 3) = Right(txtbx_review_date.Text, tl - dl)
.Cells(nRowSavedRecord, 4) = combb_vertical.Value
.Cells(nRowSavedRecord, 5) = combb_assignment.Value
.Cells(nRowSavedRecord, 6) = combb_reviewarea.Value
.Cells(nRowSavedRecord, 7) = txtbx_review_time.Text
.Cells(nRowSavedRecord, 8) = txtbx_details.Text
.Cells(nRowSavedRecord, 9) = Now
End With
DestnWB.Save
MsgBox "Successfully Updated!", vbOKOnly, "Updated"
Call btn_clear_Click
BlnVal3 = 0
End If
End Sub
Request your help in the following issue-
I have coded to load the data from excel in user form but the code to update the entries back to excel after changes is not working.
I also need to delete the entry on clicking delete button.
Thanks in advance for your time and support.
On first click, it asks for the serial number and if the serial number matches with user then the data loads to the user form. And on second click it has to update back the entries.
Same way delete button should delete the line item
Private Sub btn_update_Click()
If BlnVal3 = 0 Then
On Error Resume Next
Sln = InputBox("Please enter the serial number you want to update", "Update a saved entry")
CName = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 2, False)
On Error GoTo 0
If Not (CName = txtbx_name.Text) Then
MsgBox "Oops! This serial number doesn't match with your saved entry. " & vbLf & _
"Please download your report and check the serial number."
Else
combb_vertical.Value = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 4, False)
combb_assignment.Value = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 5, False)
txtbx_review_date.Text = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 3, False)
combb_reviewarea.Value = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 6, False)
txtbx_details.Text = Application.VLookup(Sln, DestnWS.Range("Table_DB"), 8, False)
txtbx_review_time.Text = format(Application.VLookup(Sln, DestnWS.Range("Table_DB"), 7, False), "HH:MM")
BlnVal3 = 1
End If
ElseIf BlnVal3 = 1 Then
Call Data_Validation
nRowSavedRecord = Application.WorksheetFunction.Match(Sln, DestnWS.Range("Table_DB[Sl#]"), 0)
With DestnWS
.Cells(nRowSavedRecord, 2) = txtbx_name.Text
.Cells(nRowSavedRecord, 3) = Right(txtbx_review_date.Text, tl - dl)
.Cells(nRowSavedRecord, 4) = combb_vertical.Value
.Cells(nRowSavedRecord, 5) = combb_assignment.Value
.Cells(nRowSavedRecord, 6) = combb_reviewarea.Value
.Cells(nRowSavedRecord, 7) = txtbx_review_time.Text
.Cells(nRowSavedRecord, 8) = txtbx_details.Text
.Cells(nRowSavedRecord, 9) = Now
End With
DestnWB.Save
MsgBox "Successfully Updated!", vbOKOnly, "Updated"
Call btn_clear_Click
BlnVal3 = 0
End If
End Sub