Userform overwritting or not inputting on worksheet

[Atty88]

Hi,

This is my first real userform since leaving college 3 years ago and I must've forgotten 90% of what I learnt. I've made a simple userform for my company to manage their stocktake with but for some reason it occasionally doesn't input the data on the worksheet, or overwrites the previous line or entry. It's probably something simple such as not allowing duplicates of data. But obviously I cant afford to have it miss any inputted data out! Heres the code if anyone can spot a problem or suggest any useful tweaks it would be great!

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Products")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.txtProduct.Value) = "" Then
Me.txtProduct.SetFocus
MsgBox "Please enter a product"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtLocation.Value
ws.Cells(iRow, 2).Value = Me.txtProduct.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value
ws.Cells(iRow, 5).Value = Me.txtComments.Value
'clear the data
Me.txtProduct.Value = ""
Me.txtLocation.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtComments.Value = ""
Me.txtProduct.SetFocus

End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub txtProduct_Change()
End Sub
Private Sub txtComments_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub


Oh also is there any way that the collected data in the worksheet can be filtered using a button to show the total Qty of each product??

Atty88

 

[jongooligan]

Hi,

This is my first real userform since leaving college 3 years ago and I must've forgotten 90% of what I learnt. I've made a simple userform for my company to manage their stocktake with but for some reason it occasionally doesn't input the data on the worksheet, or overwrites the previous line or entry. It's probably something simple such as not allowing duplicates of data. But obviously I cant afford to have it miss any inputted data out! Heres the code if anyone can spot a problem or suggest any useful tweaks it would be great!

Is this a shared workbook issue cos (at first glance) the code looks OK

 

[Andrew Poulsom]

Welcome to the Board.

Maybe you should be checking for a location rather than a product - that's what's going in column A which you are using to get the next blank row.