VBA Code Problem

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,531
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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)
 
Upvote 0
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.

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,224,755
Messages
6,180,751
Members
452,996
Latest member
nelsonsix66

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top