Userform to Update or delete information from cells in an Excel spreadsheet

[yannine]

Hi. I have designed a userform with several text and comboboxes. A user may not have all the required information available to complete the form when it is accessed for the first time and therefore, the Excel spreadsheet is only populated with the completed information. I need to be able to pull the information from the Excel spreadsheet into another Userform (which I have designed and which is working well), update the second userform with the outstanding information and post the updated information back into the same row of cells in the Excel spreadsheet. I am completely at odds on how to achieve this - when I try to update or replace the existing information, it goes to a separate line on the spreadsheet.

I would be so grateful for any assistance on how to achieve the above.
Many thanks!

Private Sub CommandButton2_Click()

    Workbooks.Open Filename:= _
        "\\serveshare\Desktop\PreProd forms and data\SQL\TrackerData.xlsm"

  Windows("TrackerData.xlsm").Activate
  Windows("TrackerData.xlsm").Activate
   Windows("TrackerForm.xlsm").Activate

Dim wb As Workbook, sh As Worksheet
Set wb = Workbooks("TrackerData.xlsm")
Set sh = wb.Sheets("PP_Info")

cAry = Array(Me.TextBox1, Me.TextBox4, Me.TextBox36, Me.TextBox29, ComboBox5, Me.TextBox2, Me.TextBox3, ComboBox13, Me.TextBox8, Me.TextBox26, Me.TextBox30, Me.TextBox32, Me.TextBox31, Me.TextBox12, Me.TextBox13, Me.ComboBox11, Me.ComboBox6, Me.ComboBox7, Me.ComboBox14, Me.ComboBox8, Me.TextBox37, Me.TextBox17, Me.TextBox19, Me.ComboBox9, Me.ComboBox10, Me.ComboBox1, Me.ComboBox12, Me.ComboBox17, Me.TextBox33, Me.CheckBox6, Me.TextBox34, Me.CheckBox7, Me.ComboBox4, Me.ComboBox15, Me.ComboBox16)
   

   
    With sh
        For i = 1 To 35
            .Cells(Rows.Count, i).End(xlUp)(2) = cAry(i - 1).Value
           

        Next
    End With

           
End Sub

 

[Fluff]

How do you populate the userform to begin with?

 

[dmt32]

Hi,
to update an existing record you will need to replace this code

With sh
        For i = 1 To 35
            .Cells(Rows.Count, i).End(xlUp)(2) = cAry(i - 1).Value
        Next
    End With

with something like this

With sh
        For i = 1 To 35
            .Cells(RecordRow, 1).Value = Me.Controls("TextBox" & i).Value
        Next
    End With

Where RecordRow is a declared Long variable that contains the row your record is to be returned to.
This value could for example, be found by searching for a unique value in a column like an ID number.

Dave

 

[yannine]

Hi,
to update an existing record you will need to replace this code

With sh
        For i = 1 To 35
            .Cells(Rows.Count, i).End(xlUp)(2) = cAry(i - 1).Value
        Next
    End With

with something like this

With sh
        For i = 1 To 35
            .Cells(RecordRow, 1).Value = Me.Controls("TextBox" & i).Value
        Next
    End With

Where RecordRow is a declared Long variable that contains the row your record is to be returned to.
This value could for example, be found by searching for a unique value in a column like an ID number.

Dave

 

[yannine]

Hi Dave

Thank you so much for the help. I really appreciate it