PDA

View Full Version : Edit Feature in Userform



uaku
04-29-2011, 07:33 PM
Hello Everyone,
I created a form for data entry, however I am unable to create a button where I can edit and save the data. So far the save button is putting the duplicate record at the end of the last row.
I would appreciate your help

Thanks
Aku

Bob Phillips
04-30-2011, 02:34 AM
Show us the code you have so we can help.

uaku
04-30-2011, 05:12 AM
Private Sub CmdBtn_2_Click()
UsrFrm_DataEntry.Hide
Range("B6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Value = TextBox2.Value
ActiveCell.Offset(1, 1).Value = TextBox3.Value
ActiveCell.Offset(1, 2).Value = TextBox4.Value

End Sub

I am using this to populate data at the last row, now for edit I don't want to create a new data at the end, Instead I want to change the cell values. Textbox2 value would be unique. I tried to find that cell value and place everything in that row but it did not work.
Thanks for your help
Aku

BrianMH
04-30-2011, 06:10 AM
Sub test()
Dim rSearch As Range
Dim rFound As Range
Dim dRows As Double
dRows = Range("B6").End(xlDown).Row
Set rSearch = Range("B6:B" & dRows)
Set rFound = rSearch.Find(textbox2.Value)
rFound.Offset(0, 1) = textbox3.Value
rFound.Offset(0, 2) = textbox4.Value


End Sub



This should work and gives a basic premise. It has no error handling and will error if the range is not found. It gives you the basis though.

Bob Phillips
04-30-2011, 06:46 AM
Private Sub CmdBtn_2_Click()
Dim cell As Range
Dim Lastrow As Long
With Range("B6")

Lastrow = .End(xlDown).Row
Set cell = Nothing
On Error Resume Next
Set cell = .Resize(Lastrow - 5).Find(TextBox2.Value)
On Error GoTo 0
If cell Is Nothing Then

Lastrow = .End(xlDown).Row
If .Offset(1, 0).Value = "" Then Lastrow = 6
Set cell = .Offset(Lastrow - 5)
End If

cell.Value = TextBox2.Value
cell.Offset(0, 1) = TextBox3.Value
cell.Offset(0, 2) = TextBox4.Value
End With

Me.Hide

End Sub

uaku
04-30-2011, 03:43 PM
Hello Brian, I ran this code and rFound is giving the cell value and the offset function is not working. How can i select the found cell so that I can offset with reference to that.

uaku
04-30-2011, 05:44 PM
I ran the code you gave, it updates the first cell and after that it picks the right textbox value but does not update.

shrivallabha
04-30-2011, 10:16 PM
I am using this to populate data at the last row, now for edit I don't want to create a new data at the end, Instead I want to change the cell values. Textbox2 value would be unique. I tried to find that cell value and place everything in that row but it did not work.
Thanks for your help
Aku
Brian has assumed (my guess) based on the above information:
Since the value is matching you really do not need to change it. But since you are asking for it then does it mean that you have your unique entry in two columns?

Bob (XLD) has given you a solution which will handle both cases i.e. handling cell not found case and found case.

uaku
05-01-2011, 06:30 AM
Shrivallabha,
I successfully created a userform to add data to spreadsheet. What I am difficulty is to edit them in the userform.
Private Sub CmdBtn_2_Click()
Range("B6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Value = TextBox2.Value
.
' I have 26 columns to enter. which it is doing nicely. Later to review the data I am using vlookup as below
Private Sub ComboBox2_Change()
LookupVal = Me.ComboBox2.Value
If LookupVal = "" Then
MsgBox "You have reached the end of students Data"
Unload Me
Exit Sub
End If
Sheets("Student_Info").Activate
Dim i As Long
For i = 2 To 26
UsrFrm_DataEntry.Controls("Textbox" & i).Value = Application.WorksheetFunction.VLookup(CLng(LookupVal), Range("A7:Z100"), i, False)
Next
End Sub

So far the data populates the form using vlookup. What I am having difficulty is to edit this data incase if I find a mistake. Thanks for your help.
Aku

shrivallabha
05-02-2011, 08:08 AM
I am sorry but I have not understood your requirement. Could you post your workbook with sensitive information removed and maybe a demonstration of your requirement?