VBA User form problem

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
117
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,466
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
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


Code:
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

Well-known Member
Joined
Nov 11, 2020
Messages
672
Office Version
  1. 2016
Platform
  1. Windows
Insert public variable (Public vCurrentRow) in some standard module that will keep current selection row number.
In the sheet module insert this code.
VBA 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.
VBA Code:
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

Board Regular
Joined
Sep 14, 2019
Messages
117
Office Version
  1. 365
Platform
  1. Windows
Insert public variable (Public vCurrentRow) in some standard module that will keep current selection row number.
In the sheet module insert this code.
VBA 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.
VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
7,466
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Sep 14, 2019
Messages
117
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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


Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
7,466
Office Version
  1. 2019
Platform
  1. Windows
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
 
Last edited:

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
117
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
7,466
Office Version
  1. 2019
Platform
  1. Windows
Did you place this code in your worksheets code page & try double clicking the cell?

VBA Code:
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

Well-known Member
Joined
Nov 11, 2020
Messages
672
Office Version
  1. 2016
Platform
  1. Windows
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 ...
 

Forum statistics

Threads
1,171,043
Messages
5,873,444
Members
432,980
Latest member
KMorrison12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top