Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Option Explicit Private Sub Worksheet_Activate() UpdateValues 'UpdateStatisticsValues End Sub Sub UpdateValues() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Statistics") Dim shpTotalOrders As ShapeRange Dim shpTotalCustomers As ShapeRange Set shpTotalOrders = ws.Shapes.Range(Array("TotalOrders")) Set shpTotalCustomers = ws.Shapes.Range(Array("TotalCustomers")) Dim lngOrders As Long Dim intCustomers As Integer 'Doesn't work? On Error GoTo Hell lngOrders = Application.WorksheetFunction.Count(Range("Orders[ID]")) intCustomers = Application.WorksheetFunction.CountIf(Range("Customers[Orders]"), ">0") shpTotalOrders.TextFrame2.TextRange.Characters.Text = lngOrders shpTotalCustomers.TextFrame2.TextRange.Characters.Text = intCustomers Set ws = Nothing Set shpTotalOrders = Nothing Set shpTotalCustomers = Nothing Hell: MsgBox "There was a problem." End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.