VBA User form problem

F

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
167
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
 
Sort by date Sort by votes
dmt32 said:
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
Click to expand...
I uploaded the workbook here USERFORM - UPDATE FROM SELECTED CELL V2. I've tried everything and just keep making it worse. LOL
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
dmt32 said:
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
Click to expand...
Sorry to bother you again. Is there any way to open the form with a button rather than double click? I know i can use the Show function normally, but with this method of double clicking, is there a way to adjust that so I can use a button instead?
 
Upvote 0
EXCEL MAX said:
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 ...
Click to expand...
I got this working to a point. I had to remove a combo box from the form because this below line was giving an error. Any way to make this work with the combo box?
UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
 
Upvote 0
In which way?
Do you want to replace all 16 textboxes with combobox or just some few of them?
 
Upvote 0
Hi,
had a play with your project, see if the changes made to attached file do what you want

Dropbox

www.dropbox.com www.dropbox.com

or you can copy the updatedcodes below

UserForm Module Codes

VBA Code: 
Private Sub CommandButton1_Click()
    EditAdd Me
End Sub

Private Sub CommandButton2_Click()
    ClearForm Me
End Sub

Private Sub CommandButton3_Click()
    Unload Me
End Sub

Private Sub TextBox1_Change()
    GetData Me
End Sub

Private Sub UserForm_Activate()
    If Len(Me.TextBox1.Value) = 0 Then GetData Me
End Sub

Private Sub UserForm_Click()
    TextBox1.SetFocus
End Sub

Private Sub UserForm_Initialize()
    Dim ctrl        As msforms.Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Label" Then ctrl.BackColor = &HC0FFC0
    Next ctrl
End Sub

Standard Module Codes

VBA Code: 
Sub ClearForm(ByVal Form As Object)
    For j = 1 To 17
        Form.Controls("TextBox" & j).Value = ""
    Next j
    Form.TextBox1.SetFocus
End Sub

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

Sub GetData(ByVal Form As Object)
    Dim CSA_ID      As Variant, m As Variant
    Dim EditMode    As Boolean
    
    CSA_ID = Form.TextBox1.Value
    
    m = Application.Match(CSA_ID, Columns(1), 0)
    
    For j = 2 To 17
        With Form.Controls("TextBox" & j)
            If Not IsError(m) Then .Value = Cells(CLng(m), j).Text: Form.Tag = m Else .Value = "": Form.Tag = 0
        End With
    Next j
    
    EditMode = Val(Form.Tag) > 0
    
    'button status
    With Form.CommandButton1
        .Enabled = IsNumeric(CSA_ID)
        .Caption = IIf(EditMode, "Edit", "Add")
        .BackColor = IIf(EditMode, rgbOrange, &H4000&)
    End With
    
    Form.Caption = IIf(EditMode, "Edit CAS_ID - " & CSA_ID, "Add New Record")
    
End Sub

Sub ShowForm_Click()
    If ActiveCell.Row > 1 Then
        With UserForm1
            .TextBox1.Value = Cells(ActiveCell.Row, 1).Text
            .Show
        End With
    End If
End Sub

I have only lightly tested but seemed to work ok

Hope Helpful

Dave
 
Upvote 0
dmt32 said:
Hi,
had a play with your project, see if the changes made to attached file do what you want

Dropbox

www.dropbox.com www.dropbox.com

or you can copy the updatedcodes below

UserForm Module Codes

VBA Code: 
Private Sub CommandButton1_Click()
    EditAdd Me
End Sub

Private Sub CommandButton2_Click()
    ClearForm Me
End Sub

Private Sub CommandButton3_Click()
    Unload Me
End Sub

Private Sub TextBox1_Change()
    GetData Me
End Sub

Private Sub UserForm_Activate()
    If Len(Me.TextBox1.Value) = 0 Then GetData Me
End Sub

Private Sub UserForm_Click()
    TextBox1.SetFocus
End Sub

Private Sub UserForm_Initialize()
    Dim ctrl        As msforms.Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Label" Then ctrl.BackColor = &HC0FFC0
    Next ctrl
End Sub

Standard Module Codes

VBA Code: 
Sub ClearForm(ByVal Form As Object)
    For j = 1 To 17
        Form.Controls("TextBox" & j).Value = ""
    Next j
    Form.TextBox1.SetFocus
End Sub

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

Sub GetData(ByVal Form As Object)
    Dim CSA_ID      As Variant, m As Variant
    Dim EditMode    As Boolean
   
    CSA_ID = Form.TextBox1.Value
   
    m = Application.Match(CSA_ID, Columns(1), 0)
   
    For j = 2 To 17
        With Form.Controls("TextBox" & j)
            If Not IsError(m) Then .Value = Cells(CLng(m), j).Text: Form.Tag = m Else .Value = "": Form.Tag = 0
        End With
    Next j
   
    EditMode = Val(Form.Tag) > 0
   
    'button status
    With Form.CommandButton1
        .Enabled = IsNumeric(CSA_ID)
        .Caption = IIf(EditMode, "Edit", "Add")
        .BackColor = IIf(EditMode, rgbOrange, &H4000&)
    End With
   
    Form.Caption = IIf(EditMode, "Edit CAS_ID - " & CSA_ID, "Add New Record")
   
End Sub

Sub ShowForm_Click()
    If ActiveCell.Row > 1 Then
        With UserForm1
            .TextBox1.Value = Cells(ActiveCell.Row, 1).Text
            .Show
        End With
    End If
End Sub

I have only lightly tested but seemed to work ok

Hope Helpful

Dave
Click to expand...
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.
 
Upvote 0
EXCEL MAX said:
In which way?
Do you want to replace all 16 textboxes with combobox or just some few of them?
Click to expand...
I just want to replace or add a few combo boxes. Below is the code. I'm not sure how to adjust it to add combo boxes. When I double click a row to update it in the form, I get an error with this line highlighted UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value. Below is the codes if needed.
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With UserForm1
        .Tag = Target.Row
        For j = 1 To 17
            .Controls("TextBox" & j).Value = Cells(Target.Row, j).Text
        Next j
        .Show
    End With
End Sub

Code: 
Sub EditAdd()
    Dim EditMode    As Boolean
    Dim RecordRow   As Long, j As Long
    Dim msg         As String
    
    EditMode = Val(UserForm1.Tag) > 0
    
    If EditMode Then
        RecordRow = Val(UserForm1.Tag)
    Else
        RecordRow = WorksheetFunction.CountA(Range("A:A")) + 2
    End If
    
    For j = IIf(EditMode, 2, 1) To 17
        With UserForm1.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
    UserForm1.Tag = 0
    
End Sub
 
Upvote 0
Suppose you replace TextBox17 with ComboBox 17.
You can determine with "Select Case" what control is ComboBox.
VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True
   With UserForm1
      .Tag = Target.Row
      For j = 1 To 17
         Select Case j
            Case 17
               .Controls("ComboBox" & j).Value = Cells(Target.Row, j).Text
            Case Else
               .Controls("TextBox" & j).Value = Cells(Target.Row, j).Text
         End Select
      Next j
      .Show
   End With
End Sub
Similary you can do in other parts of code.
 
Upvote 0
EXCEL MAX said:
Suppose you replace TextBox17 with ComboBox 17.
You can determine with "Select Case" what control is ComboBox.
VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True
   With UserForm1
      .Tag = Target.Row
      For j = 1 To 17
         Select Case j
            Case 17
               .Controls("ComboBox" & j).Value = Cells(Target.Row, j).Text
            Case Else
               .Controls("TextBox" & j).Value = Cells(Target.Row, j).Text
         End Select
      Next j
      .Show
   End With
End Sub
Similary you can do in other parts of code.
Click to expand...
I tried this and still get an error. This line is highlighted .Controls("ComboBox" & j).Value = Cells(Target.Row, j).Text
 
Upvote 0
floggingmolly said:
I tried this and still get an error. This line is highlighted .Controls("ComboBox" & j).Value = Cells(Target.Row, j).Text
Click to expand...
I've tried everything and can't get it to work. I'm not real good with VBA so not sure what I'm doing wrong. I uploaded the workbook if you might have time to take a look. If not, it's OK. USERFORM - UPDATE FROM SELECTED CELL V4
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel VBA Form Add data to a specific range
Replies
2
Views
262
JJCam
J
P
  • Question Question
TextBox to count values from ComboBox item
Replies
1
Views
339
Patriot2879
P
J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
372
Rhodie72
Rhodie72
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
95
Denny57
D
Keebler
  • Solved
adding sort to a complicated array of rabbit holes and ...
2
Replies
12
Views
725
Keebler
Keebler

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top