Update values in a shape

Question

https://drive.google.com/file/d/1Sb6G978NkR9tWjFmQmg9NDfpj94jUhm9/view?usp=drivesdk

I have inserted a sample file. What I would like to do is when I add product data with the userform, then I want the value that has been added to the table to be subtracted from my total inventory in the correct shape. I am dealing with shapes here. The activex textboxes are just to add inventory values. I'm getting some kind of error when I use the form, but when I use the error handler than it works fine. I would like to know if anybody could debug this for me and let me know what I missed. The bug slows down my real workbook that I'm working on and thought I might share a sample file on here.

Thanks.

Answer 1

It is suggested to put your code in worksheet change instead of workbooksheetchange.

===============================

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WheatInv As Long

Dim BarleyInv As Long

Dim CanolaInv As Long

Dim OatsInv As Long

Dim PeasInv As Long

Dim sh1 As Worksheet

Dim sh2 As Worksheet

Dim n As Long

Set sh1 = ThisWorkbook.Sheets("Inventory")

Set sh2 = ThisWorkbook.Sheets("Grain Data")

n = sh2.Range("c" & Application.Rows.Count).End(xlUp).Row

WheatInv = sh1.Shapes("Rectangle 1").TextFrame2.TextRange.Text

BarleyInv = sh1.Shapes("Rectangle 2").TextFrame2.TextRange.Text

CanolaInv = sh1.Shapes("Rectangle 3").TextFrame2.TextRange.Text

OatsInv = sh1.Shapes("Rectangle 4").TextFrame2.TextRange.Text

PeasInv = sh1.Shapes("Rectangle 5").TextFrame2.TextRange.Text

'On Error GoTo errhandler

sh2.Activate

If Not Intersect(Target, sh2.Range("B1:B100")) Is Nothing Then

  Select Case sh2.Cells(n, 1) 

    Case "Wheat" 

      WheatInv = WheatInv - sh2.Cells(n, 2) 

      sh1.Shapes("Rectangle 1").TextFrame2.TextRange.Text = WheatInv 

    Case "Barley" 

      BarleyInv = BarleyInv - sh2.Cells(n, 2) 

      sh1.Shapes("Rectangle 2").TextFrame2.TextRange.Text = BarleyInv 

    Case "Canola" 

      CanolaInv = CanolaInv - sh2.Cells(n, 2) 

       MsgBox CanolaInv 

      sh1.Shapes("Rectangle 3").TextFrame2.TextRange.Text = CanolaInv 

    Case "Oats" 

      OatsInv = OatsInv - sh2.Cells(n, 2) 

     sh1.Shapes("Rectangle 4").TextFrame2.TextRange.Text = OatsInv 

    Case "Peas" 

      PeasInv = PeasInv - sh2.Cells(n, 2) 

      sh1.Shapes("Rectangle 5").TextFrame2.TextRange.Text = PeasInv 

   End Select 

End If

sh1.Activate

End Sub

===============================