Can scripting dictionary alert if certain criteria are met?

[Lil Stinker]

Hello @DanteAmor
You provided this code cross referencing serial numbers entered on one sheet with serial numbers listed on another Inventory sheet to me back in June. Provided the numbers match, it updates the Inventory sheet to indicate the number as Out, the Location and Date Out. It also notifies the user if any of the entered serial numbers do not exist anywhere on the Inventory sheet. Everything is working well. Though, now I am wondering if it is possible to alert the user of the current status of a serial number after it is entered in addition to whether it exists or not? For example, if any of the serial numbers entered on RAreceipt (sheet1) are already showing Out on the Inventory (sheet2), have a MsgBox pop up listing those corresponding serial numbers as out and prevent anything being written to the Inventory sheet for those numbers? Basically, if anything on the Inventory sheet is listed as Out, I want to prevent the Inventory sheet from being overwritten with any incorrect data.

I tried playing with If cad = "Out" then MsgBox but that didn't get me anywhere.

Sub logINV_OUT2()
'''code courtesy of @DanteAmor via MrExcel'''

   Dim a As Variant, b As Variant, c As Variant
   Dim dic As Object, rng As Range, f As Range
   Dim i&, j&, wRow&
   Dim dtOUT As Date, dtRTN As Date, loc As String, cad As String, sNUM As String
   Dim RAnum As Long
  
   Set dic = CreateObject("Scripting.Dictionary")
   Set f = RAreceipt.Range("B:B").Find("SERIAL NUMBERS", , xlValues, xlWhole, xlByRows, xlPrevious, False)
      
   If RAreceipt.Range("AD9").Value = True Then
      a = RAreceipt.Range("B24:O" & f.Row - 1).Value
   ElseIf RAreceipt.Range("AD10").Value = True Then
      a = RAreceipt.Range("P24:Q" & f.Row - 1).Value
   ElseIf RAreceipt.Range("AD11").Value = True Then
      a = RAreceipt.Range("R24:S" & f.Row - 1).Value
   End If
  
   b = Inventory.Range("B2:B" & Inventory.Range("B" & Rows.Count).End(3).Row).Value
   c = Inventory.Range("F2:P" & Inventory.Range("B" & Rows.Count).End(3).Row).Value
   Set rng = Inventory.Range("G1")
  
   dtOUT = RAreceipt.Range("B12").Value
   dtRTN = RAreceipt.Range("B15").Value
   loc = RAreceipt.Range("C8").Value
   RAnum = RAreceipt.Range("Q4").Value
  
   For i = 1 To UBound(b, 1)
      dic(Format(b(i, 1), "@")) = i
   Next
  
   For i = 1 To UBound(a, 1)
      For j = 1 To UBound(a, 2)
      If a(i, j) <> "" Then
         sNUM = Format(a(i, j), "@")
         If dic.exists(sNUM) Then
            wRow = dic(sNUM)
            c(wRow, 1) = "OUT"
            c(wRow, 2) = loc
            c(wRow, 3) = dtOUT
            c(wRow, 4) = dtRTN
            c(wRow, 5) = RAnum
            c(wRow, 6) = "" 'clear previous rtn numbers for items going out
            c(wRow, 11) = "N" 'log new record
            Set rng = Union(rng, Inventory.Range("G" & wRow + 1))
         Else
            cad = cad & a(i, j) & vbCr
         End If
      End If
      Next
   Next
  
   Inventory.Range("F2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
   If cad <> "" Then
      MsgBox "The following equipment was not found in inventory:" & vbNewLine & cad, vbExclamation, "NOT FOUND!"
   ElseIf cad = "" Then
      MsgBox "Existing equipment updated", 0, "Success!"
   End If

End Sub

Here's a small sample of the Inventory on Sheet2.

ItemID	Barcode	DeviceNumber	Model	Description	Status	Location	DateOut	ReturnDate	RAnumber	RTNnumber	LastReturn	InRepair	Notes	IntakeDate	New Rec
1	011	018TEQ9190	Motorola Radius	UHF 16 Channel Mobile Radio	OUT	Anywhere	11/30/2023	2/1/2024	10010					3/13/2023
2	016	018TEQ8870	Motorola Radius	UHF 16 Channel Mobile Radio	IN	WAREHOUSE								4/3/2023
3	017	018TEQ8821	Motorola Radius	UHF 16 Channel Mobile Radio	OUT	Somewhere Out There	6/2/2023	10/10/2023	10008					4/26/2023
4	023	018TEQ9253	Motorola Radius	UHF 16 Channel Mobile Radio	IN	WAREHOUSE								5/5/2023
5	027	018TEQ9279	Motorola Radius	UHF 16 Channel Mobile Radio	IN	WAREHOUSE								6/7/2023
6	029	018teQ9140	Motorola Radius	UHF 16 Channel Mobile Radio	IN	WAREHOUSE			10019	1	12/18/2023			3/13/2023	N
7	030	018TEQ8659	Motorola Radius	UHF 16 Channel Mobile Radio	IN	WAREHOUSE			10007	1	12/19/2023			4/3/2023
8	052	018TEQ8816	Motorola Radius	UHF 16 Channel Mobile Radio	IN	WAREHOUSE								4/3/2023
9	061	0180000000	Motorola Radius	UHF 16 Channel Mobile Radio	OUT	Chicago	12/13/2023	1/5/2024	10013		12/12/2023			5/2/2023	N
10	071	018TEQ9178	Motorola Radius	UHF 16 Channel Mobile Radio	IN	WAREHOUSE								3/13/2023

 

[Lil Stinker]

Looks like DanteAmor is busy. Can any other VBA veterans out there tell me by looking at the code above if there is a way to add in a MsgBox stop when the matching barcode number on the Inventory sheet already indicates "OUT" or "DMG" before the code overwrites the data on the Inventory sheet?

I have no formal training in VBA and only have a vague idea of what the code is doing. Any suggestions would be appreciated.

Thanks in advance!