-
Feb 2nd, 2012, 08:22 AM
#1
Thread Starter
Member
[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.
-
Feb 2nd, 2012, 08:43 AM
#2
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!
-
Feb 2nd, 2012, 08:49 AM
#3
Addicted Member
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.
-
Feb 2nd, 2012, 08:53 AM
#4
Thread Starter
Member
Re: Edit row data in a Userform
ok thanks for that, ill give it a try.
-
Feb 3rd, 2012, 07:38 AM
#5
Thread Starter
Member
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.
-
Feb 4th, 2012, 06:07 AM
#6
Re: Edit row data in a Userform
you should assign the value of activecell.row to i, not the reverse
to find the last row with data in a specific column
vb Code:
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
-
Feb 4th, 2012, 07:37 AM
#7
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|