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

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-

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-

On Oct 11, 7:16 pm, skidmore
wrote:
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-

Looks like it's not the Next statement that's the problem. You are
missing an End If statement. Should be:
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
End If
Next iRow
End If

I tried this and seemed to have worked but when i checked the spreadsheet it
is still adding the info to the last empty row. i even removed the search for
next empty row code from it and it is still the same. No error this time
though!! I would like the code to be usuable for new entries as well in a
manor that will not allow me to enter a movie title twice. I do appreciate
all the help you have given me thus far.

"JW" wrote:

On Oct 11, 7:16 pm, skidmore
wrote:
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-

Looks like it's not the Next statement that's the problem. You are
missing an End If statement. Should be:
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
End If
Next iRow
End If

On Oct 11, 9:16 pm, skidmore
wrote:
I tried this and seemed to have worked but when i checked the spreadsheet it
is still adding the info to the last empty row. i even removed the search for
next empty row code from it and it is still the same. No error this time
though!! I would like the code to be usuable for new entries as well in a
manor that will not allow me to enter a movie title twice. I do appreciate
all the help you have given me thus far.

"JW" wrote:
On Oct 11, 7:16 pm, skidmore
wrote:
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-

Looks like it's not the Next statement that's the problem. You are
missing an End If statement. Should be:
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
End If
Next iRow
End If

Glad to help skidmore. If you'd like, you can send me the file to the
e-mail address in my profile and I'll take a closer look. I'm sure
it's something simple. Like, in your If statement, we are currently
searching for the value in the txtSearchBox field. Now, if when your
search is successfully completed, you are clearing the txtSearchBox,
then you might want to change that to"
If CStr(ws.Cells(iRow, 1)) = _
Me.txtTitle.Value Then

Okay, i sent it to you. Thanks again!

"JW" wrote:

On Oct 11, 9:16 pm, skidmore
wrote:
I tried this and seemed to have worked but when i checked the spreadsheet it
is still adding the info to the last empty row. i even removed the search for
next empty row code from it and it is still the same. No error this time
though!! I would like the code to be usuable for new entries as well in a
manor that will not allow me to enter a movie title twice. I do appreciate
all the help you have given me thus far.

"JW" wrote:
On Oct 11, 7:16 pm, skidmore
wrote:
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-

Looks like it's not the Next statement that's the problem. You are
missing an End If statement. Should be:
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
End If
Next iRow
End If

Glad to help skidmore. If you'd like, you can send me the file to the
e-mail address in my profile and I'll take a closer look. I'm sure
it's something simple. Like, in your If statement, we are currently
searching for the value in the txtSearchBox field. Now, if when your
search is successfully completed, you are clearing the txtSearchBox,
then you might want to change that to"
If CStr(ws.Cells(iRow, 1)) = _
Me.txtTitle.Value Then

ISSUE IS RESOLVED! JW was a great help with this problem. JW was very patient
with me and persistant on getting this fixed. Thank you JW!!! I couldn't have
figured it out without you. My wife will be glad to get me away from the PC
for a while now. Here is the completed code for the entire form in case
someone else needs to see it.


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")
If Me.cmdAdd.Caption = "Update" Then
For iRow = 2 To ws.Cells(2000, 1).End(xlUp).Row
If CStr(UCase(ws.Cells(iRow, 1).Value)) = _
UCase(Me.txtTitle.Value) Then
ws.Cells(iRow, 2).Value = Me.cboType.Value
ws.Cells(iRow, 3).Value = Me.cboInOut.Value
ws.Cells(iRow, 4).Value = Me.txtLoanto.Value
If Me.cboInOut.Value = "On Shelf" Then _
ws.Cells(iRow, 4).Value = ""
Me.txtTitle.Value = ""
Me.cboType.Value = ""
Me.cboInOut.Value = ""
Me.txtLoanto.Value = ""
Me.txtSearchbox.SetFocus
Me.cmdAdd.Caption = "Save"
End If
Next iRow
Else
For iRow = 2 To ws.Cells(2000, 1).End(xlUp).Row
If CStr(UCase(ws.Cells(iRow, 1).Value)) = _
UCase(Me.txtTitle.Value) Then
MsgBox "DVD Title already exists!!!"
'clear the data
Me.txtTitle.Value = ""
Me.cboType.Value = ""
Me.cboInOut.Value = ""
Me.txtLoanto.Value = ""
Me.txtTitle.SetFocus
Exit Sub
End If
Next iRow
'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 If
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 UCase(ws.Cells(iRow, 1)) = UCase(Me.txtSearchbox.Value) Then
Me.cmdAdd.Caption = "Update"
Me.txtTitle.Value = ws.Cells(iRow, 1).Value
Me.cboType.Value = ws.Cells(iRow, 2).Value
Me.cboInOut.Value = ws.Cells(iRow, 3).Value
Me.txtLoanto.Value = ws.Cells(iRow, 4).Value
Me.txtSearchbox.Value = ""
Exit For
End If
Next iRow
If UCase(ws.Cells(iRow, 1)) = _
Me.txtTitle.Value Then
Me.txtSearchbox.Value = ""
MsgBox "DVD Title not found!!!"
Me.txtSearchbox.SetFocus
End If
Exit Sub

End Sub

Private Sub UserForm_Click()

End Sub