My macros should do the following. When you enter a number, it should bring up msgbox that says sure right qty and part used yes/no, if yes then recalculates, (auto calc is off)
Here is my excel macros i for used parts and one for recieved
Code: Select all
Private Sub Worksheet_change(ByVal Target As Range)
'used parts
x = 9
y = 7
Do
z = Worksheets("sheet1").Cells(x, y)
x = x + 1
Loop Until z > 0 Or x = 3500
If z > 0 Then
currentitem = Worksheets("sheet1").Cells(x - 1, y - 3)
currentqty = Worksheets("sheet1").Cells(x - 1, y)
currentdesc = Worksheets("sheet1").Cells(x - 1, y - 2)
If IsNumeric(currentqty) = True Then
response = MsgBox("Did you use (" & currentqty & ") MANF. # " & currentitem & vbCrLf & vbCrLf & currentdesc & "?", vbYesNo)
If response = vbYes Then
Calculate
Worksheets("sheet1").Cells(x - 1, y) = ""
response = MsgBox("You have successfully updated your inventory list. Do you need to Use/Recieve any more items?", vbYesNo)
If response = vbNo Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End If
If response = vbNo Then
Worksheets("sheet1").Cells(x - 1, y) = ""
End If
Else
MsgBox "You must enter a number!!", vbOKOnly
Worksheets("sheet1").Cells(x - 1, y) = ""
End If
End If
'recieved parts
x = 9
y = 9
Do
z = Worksheets("sheet1").Cells(x, y)
x = x + 1
Loop Until z > 0 Or x = 3500
If z > 0 Then
currentitem = Worksheets("sheet1").Cells(x - 1, y - 5)
currentqty = Worksheets("sheet1").Cells(x - 1, y)
currentdesc = Worksheets("sheet1").Cells(x - 1, y - 4)
If IsNumeric(currentqty) = True Then
response = MsgBox("Did you recieve (" & currentqty & ") MANF. # " & currentitem & vbCrLf & vbCrLf & currentdesc & "?", vbYesNo)
If response = vbYes Then
Calculate
Worksheets("sheet1").Cells(x - 1, y) = ""
response = MsgBox("You have successfully updated your inventory list. Do you need to Use/Recieve any more items?", vbYesNo)
If response = vbNo Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End If
If response = vbNo Then
Worksheets("sheet1").Cells(x - 1, y) = ""
End If
Else
MsgBox "You must enter a number!!", vbOKOnly
Worksheets("sheet1").Cells(x - 1, y) = ""
End If
End If
End Sub