[RESOLVED] Edit row data in a Userform

[timh1234]

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.

[opus]

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

for Save

  .Cells(NewLine,ColumnA).Value= TextBox1.Text

for edit

    TextBox1.Text=.Cells(ActiveLine,ColumnA).Value

[Resource Dragon]

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.

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.

[timh1234]

ok thanks for that, ill give it a try.

[timh1234]

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.

[westconn1]

you should assign the value of activecell.row to i, not the reverse

i = activecell.row

to find the last row with data in a specific column

lastrow = cells(rows.count, 1).end(xlup).row   ' 1 for column A

[timh1234]

Ah that has worked perfectly. Thank you very much