S
skidmore
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
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