VBA Code Problem

[hrayani]

Hello Friends,

The below VBA code is assigned to 6 shapes which runs perfectly fine when I click the shape

But When I try to run it without clicking the shape (directly from the VBA window with F5 key or calling with Application.Run in the worksheet activate event, then it states that the item with the specified name does not found with below line highlighted in yellow

Name = ActiveSheet.Shapes(Application.Caller).Name

Can somebody pls look into this as to what is going wrong here

Private Sub ApplyFormulaToPendingTargets()

Dim shp As shape

Dim color1 As Long, color2 As Long
color1 = RGB(255, 255, 255)
color2 = RGB(166, 166, 166)

Dim ws As Worksheet
' Assign the sheet to a variable
Set ws = Sheets("PENDING TARGETS")
    
Name = ActiveSheet.Shapes(Application.Caller).Name
Range("A1").Value = Name

ws.Range("B5:B1000").ClearContents
        
' Apply formulas
ws.Range("B5:B100").Formula = "=IFERROR(AGGREGATE(15,6,orders_ref/(((orders_design_receipt<>"""")*(" & Range("A1").Value & "=""""))*ISNA(MATCH(orders_ref,B4:B$4,0))),1),"""")"
                 
 For Each shp In ws.Shapes("pending_targets_category").GroupItems
        shp.TextFrame.Characters.Font.Color = color2
        shp.TextFrame.Characters.Font.Bold = False
    Next shp

ws.Shapes(Application.Caller).TextFrame.Characters.Font.Color = color1
ws.Shapes(Application.Caller).TextFrame.Characters.Font.Bold = True

ActiveSheet.Shapes(Application.Caller & "_count").TextFrame.Characters.Font.Color = color1
ActiveSheet.Shapes(Application.Caller & "_count").TextFrame.Characters.Font.Bold = True
End Sub

Regards,

Humayun

 

[MARK858]

When I run it without On Error Resume Next then it says "The item with the specified name wasn't found" with below part of the code highlighted in yellow
Name = ActiveSheet.Shapes(Application.Caller).Name

Strange, when I run it via being called by a worksheet.activate it goes to

Else
    Name = "photo_sample_actual_date"
End If

which it should do as a shape hasn't been clicked

I often see the screen flickering when screen updating is set to True at the end of the sub - this is why I always keep it to false

It only helps screen flickering when set to False during a procedure, when running code from a module, it is automatically set to True when the procedure ends