PDA

View Full Version : Solved: Userform to edit filtered data



Johnpants
11-13-2005, 10:22 AM
Hi, I have a worksheet that has filters on it that people will use to find a specific row. Now what I want is that once this has been filtered, and a userform is opened the fields in the userform will edit the filtered row only..

Is this possible? Could anyone help me with this please?

Thanks,
John.

Zack Barresse
11-13-2005, 11:50 AM
Hi, yes this is possible, but not with the information you have provided. We would need much more details, perhaps a zipped/uploaded sample file to work from and some examples of what you're talking about.

Johnpants
11-14-2005, 02:54 AM
Hi, thank you but I am having trouble uploading the file. I can only get it down to 334kb even when zipped. I have removed everything from it apart from some of the data...

John.

Bob Phillips
11-14-2005, 04:33 AM
Hi, I have a worksheet that has filters on it that people will use to find a specific row. Now what I want is that once this has been filtered, and a userform is opened the fields in the userform will edit the filtered row only..

Is this possible? Could anyone help me with this please?

Thanks,
John.

After the filter, set a range to the visible cells, like

Set rng = original.range.Specialcells(xlCellTypeVisible)

and then work on that new range object.

Johnpants
11-14-2005, 06:21 AM
Ok, I see what you mean. When I open the userform how do I populate the textbox's with the relevent info, for example after I have filtered and there is 1 record left, what code do I need to fill textbox1 with the filtered data from column B for example?

then the user will be able to change the data in the userform, click 'amend' and it will change the relevent cell...

Does this make sense?

Thank you for your time.

Bob Phillips
11-14-2005, 06:23 AM
Ok, I see what you mean. When I open the userform how do I populate the textbox's with the relevent info, for example after I have filtered and there is 1 record left, what code do I need to fill textbox1 with the filtered data from column B for example?

then the user will be able to change the data in the userform, click 'amend' and it will change the relevent cell...

Does this make sense?

Thank you for your time.

Using the range as described before, you then use Cells to pick from the range

rng.Cells(1,2)

gets column B from that row.

Johnpants
11-14-2005, 06:48 AM
http://f4.grp.yahoofs.com/v1/UIp4QytwXrun2HzSMiP_yHEw50nbstEylztuuDIEAHOmxHnNSKFFtQO9o53B6a8wVm-n90Ka7EaY63zJgKi6eA/editrectest.zip

Thank you for your help, but I am still new to this VBA and learning as I go along. I am not sure where this code needs to be placed and what it requires.

I have uploaded a zip file of the spreadsheet to the above link. If it isn't too much trouble would you mind taking a quick look and telling me where I need to enter this code etc.

I really do appreciate this.

John.

Bob Phillips
11-14-2005, 07:39 AM
Couldn't upload the file too big.

This is the userform code



Dim iLastrow As Long
Dim i As Long
Dim rng As Range

Private Sub cmbAmend_Click()
rng.Cells(1, "B") = TextBox1.Text
rng.Cells(1, "C") = TextBox2.Text
rng.Cells(1, "E") = TextBox3.Text
rng.Cells(1, "G") = TextBox4.Text
Unload Me
End Sub

Private Sub cmbDelete_Click()
rng.Rows(1).Delete
Unload Me
End Sub

Private Sub UserForm_Activate()

iLastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Rows(5).Resize(iLastrow - 4)
Set rng = rng.SpecialCells(xlCellTypeVisible)
TextBox1.Text = rng.Cells(1, "B")
TextBox2.Text = rng.Cells(1, "C")
TextBox3.Text = rng.Cells(1, "E")
TextBox4.Text = rng.Cells(1, "G")
End Sub


I changed the name of the amend button to cmbAmend.

Johnpants
11-14-2005, 10:04 AM
Thank you very much, working perfectly. 1 step closer to making my boss happy.. :)

Johnpants
11-14-2005, 11:55 AM
Hate to be a pain, but one more small thing if it is possible.. :)


How easy is it to copy the amended row, and paste it to the next empty row at the bottom of the sheet when I click the 'Amend' button?

Thanks again,

John