Help : Inventory Database

[mrsbxl]

Hi, I'm trying to create an Inventory Count Database in Excel using a Macro/VBA.

The concept is for the Count Team to enter in an Item Number and Bin Number (which I have Data Validation on to prevent typos/errors). The Item Description is a formula that will pull the description associated with the Item Number, if not found it will print "Not Found." There is going to be a minimum of 2 rounds of counts and a 3rd and 4th are available if the variance between the first two counts is greater than 3%. I would need to have only one count box available for edit at a time. (If they're on Count 1, Counts 2-4 should be locked. If they're on Count 2, Count 1 will display the value that was initially submitted, Counts 3&4 will be locked, etc.).

The Tag No. ideally should be a "counter" that will essentially step up with each item entry.

Can anyone help me out with this?
Please and Thank You!

 

[tjdrake]

Can you share what does your code looks like so far, or are you just asking for someone to code the solution on your behalf?

 

[mrsbxl]

Can you share what does your code looks like so far, or are you just asking for someone to code the solution on your behalf?

Sure. At the moment I'm having issues with a few blocks of my code. The full concept is above, but what isn't working for me now is the data validation check. When I test it by entering valid data, I still get an error message that the validation failed, which should only be with incorrect data. On my "Item List" tab I have the Item Number list in column B and Item Descriptions in column C, and on the "Item by Bin" tab I have the Item Number list in Column B and the corresponding bin in Column C. Code below:




Private Sub btnSubmit_Click()

Dim confirmation As VbMsgBoxResult
confirmation = MsgBox("Are you sure you want to submit the data?", vbYesNo, "Confirmation")

If confirmation = vbYes Then

Dim itemNum As String
Dim binNum As String

itemNum = frmUserForm.Controls("txtItemNum").Text
binNum = frmUserForm.Controls("txtBinNum").Text

Dim itemByBin As Worksheet
Set itemByBin = ThisWorkbook.Sheets("Item by Bin")

Dim itemRange As Range
Set itemRange = itemByBin.Range("B:C").Find(What:=itemNum, LookIn:=xlValues, LookAt:=xlWhole)

If itemRange Is Nothing Or itemRange.Offset(0, 2).Value <> binNum Then
MsgBox "Data validation failed. Please check Item Number and Bin Number.", vbExclamation, "Error"
Exit Sub
End If


Dim dbSheet As Worksheet
Set dbSheet = ThisWorkbook.Sheets("Summary Table")

Dim lastRow As Long
lastRow = dbSheet.Cells(dbSheet.Rows.Count, 1).End(xlUp).Row


Dim existingRecord As Range
Set existingRecord = dbSheet.Range("B:B").Find(What:=itemNum, LookIn:=xlValues, LookAt:=xlWhole)

If existingRecord Is Nothing Then

Dim serialNumber As Long
If lastRow = 1 Then
serialNumber = 10001
Else
serialNumber = dbSheet.Cells(lastRow, 1).Value + 1
End If


With dbSheet
.Cells(lastRow + 1, 1).Value = serialNumber
.Cells(lastRow + 1, 2).Value = itemNum
.Cells(lastRow + 1, 3).Value = frmUserForm.Controls("txtItemDesc").Text
.Cells(lastRow + 1, 4).Value = binNum
.Cells(lastRow + 1, 6).Value = frmUserForm.Controls("txtCount1").Text
.Cells(lastRow + 1, 7).Value = frmUserForm.Controls("txtTeam1").Text
.Cells(lastRow + 1, 8).Value = frmUserForm.Controls("txtCount2").Text
.Cells(lastRow + 1, 9).Value = frmUserForm.Controls("txtTeam2").Text
.Cells(lastRow + 1, 13).Value = frmUserForm.Controls("txtCount3").Text
.Cells(lastRow + 1, 14).Value = frmUserForm.Controls("txtTeam3").Text
.Cells(lastRow + 1, 19).Value = frmUserForm.Controls("txtCount4").Text
.Cells(lastRow + 1, 20).Value = frmUserForm.Controls("txtTeam4").Text
End With
Else
MsgBox "Duplicate entry. Please use the Modify button to update the record.", vbExclamation, "Error"
Exit Sub
End If

MsgBox "Data transferred to the Summary Table sheet.", vbInformation, "Success"

Initialize
End If
End Sub