Not Working : Load data from excel in user form and update / delete

[anish.ms]

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

[snb]

To avoid ridiculous code, see

https://www.snb-vba.eu/VBA_Userform_in_database_en.html

[anish.ms]

Thanks a lot snb for this very useful information!:bow:
I became VBAX Contributor by asking help in this forum and not by providing solutions :rotlaugh:
I prepared the user form based on my little knowledge in VBA. Your site would have been very useful if I had it before I started working on the user form. Still, a lot of areas I can be simplify the codes based on your notes.
I referred to your sample user forms and I understand it loads the database into a Listobject first and the database recreated based on the values in the Listobject on clicking save button. Following is my doubts on this -
(1) My user form is simultaneously used by multiple users to update their daily work. So if I use list object to hold the data first and recreate the database on save, different users will have different additions and deletions.

I can replace one the code below to list unique sorted values in a column


Sub ListReportMemberName()
combb_rpt_name.Clear
Dim ReportMemberList As Collection
Dim rng As Range
Dim i As Long
Set ReportMemberList = New Collection
On Error Resume Next
For Each rng In DestnWS.Range("Table_DB[Member Name]")
If Not (rng) = Empty Then ReportMemberList.Add CStr(rng), CStr(rng)
Next
On Error GoTo 0
For i = 1 To ReportMemberList.Count
combb_rpt_name.AddItem ReportMemberList(i)
Next
If combb_rpt_name.ListCount = 0 Then combb_rpt_name.AddItem "No Records Available"
End Sub


With the code below

4.5 unique sorted values in a column


With CreateObject("System.Collections.ArrayList")
For Each cl In sn
If cl<>"" And Not .contains(cl) Then .Add cl
Next
.Sort


ComboBox1.List = Application.Transpose(.toarray())
End With


If I need to declare the variables what would be the "cl" variable type; is it collection? and I hope "sn" is a range.

[snb]

Question: Is Excel fit for multi-user purposes ?
Answer: No

[anish.ms]

Hi snb,
We use office 365 and the database is stored in one drive. The user form goes as an add-in each users excel (max 20 users). And when I say multiple users are accessing the data base simultaneously, it could be max 5 users at a time. We are testing this and currently it is working without any issues.
Appreciate if you can you help me with my request in thread #1

[anish.ms]

I solved the issue. Thanks!