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]

Hi,

As an idea before your form is displayed, you can use its tag property to mark that you are in Edit mode & this is used in your code to use the ActiveCell.Row to update that record.

Sub EditAdd()
    Dim EditMode    As Boolean
    Dim RecordRow   As Long, j As Long
    Dim msg         As String
    
    EditMode = Val(Me.Tag) > 0
    
    If EditMode Then
        RecordRow = Val(Me.Tag)
    Else
        RecordRow = WorksheetFunction.CountA(Range("A:A")) + 2
    End If
    
    For j = IIf(EditMode, 2, 1) To 16
        With Me.Controls("TextBox" & j)
            Cells(RecordRow, j).Value = .Value
            'clear record
            .Value = ""
        End With
    Next j
    
    msg = IIf(EditMode, "Record Updated", "New Record Added")
    MsgBox msg, 64, msg
    Me.Tag = 0
    
End Sub

Note I have replaced your userform1 with the Me keyword which assumes that the code is in your userforms code page?

You have not shared the code that displays the form but guessing using the double click event which will need to be modified to update the forms Tag property

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With UserForm1
        .Tag = Target.Row
        For j = 1 To 16
            .Controls("TextBox" & j).Value = Cells(Target.Row, j).Text
        Next j
        .Show
    End With
End Sub

Hope Helpful

Dave

 

[EXCEL MAX]

Insert public variable (Public vCurrentRow) in some standard module that will keep current selection row number.
In the sheet module insert this code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   vCurrentRow = Target.Row
   For j = 1 To 16
      UserForm1.Controls("TextBox" & j).Value = Cells(vCurrentRow, j).Value
   Next j

End Sub

In the userform this.

Private Sub CommandButton1_Click()
   
    EditAdd
   
End Sub

Sub EditAdd()

      For j = 1 To 16
          Cells(vCurrentRow, j).Value = UserForm1.Controls("TextBox" & j).Value
      Next j

End Sub

 

[floggingmolly]

Insert public variable (Public vCurrentRow) in some standard module that will keep current selection row number.
In the sheet module insert this code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   vCurrentRow = Target.Row
   For j = 1 To 16
      UserForm1.Controls("TextBox" & j).Value = Cells(vCurrentRow, j).Value
   Next j

End Sub

In the userform this.

Private Sub CommandButton1_Click()
  
    EditAdd
  
End Sub

Sub EditAdd()

      For j = 1 To 16
          Cells(vCurrentRow, j).Value = UserForm1.Controls("TextBox" & j).Value
      Next j

End Sub

I can't get either of these suggestions to work. In the code above I get an error on this line
Cells(vCurrentRow, j).Value = UserForm 1. Controls("TextBox" & j).Value

 

[dmt32]

I can't get either of these suggestions to work.

I just made a guess but helpful if you can share code you were using to show the userform

Dave

 

[floggingmolly]

Hi,

As an idea before your form is displayed, you can use its tag property to mark that you are in Edit mode & this is used in your code to use the ActiveCell.Row to update that record.

Sub EditAdd()
    Dim EditMode    As Boolean
    Dim RecordRow   As Long, j As Long
    Dim msg         As String
   
    EditMode = Val(Me.Tag) > 0
   
    If EditMode Then
        RecordRow = Val(Me.Tag)
    Else
        RecordRow = WorksheetFunction.CountA(Range("A:A")) + 2
    End If
   
    For j = IIf(EditMode, 2, 1) To 16
        With Me.Controls("TextBox" & j)
            Cells(RecordRow, j).Value = .Value
            'clear record
            .Value = ""
        End With
    Next j
   
    msg = IIf(EditMode, "Record Updated", "New Record Added")
    MsgBox msg, 64, msg
    Me.Tag = 0
   
End Sub

Note I have replaced your userform1 with the Me keyword which assumes that the code is in your userforms code page?

You have not shared the code that displays the form but guessing using the double click event which will need to be modified to update the forms Tag property

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With UserForm1
        .Tag = Target.Row
        For j = 1 To 16
            .Controls("TextBox" & j).Value = Cells(Target.Row, j).Text
        Next j
        .Show
    End With
End Sub

Hope Helpful

Dave

To show the form I just use the UserForm1.show command. The code above isn't working either. The "Me" line is giving an error. I'm not familiar with that so I don't know how to fix it.

 

[dmt32]

To show the form I just use the UserForm1.show command. The code above isn't working either. The "Me" line is giving an error. I'm not familiar with that so I don't know how to fix it.

I did comment that using Me keyword assumes the EditAdd code is in your userforms code page - as it is not, replace Me with your userform name & see if code does what you want.

If still have issues helpful if could place copy of your workbook with dummy data on a file sharing site like dropbox & provide a link to it

Dave

 

[floggingmolly]

I did comment that using Me keyword assumes the EditAdd code is in your userforms code page - as it is not, replace Me with your userform name & see if code does what you want.

If still have issues helpful if could place copy of your workbook with dummy data on a file sharing site like dropbox & provide a link to it

Dave

I tried changing the Me to the userform name. Still not working. It just adds a new line with whatever is in cell that was selected. Example, if I select A3 the form is populated with the data in Row 3 for each column, then when I click Add/Edit it just adds whatever is in cell A3 into cell A6. There is already data in some columns, example A thru G might have data. Then columns H thru P need filled in as each item is completed. So when the form is populated with Row 3 it has data for columns A thru G. I need the row to update as items are completed.

 

[dmt32]

Did you place this code in your worksheets code page & try double clicking the cell?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With UserForm1
        .Tag = Target.Row
        For j = 1 To 16
            .Controls("TextBox" & j).Value = Cells(Target.Row, j).Text
        Next j
        .Show
    End With
End Sub

Dave

 

[EXCEL MAX]

About my code.
Did you declare public variable "vCurrentRow" in the standard module?
You can declare public variable there, or you can place there public declaration together with EditAdd() procedure.
The code in the worksheet module will keep selected row in the variable and fill textboxes on the selection change event.
The button click event will activate EditAdd() procedure to fill sheet currently selected row with values from textboxes.
Also check did all 16 textboxes have names TextBox1, TextBox2, TextBox3 etc ...