VBA Userform - Edit Row

[snuffnchess]

I currently have an Excel sheet with MultiPage userform to add data onto a worksheet. Data has to ability to fill up to 122 columns.

I want to make it so that the sheet cannot be edited directly, I want it to be an "Edit Row" button - so the user can click in any (populated) cell in a row, click the button, and then a userform will come up, similar to "Create new entry" form, but with the active row's data in the text boxes, list boxes etc. When they click "OK" it will update the data for that row.

How do I go about doing this???

 

[snuffnchess]

Thank you for the suggestions!

Is there a way for me to program it in a way where i can say Textbox1.Value = ActiveRow(1,2) or something like that and go down the list? I ask because some of the text boxes do not follow the order of the columns... so if I can manually coordinate which cell fills which column

 

[dmt32]

Hi,
coding it that way would create many lines of code - you can in the select case statement specify the column order for each type of control.
An alternative idea to the array approach would be to cycle through the controls in your form & by setting the tab order of the controls, get data from the correct columns

For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
        txt = txt + 1
        
        ElseIf TypeName(ctl) = "ComboBox" Then
        cmb = cmb + 1
        
        ElseIf TypeName(ctl) = "OptionButton" Then
        opt = opt + 1
        
        ElseIf TypeName(ctl) = "CheckBox" Then
        chk = chk + 1
        
        End If
    Next ctl

I did take a look at your project & note that you seem to be combining some textbox values in to same cell which you would need to split & you are also posting data to another worksheet which adds to the complexity of what you are trying to do.

You project is far too large for the time I have available to engineer a working solution but happy to assist further with others here if I can

dave

 

[snuffnchess]

Dave,

I am getting the concept of adding the data that is in the worksheet back into the form. But then how do i make it so that the data going from the form back into the dataset is updated, rather than added to the last line of the workbook???


Code is setup right now to add data 1 below the last row of a worksheet when info is entered into a userform

        Private Sub CommandButton3_Click()


    Dim WS As Worksheet
    Set WS = Worksheets("ClientDB")
    Dim newRow As Long
    Dim ns As Worksheet
    Set ns = Worksheets("USDS")
    Dim nsRow As Long
    
    newRow = Application.WorksheetFunction.CountA(WS.Range("A:A")) + 2
    nsRow = Application.WorksheetFunction.CountA(ns.Range("A:A")) + 2
    
    WS.Cells(newRow, 1).NumberFormat = "mm/dd/yyyy"
    WS.Cells(newRow, 1).Value = Date
    WS.Cells(newRow, 2).Value = TextBox1.Text
    WS.Cells(newRow, 3).Value = TextBox3.Text

Final column includes a unique identifier

        WS.Cells(newRow, 122).Value = "*" & UCase(Left(WS.Cells(newRow, 2), 2)) & Format(Now, "YYMMDD") & Format(Now, "hhmmss") & UCase(Left(WS.Cells(newRow, 3), 2)) & "*"
    Unload UserForm
    End Sub

I am just at the beginning of getting the data back into the form by using

        Private Sub CommandButton2_Click()




    UserForm.Controls("TextBox1").Text = Cells(Application.ActiveCell.Row, 2).Value
        
    UserForm.Show




    End Sub

where CB2 is the "Edit" button.


What is the best way to go about getting the updated data back into the dataset?




My thoughts are it would need to be something like
If ActiveRow, Last Column is not blank, then append updated data from UserForm to Active Row,
Else Append data from UserForm to "newRow"


But coding that I am at a loss for.

 

[dmt32]

What is the best way to go about getting the updated data back into the dataset?

you can try something like following

Move following variables from the CommandButton3_Click code to the very TOP of your forms code page OUTSIDE any procedure

Dim newRow As Long
 Dim nsRow As Long

update CommandButton3_Click code as follows

Private Sub CommandButton3_Click()
    Dim WS As Worksheet
    Set WS = Worksheets("ClientDB")
    Dim ns As Worksheet
    Set ns = Worksheets("USDS")
    
 'check for edit mode
    If Me.CommandButton3.Caption <> "Update" Then
        newRow = Application.WorksheetFunction.CountA(WS.Range("A:A")) + 2
        nsRow = Application.WorksheetFunction.CountA(ns.Range("A:A")) + 2
    End If
    
    'rest of code
    
    
'reset caption
    Me.CommandButton3.Caption = "Submit"
End Sub

your update or edit code, add lines shown in RED

Private Sub CommandButton2_Click()




    newRow = ActiveCell.Row
    nsRow = newRow
    Me.CommandButton3.Caption = "Update"
    
    'rest of code


    UserForm.Controls("TextBox1").Text = Cells(newRow, 2).Value

    UserForm.Show








End Sub

By moving the row variables to top of the form, they will retain the value set by your Edit code.
Additional code in commandbutton3 checks caption setting & if in Update (or edit mode) the part of your code that finds next new row is ignored so your updated record should be returned to its correct row. At the end of the update process, commandbutton3 caption is reset.


Hope Helpful

Dave

 

[snuffnchess]

Thank you Dave!

When running this, I get an error when hitting commandbutton2

Private Sub CommandButton2_Click()

   newRow = ActiveCell.Row
   nsRow = newRow
   Me.CommandButton3.Caption = "Update"
    
    'rest of code


   UserForm.Controls("TextBox1").Text = Cells(newRow, 2).Value

   UserForm.Show

End Sub

Presented error is relating to the "CommandButton3" caption line

Error Message : "Compile error: Method or data member not found"

Uploaded file to https://www.dropbox.com/s/aqphtlma1l3a2tf/Client List - Start Edit Button Code.xlsm?dl=0

 

[snuffnchess]

Sorry! I cannot edit my last post. I WAS ABLE TO GET IT TO WORK! Instead of Me.CommandButton3.Caption, i used UserForm.CommandButton3.Caption. So THANK YOU!!!!!!

A new error is coming up now, though... your thoughts?

Thank you Dave!



So now, when I hit the "Edit Client" button, it is pulling up the info from the worksheet, and the Captionbutton label says "Update".

When I click on "Update", I am getting a "Run-time error '1004: Application-defined or object-defined error"

The first line it is showing the error is " WS.Cells(newRow, 1).NumberFormat = "mm/dd/yyyy""


note: I tested to see if it has specifically to do with the date field, and it does not... whatever line is listed first is the line the error presents on


Uploaded file to https://www.dropbox.com/s/fqxutakz0wunn9f/Client%20List%20-%20Start%20Edit%20Button%20Code.xlsm?dl=0

 

[dmt32]

Me.CommandButton2.Caption = "Update"

i neglected to change commandbutton name as shown in RED

Dave

 

[dmt32]

your links don't work but check that your variables have been correctly initialized.

WS.Cells(newRow, 1).NumberFormat = "mm/dd/yyyy""

Dave

 

[snuffnchess]

your links don't work but check that your variables have been correctly initialized.

WS.Cells(newRow, 1).NumberFormat = "mm/dd/yyyy""

Dave

Hi Dave.

I played around with it last night, and was able to get it to work by just adding in an "else". So YAY! Thank you for all of your help!

'check for edit mode    If UserForm.CommandButton3.Caption <> "Update" Then
        newRow = Application.WorksheetFunction.CountA(WS.Range("A:A")) + 2
        nsRow = Application.WorksheetFunction.CountA(ns.Range("A:A")) + 2
[COLOR=#b22222]    Else[/COLOR]
[COLOR=#b22222]        newRow = ActiveCell.Row[/COLOR]

There is one more thing that I am needing to do with the Edit part, and I suspect that is where the Unique Identifier for each line will come in to play.

I made it so that identifier will NOT update if the "edit client" button is utilized. That way it is set for a client, and will not change.

What needs to happen is If there is an update to the data in column 2, 3 or 9, then that needs to update on the USDS tab.

Which I suspect would be something like
If UserForm.CommandButton3.Caption = "Update" then
Match WS.Cells(newRow, 122).Value in ns.Cells(newRow, 2).Value
and then update
ns.Cells(nsRow, 3).Value = TextBox1.Text ns.Cells(nsRow, 4).Value = TextBox3.Text
ns.Cells(nsRow, 4).Value = "A"


Please let me know if the link here works... hopefully it does....


https://www.dropbox.com/s/z0duzptpcfrwi3f/Edit.xlsm?dl=0