Consulting

Results 1 to 6 of 6

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

  1. #1
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location

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

    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
    Attached Files Attached Files
    Last edited by anish.ms; 05-07-2021 at 02:59 AM.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635

  3. #3
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks a lot snb for this very useful information!
    I became VBAX Contributor by asking help in this forum and not by providing solutions
    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.
    Last edited by anish.ms; 05-07-2021 at 07:28 AM.

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Question: Is Excel fit for multi-user purposes ?
    Answer: No

  5. #5
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    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

  6. #6
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    I solved the issue. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •