Write data in the correct table row

[Arie Bos]

I have a table with 42 columns and 10 rows with customer date in each row. I made a form in which the user can fill in all the textboxes and then save as a new row to this table. Works all fine.
No, a have a similar form in which and existing customer can be 'lifted' out of the table to make changes later, using a combobox. This also works fine.
Where I am stuck, is how to write all textboxes, some with updated content, back to the same row as where they came from.

below lists my latest attempt:
(CustomerData = the full table
SelectedClient = the range with company names used in the combobox to search the company to be edited))

Where do I go wrong?

Private Sub btnSaveEditsCompany_Click()
    Dim CustomerData As Range
    Set CustomerData = Range("A6").CurrentRegion
    Dim clientRow As Long
    clientRow = Application.Match(CStr(selectedClient), CustomerData.Columns(5), 0)
    ' Wait for user to edit data and click the "Save" button
    ' Save edited data back into the table
            CustomerData.Cells(clientRow, 5).Value = txtClientCompanyName.Value
            CustomerData.Cells(clientRow, 6).Value = txtClientPhone.Value
            CustomerData.Cells(clientRow, 7).Value = txtClientWebsite.Value
            CustomerData.Cells(clientRow, 8).Value = txtSalesAgent.Value
            CustomerData.Cells(clientRow, 9).Value = txtClientAddress.Value
            CustomerData.Cells(clientRow, 10).Value = txtClientCity.Value
            CustomerData.Cells(clientRow, 11).Value = txtClientZip.Value
                        
            MsgBox "Client data has been updated.", vbInformation, "Success"
End Sub

 

[dmt32]

Hi,

The code you have that returns your data to your userform you should retain the records row in a variable that can be used to post back to the correct row
You do this by placing the variable to the very TOP of your userform code page OUTSIDE any procedure.

Dim clientRow As Long ' < place at very TOP of the code page

BTW - You probably do not need two usersforms one for Adding records & one for Updating - One userform should be able to perform both functions.

Dave

 

[Arie Bos]

Thank you Dave,
It moved 'Dim clientRow As Long' to the Userform_inititialize section, and it now runs though the next line.
but VBA returns a type mismatch in the first of the writing lines. I formatted the entire CustomerData table as text, could that be the cause for that?

Arie

 

[dmt32]

It moved 'Dim clientRow As Long' to the Userform_inititialize section, and it now runs though the next line.

That's not where I said to move your variable to

You do this by placing the variable to the very TOP of your userform code page OUTSIDE any procedure.

The updated btnSaveEditsCompany_Click code would look something like this

Private Sub btnSaveEditsCompany_Click()
    
    ' Wait for user to edit data and click the "Save" button
    ' Save edited data back into the table
           Cells(clientRow, 5).Value = txtClientCompanyName.Value
           Cells(clientRow, 6).Value = txtClientPhone.Value
           Cells(clientRow, 7).Value = txtClientWebsite.Value
           Cells(clientRow, 8).Value = txtSalesAgent.Value
           Cells(clientRow, 9).Value = txtClientAddress.Value
           Cells(clientRow, 10).Value = txtClientCity.Value
           Cells(clientRow, 11).Value = txtClientZip.Value
                        
            MsgBox "Client data has been updated.", vbInformation, "Success"
End Sub

As the ranges are unqualified it is assumed that the required sheet is the Activesheet.

Dave

 

[Arie Bos]

Ahh. I see, yes, done.
at the first line "CustomerData.Cells(clientRow, 5).Value = txtClientCompanyName.Value" 424 Object required comes up.
The active sheet is 'Customers'. but may be I should still specify this?

 

[dmt32]

Ahh. I see, yes, done.
at the first line "CustomerData.Cells(clientRow, 5).Value = txtClientCompanyName.Value" 424 Object required comes up.

look at example I gave in #Post 4 to amend your procedure

The active sheet is 'Customers'. but may be I should still specify this?

Unless you only have one worksheet in your project, always sensible to qualify ranges to the worksheet.

Dave

 

[Arie Bos]

Thanks Dave, that worked:
now using Worksheets("Customers").Range("CustomerData").Cells(clientRow, 5).Value = txtClientCompanyName.Value
the data is just not saved in the original row, but somewhere below the table CustomerData

Arie

 

[dmt32]

Thanks Dave, that worked:

the data is just not saved in the original row, but somewhere below the table CustomerData

Arie

Response is little confusing but try this update (as published) to your btnSaveEditsCompany_Click code & see if resolves issue

Private Sub btnSaveEditsCompany_Click()
    
    ' Wait for user to edit data and click the "Save" button
    ' Save edited data back into the table
     With ThisWorkbook.Worksheets("Customers")
           .Cells(clientRow, 5).Value = txtClientCompanyName.Value
           .Cells(clientRow, 6).Value = txtClientPhone.Value
           .Cells(clientRow, 7).Value = txtClientWebsite.Value
           .Cells(clientRow, 8).Value = txtSalesAgent.Value
           .Cells(clientRow, 9).Value = txtClientAddress.Value
           .Cells(clientRow, 10).Value = txtClientCity.Value
           .Cells(clientRow, 11).Value = txtClientZip.Value
    End With
                        
            MsgBox "Client data has been updated.", vbInformation, "Success"
End Sub

Dave

 

[Arie Bos]

Sorry to be unclear... the edited cells are placed on the wrong row, not on the row it came from

 

[dmt32]

Sorry to be unclear... the edited cells are placed on the wrong row, not on the row it came from

not unclear - did you try the updated code?

Dave