VBA User form problem

[floggingmolly]

I have a userform and when the user selects a cell, say A3, the form populates with the data in that row. The user can update the data in the form and click the Add/Edit button. The issue is there can be rows with the same data in Column A. For example A3 might be 300001 and A4 might be the same 300001. So when they click the Edit button I need it to update the row they are on. Right now, using the code I have, it updates the first row that has 300001 so its updating the wrong row. Is there a way to make it update the row that was selected? Below is my code. Any help would be appreciated.

Sub EditAdd()

Dim emptyRow As Long

If UserForm1.TextBox1.Value <> "" Then
    flag = False
    i = 0
    ID = UserForm1.TextBox1.Value
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 2

    Do While Cells(i + 2, 1).Value <> ""

        If Cells(i + 2, 1).Value = ID Then
            flag = True
            For j = 2 To 16
                Cells(i + 2, j).Value = UserForm1.Controls("TextBox" & j).Value
            Next j
        End If

        i = i + 2

    Loop

    If flag = False Then
        For j = 1 To 16
            Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
        Next j
    End If

End If

End Sub

 

[dmt32]

Sorry, I just had a chance to check it out. The problem I have is there can be duplicates in Column A. In that case, it doesn't work. It imports whatever the first instance is into the userform. Example if 333 appears more than once in column A, I need to be able to import and update that row in the userform.

Oh dear not sure what I was thinking, must have had one of my senior moments

Try this updated version & see if does what you want

Dave

USERFORM - UPDATE FROM SELECTED CELL V5.xlsm

Shared with Dropbox

www.dropbox.com

 

[EXCEL MAX]

Floggingmolly,
accordion to the "FROM SELECTED CELL V4" I have create improved version with "Table1".
Things are not so simple as you think to modify basic idea so easily. Follow this steps.
Make a copy of "FROM SELECTED CELL V4".
Delete all codes.
This goes to the Sheet1 module

Private Sub TextBox1_Change()
   ActiveSheet.ListObjects("Table1").Range.AutoFilter 1, "*" & TextBox1 & "*", xlFilterValues
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   vCurrentRow = Target.Row
   GetData
End Sub

This goes to the Userform1 module

Private Sub CommandButton1_Click()
   EditAdd
End Sub

Private Sub CommandButton2_Click()
   ClearForm
End Sub

Private Sub CommandButton3_Click()
   Unload Me
End Sub

This goes to the standard module

Public vCurrentRow

Sub RoundedRectangle1_Click()
   vCurrentRow = ActiveCell.Row
   GetData
   UserForm1.Show False
End Sub

Sub ClearForm()
   For j = 1 To 17
       UserForm1.Controls("TextBox" & j).Value = ""
   Next j
   UserForm1.ComboBox1.Value = ""
End Sub

Sub GetData()
    With UserForm1
       For j = 1 To 17
           .Controls("TextBox" & j).Value = Sheets("Form").Cells(vCurrentRow, j).Text
       Next j
       .ComboBox1.Value = Sheets("Form").Cells(vCurrentRow, 18).Text
   End With
End Sub

Sub EditAdd()
   With Sheets("Form")
      If UserForm1.TextBox1 = "" Then MsgBox "There is no CSA": Exit Sub
      Set vFindRecord = .Columns(1).Find(UserForm1.TextBox1, , , xlWhole, , , True)
      If Not vFindRecord Is Nothing Then
         For j = 1 To 17
             .Cells(vCurrentRow, j).Value = UserForm1.Controls("TextBox" & j).Value
         Next j
         .Cells(vCurrentRow, 18).Value = UserForm1.ComboBox1.Value
          MsgBox "Record Updated"
      Else
         .ListObjects("Table1").ListRows.Add
         vCurrentRow = .ListObjects("Table1").ListRows.Count + 1
         For j = 1 To 17
             .Cells(vCurrentRow, j).Value = UserForm1.Controls("TextBox" & j).Value
         Next j
         .Cells(vCurrentRow, 18).Value = UserForm1.ComboBox1.Value
         MsgBox "New Record Added"
      End If
   End With
   ClearForm
   UserForm1.Hide
End Sub

 

[floggingmolly]

Floggingmolly,
accordion to the "FROM SELECTED CELL V4" I have create improved version with "Table1".
Things are not so simple as you think to modify basic idea so easily. Follow this steps.
Make a copy of "FROM SELECTED CELL V4".
Delete all codes.
This goes to the Sheet1 module

Private Sub TextBox1_Change()
   ActiveSheet.ListObjects("Table1").Range.AutoFilter 1, "*" & TextBox1 & "*", xlFilterValues
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   vCurrentRow = Target.Row
   GetData
End Sub

This goes to the Userform1 module

Private Sub CommandButton1_Click()
   EditAdd
End Sub

Private Sub CommandButton2_Click()
   ClearForm
End Sub

Private Sub CommandButton3_Click()
   Unload Me
End Sub

This goes to the standard module

Public vCurrentRow

Sub RoundedRectangle1_Click()
   vCurrentRow = ActiveCell.Row
   GetData
   UserForm1.Show False
End Sub

Sub ClearForm()
   For j = 1 To 17
       UserForm1.Controls("TextBox" & j).Value = ""
   Next j
   UserForm1.ComboBox1.Value = ""
End Sub

Sub GetData()
    With UserForm1
       For j = 1 To 17
           .Controls("TextBox" & j).Value = Sheets("Form").Cells(vCurrentRow, j).Text
       Next j
       .ComboBox1.Value = Sheets("Form").Cells(vCurrentRow, 18).Text
   End With
End Sub

Sub EditAdd()
   With Sheets("Form")
      If UserForm1.TextBox1 = "" Then MsgBox "There is no CSA": Exit Sub
      Set vFindRecord = .Columns(1).Find(UserForm1.TextBox1, , , xlWhole, , , True)
      If Not vFindRecord Is Nothing Then
         For j = 1 To 17
             .Cells(vCurrentRow, j).Value = UserForm1.Controls("TextBox" & j).Value
         Next j
         .Cells(vCurrentRow, 18).Value = UserForm1.ComboBox1.Value
          MsgBox "Record Updated"
      Else
         .ListObjects("Table1").ListRows.Add
         vCurrentRow = .ListObjects("Table1").ListRows.Count + 1
         For j = 1 To 17
             .Cells(vCurrentRow, j).Value = UserForm1.Controls("TextBox" & j).Value
         Next j
         .Cells(vCurrentRow, 18).Value = UserForm1.ComboBox1.Value
         MsgBox "New Record Added"
      End If
   End With
   ClearForm
   UserForm1.Hide
End Sub

I just tried this and it works. Now I just need to figure out how to add a few more combo boxes. I may need to add a couple dropdowns in between some of the rows. I really appreciate all your help. I would have never figured this out myself.

 

[EXCEL MAX]

 

[floggingmolly]

If I wanted to add a combo box like for column 10, would I have to redo the whole thing or can I insert it into the code easily? I promise this is my last question. haha

 

[floggingmolly]

I can add them to the end, but in the middle I'm not sure because 1-17 are text boxes. I'm assuming I need to update the code to be something like 1-10, then 11 for combobox, then 12-17 for text boxes again. If that made any sense at all.

 

[EXCEL MAX]

As I say, sometimes It's not easy to change basic idea.
The code will be changed depending of the userform controls layout.
Few changes, suppose.

 

[floggingmolly]

As I say, sometimes It's not easy to change basic idea.
The code will be changed depending of the userform controls layout.
Few changes, suppose.

Following your guidance I was able to add a ComboBox to the middle and was able to get it to work. I can't thank you enough for your help. You're awesome .

 

[EXCEL MAX]

I'm glad you were able to finish task for yourself.