I would like to create a code on excel to mange my warehouse better

Hello Malique Moore,

Thank you for reaching out to Microsoft Q&A forum regarding your query on creating an Excel-based system for inventory tracking with barcode scanning. I've put together a straightforward guide to get you started, hope this will prove helpful to you.

First off, Setting Up Your Workbook

1. Inventory Table Basics:
   • On Sheet1, pop in these headers from A1: Item ID (A), Location (B), Status (C), Last Scan Time (D), and Notes (E).
   • Your actual data will kick off from row 2.
2. Adding Colors with Conditional Formatting:
   • Highlight your data area (like A2 to E1000).
   • Head to Format > Conditional Formatting > New Rule.
   • Set up these rules:
   • For green: Use the formula =$C2="In Location" and pick a green fill.
   • For blue: =$C2="In Use" with blue fill.
   • For red: =$C2="Awaiting Shipping" with red fill.
3. Getting Barcode Scanning Working with VBA:
   • First, turn on the Developer tab: Go to Excel > Preferences > Ribbon & Toolbar, and tick "Developer".
   • Add a new sheet called "Scan" and type "Scan here" in A1.
   • Open the VBA Editor via Developer > Visual Basic. Right-click the "Scan" sheet, choose View Code, and paste this code in:

          Private Sub Worksheet_Change(ByVal Target As Range)
              If Target.Address = "$A$1" Then
                  Dim scanValue As String
                  scanValue = Target.Value
                  
                  Dim invSheet As Worksheet
                  Set invSheet = ThisWorkbook.Sheets("Sheet1")
                  
                  Dim lastRow As Long
                  lastRow = invSheet.Cells(invSheet.Rows.Count, "A").End(xlUp).Row
                  
                  Dim itemRow As Long
                  itemRow = 0
                  
                  For i = 2 To lastRow
                      If invSheet.Cells(i, 1).Value = scanValue Then
                          itemRow = i
                          Exit For
                      End If
                  Next i
                  
                  If itemRow = 0 Then
                      lastRow = lastRow + 1
                      invSheet.Cells(lastRow, 1).Value = scanValue
                      invSheet.Cells(lastRow, 3).Value = "Awaiting Shipping"
                      invSheet.Cells(lastRow, 4).Value = Now
                      MsgBox "New item added: " & scanValue & " (Awaiting Shipping)"
                  Else
                      Dim nextScan As String
                      nextScan = InputBox("Scan location to check in, or leave blank and OK to check out.")
                      
                      If nextScan <> "" Then
                          invSheet.Cells(itemRow, 2).Value = nextScan
                          invSheet.Cells(itemRow, 3).Value = "In Location"
                          invSheet.Cells(itemRow, 4).Value = Now
                          MsgBox "Item " & scanValue & " checked into " & nextScan
                      Else
                          invSheet.Cells(itemRow, 2).Value = ""
                          invSheet.Cells(itemRow, 3).Value = "In Use"
                          invSheet.Cells(itemRow, 4).Value = Now
                          MsgBox "Item " & scanValue & " checked out (In Use)"
                      End If
                  End If
                  
                  Target.Value = ""
              End If
          End Sub
     

   • Save it as a macro-enabled file (.xlsm).
   • To use it: Just scan your barcodes right into A1 on the "Scan" sheet, and it'll guide you through check-ins and check-outs with pop-ups.

Then for Barcode Generation with Excel: Install a barcode font (e.g., Code 39), enter formulas like ="*" & A2 & "*", apply the font, and print. Pros: Free and easy. Cons: Less durable.

About switching from Mac to Linux, on your Mac, everything should run smoothly with VBA and all. However, Excel isn't native with Linux, so you might need to run it through a virtual machine like VirtualBox with Windows.

If you have any additional concern, feel free to comment below. I be more than happy to assist.  

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".      

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.