Thank you very much for your quick response!! So far this is giving me a
"Next without For" error message. Below is how I encorporated it into the
code. Please correct me if I am wrong in doing this. I added the search for
the next empty cell into your code. I received the same error message in both
cases though whether using your code only or the "empty cell" code with it.

If Me.cmdAdd.Caption = "Update" Then
For iRow = 2 To ws.Cells(2000, 1).End(xlUp).Row
If CStr(ws.Cells(iRow, 1)) = Me.txtSearchbox.Value Then
ws.Cells(iRow, 2) = Me.cboType.Value
ws.Cells(iRow, 3) = Me.cboInOut.Value
ws.Cells(iRow, 4) = Me.txtLoanto.Value
Else: 'find first empty row in database
iRow = ws.Cells(Rows.Count, 1 _
).End(xlUp).Offset(1, 0).Row
Next iRow
End If



"JW" wrote:

On Oct 11, 5:45 pm, skidmore
wrote:
I have created an excel userform to enter in DVD's into my own personal excel
database. This form contains a search function which populates the userform
with the DVD information. I only have 4 fields, Title, Type, On shelf/On
Loan, and On Loan To. The form populates pefrectly but I need the code that
would allow me to edit the data once the form is populated. For instance, if
I search for the movie Troy, it then populates the Title field, Type field
(combobox), On shelf/On loan field (combobox), and the On Loan To field (if
data exists in worksheet). This is so i can keep up with which movies are on
loan or not. I need to have the ability to edit this data and have the
userform save/overwrite the data back into the same fields from whence they
came or simply delete the old fields and add the data as a new entry. I
simply don't want to have duplicate movie titles over and over with different
data for each row. Below is a copy of the form code thus far. It works great
except for the ability to overwrite existing data. I have searched every site
imaginable and have not found any help or answers to this issue. I am new to
VB and have compiled all this code from different sites and so on so I am not
by any means VB literate. Any help would be GREATLY appreciated!!

Private Sub CommandButton1_Click()

End Sub

Private Sub cboInOut_Change()

End Sub

Private Sub cboType_Change()

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DVDCollection")

'check for a title
If Trim(Me.txtTitle.Value) = "" Then
Me.txtTitle.SetFocus
MsgBox "You must enter a DVD TITLE!!"
Exit Sub
End If

'check for a type
If Trim(Me.cboType.Value) = "" Then
Me.cboType.SetFocus
MsgBox "You must enter a DVD TYPE!!"
Exit Sub
End If

'check for inout
If Trim(Me.cboInOut.Value) = "" Then
Me.cboInOut.SetFocus
MsgBox "You must enter if the DVD is On Shelf or Out On Loan!!"
Exit Sub
End If

'check for a name
If Trim(Me.cboInOut.Value) = "Out On Loan" Then
Me.txtLoanto.SetFocus
MsgBox "You must enter WHO the DVD is on Loan To!!"
Exit Sub
End If

(I BELIEVE SOMEWHERE HERE WOULD BE WHERE I NEED THE CODE TO LOOK FOR AND/OR
OVERWRITE/EDIT EXISTING CELL DATA THAT IS RETURNED TO THE USERFORM)

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtTitle.Value
ws.Cells(iRow, 2).Value = Me.cboType.Value
ws.Cells(iRow, 3).Value = Me.cboInOut.Value
ws.Cells(iRow, 4).Value = Me.txtLoanto.Value

'clear the data
Me.txtTitle.Value = ""
Me.cboType.Value = ""
Me.cboInOut.Value = ""
Me.txtLoanto.Value = ""
Me.txtTitle.SetFocus

End Sub

Private Sub cmdSearch_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DVDCollection")
For iRow = 2 To ws.Cells(2000, 1).End(xlUp).Row
If CStr(ws.Cells(iRow, 1)) = Me.txtSearchbox.Value Then
Me.txtTitle.Value = ws.Cells(iRow, 1)
Me.cboType.Value = ws.Cells(iRow, 2)
Me.cboInOut.Value = ws.Cells(iRow, 3)
Me.txtLoanto.Value = ws.Cells(iRow, 4)
Exit For
End If
Next iRow
Exit Sub

Me.txtSearchbox.Value = ""

End Sub

Private Sub UserForm_Click()

End Sub

Basically, you've already got the code needed to accomplish this, you
just need to reverse it. I would recommend adding something to the
Search code so that, if your search is successful, it changes the
caption of your cmdAdd button to "Update". YOu can do this by
Me.cmdAdd.Caption = "Update". Then, in your cmdAdd button code, place
something like this:
If Me.cmdAdd.Caption="Update" Then
For iRow = 2 To ws.Cells(2000, 1).End(xlUp).Row
If CStr(ws.Cells(iRow, 1)) = Me.txtSearchbox.Value Then
ws.Cells(iRow, 2) = Me.cboType.Value
ws.Cells(iRow, 3) = Me.cboInOut.Value
ws.Cells(iRow, 4) = Me.txtLoanto.Value
Next iRow
End If

Of course, you might want to throw in some error handling and might
want to clear the form once the Update has been made and change the
caption of cmdAdd back to whatever you want it to be typically.

Regards
-Jeff-