Results 1 to 7 of 7

Thread: [RESOLVED] Edit row data in a Userform

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2012
    Posts
    50

    Resolved [RESOLVED] Edit row data in a Userform

    Hello,

    I am relatively new to VBA, and I have to do a university project with it this year.

    Very simple stuff I imagine, I have a sheet with lots of column headings and 2 buttons. Clicking one button opens a userform, for the user to create a new entry. They fill in all the list boxes, combo boxes, text boxes etc in the form, click OK and it will save all the info into one row. This bit works fine.

    The problem lies with the other button. 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.

    I am using Excel 2007. Any ideas for a simple problem?

    Thanks.

  2. #2
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: Edit row data in a Userform

    Welcome to VBForums
    It sounds as if you have the ButtonSave done, the ButtonEdit would just do the reverse

    for Save
    Code:
      .Cells(NewLine,ColumnA).Value= TextBox1.Text
    for edit
    Code:
        TextBox1.Text=.Cells(ActiveLine,ColumnA).Value
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  3. #3
    Addicted Member
    Join Date
    Jun 2009
    Location
    Townsville, Qld, Australia
    Posts
    135

    Re: Edit row data in a Userform

    The code below assumes that you have a userform with two text boxes and a command button. I've also got a fairly limited number of rows of data that I'm working with.

    Code:
    Private Sub CommandButton1_Click()
    Call AmendSheet
    End Sub
    
    Private Sub UserForm_Activate()
    For i = 2 To 6
    If ActiveCell.Row = i Then
    TextBox1.Text = Cells(i, 1)
    TextBox2.Text = Cells(i, 2)
    End If
    Next i
    End Sub
    
    Private Sub UserForm_Click()
    
    End Sub
    
    Private Sub AmendSheet()
    
    For i = 2 To 6
    If ActiveCell.Row = i Then
    Cells(i, 1) = TextBox1.Text
    Cells(i, 2) = TextBox2.Text
    End If
    Next i
    
    End Sub
    Obviously, you'll need to include some sort of error-handling for users who haven't selected a cell in a used row, as well as finding the maximum number of used rows.

  4. #4

    Thread Starter
    Member
    Join Date
    Feb 2012
    Posts
    50

    Re: Edit row data in a Userform

    ok thanks for that, ill give it a try.

  5. #5

    Thread Starter
    Member
    Join Date
    Feb 2012
    Posts
    50

    Re: Edit row data in a Userform

    To get around the problem of finding the maximum number of rows, i thought the following code might be more efficient:

    ActiveCell.Row = i
    TextBox1.Text = Cells(i, 1) etc

    Instead of:

    For i = 2 To 6
    If ActiveCell.Row = i Then
    TextBox1.Text = Cells(i, 1)
    etc
    End If

    But when I run it, my userform just opens up blank. Why is this? I dont see why the code wouldn't work.

    Also if there does need to be that 'for' loop, is there another bit of code, that could determine how many rows to look through instead of using fixed values (ie For i = 2 To 6), as the worksheet will have an indefinite number of rows.

    Thanks.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Edit row data in a Userform

    you should assign the value of activecell.row to i, not the reverse
    vb Code:
    1. i = activecell.row

    to find the last row with data in a specific column
    vb Code:
    1. lastrow = cells(rows.count, 1).end(xlup).row   ' 1 for column A
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Feb 2012
    Posts
    50

    Re: Edit row data in a Userform

    Ah that has worked perfectly. Thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width