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

 

[AlphaFrog]

When you click on a shape, the macro runs and the Application.Caller command returns the name of the Object Shape that the macro was called from. It then uses that name in the code to reference the specific object that called the macro.

If you run the macro directly, there is no object that called the macro. Application.Caller does not know what shape you want and then errors.

 

[hrayani]

When you click on a shape, the macro runs and the Application.Caller command returns the name of the Object Shape that the macro was called from. It then uses that name in the code to reference the specific object that called the macro.

If you run the macro directly, there is no object that called the macro. Application.Caller does not know what shape you want and then errors.

Thanks for the reply

Do we have a workaround for this ??
can you pls come up with some idea - how to make it happen

One thing I am thinking about is to programmatically click a shape by a vba code (if possible)

 

[Alex Blakenburg]

If you can programmatically click a shape then you must know the shape name or number.
Change the line in Blue to put in the default shape name you want to use if no shape is triggering the code.
Note: the shape name with the suffix "_count" will also need to exist.

Private Sub ApplyFormulaToPendingTargets()

Dim shp As Shape

Dim color1 As Long, color2 As Long

Dim Name As String

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")
    
On Error Resume Next
If Not IsError(Application.Caller) Then
    Name = ActiveSheet.Shapes(Application.Caller).Name
Else
    Name = "Oval 1"
End If
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(Name).TextFrame.Characters.Font.Color = color1
ws.Shapes(Name).TextFrame.Characters.Font.Bold = True

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