1004 Run-time Error in Application.Caller when calling from another Control Form Object

[WilliamAnton]

Hello all. I used the following line of code...

Call Button_Differentials_ZeroOut

...to call another Button [Forms Control] and it blows up the Application.Caller in the "called" button with a "Run-time error '1004' - Unable to get the Buttons property of the Worksheet class. The specific line of code in the "called" button that the Debugger highlights is...

Set BUTTON = ActiveSheet.Buttons(Application.Caller)


I use the Application.Caller in the called button macro to help me create an anchor point (the cell reference/address for the button) from which I then create relative offsets so that any row from Step 1 through Step 31 on a Salary Schedule can find the same "constant value". This makes my 31 Step Schedule portable/dynamic; as such, when I copy the model and paste it under the current model I can create variations on the model based on alternative values for that constant. It's a comparison of costs modeling thing.

Maybe this explanation will help to better explain my overall goal. I wrote the Zero Out button which clears contents and locks fields in my variable/constants control matrix. This helps the user (me) to run new or different iterations through my model. It works well and I want to call the Zero Out button (macro) from another forms control macro. Really important feature would be to have the "relative addressing" in the Zero Out Button hold up. Having the "calling" control sending its parameters along to the Zero Out macro hurts me at this point, doesn't help me at this point.

Should I be trying the "Send Keys" function instead?

Thank you for your help.

 

[RoryA]

Without the code it's pretty hard to suggest your best option. Application.Caller won't work here though. I suspect your best bet might be to have a routine that requires a control as its parameter and call that from both buttons, passing Shapes(Application.caller) for the original button and specifying the control for the other button.

 

[Sektor]

Well, I had similar issue. The problem was.... with the name of the control! Rename it to something generic like "Btn1".

 

[Sektor]

Application.Caller won't work here though.

Why is that? Application.Caller will be still available thru the chain of calls:

Sub Proc1()
    Call Proc2
End Sub

Sub Proc2()
    MsgBox Application.Caller '<-- Here will be the name of a button
End Sub

 

[WilliamAnton]

Thank you Rory. This is the code in the Zero Out Button that I use to get the Button location references (which is critical to other offsets I note and use. I'm trying to call the Button that is linked to this macro while maintaining the original Application Call which gives me the cell references I need to maintain my other offsets. I hope that makes sense. I'm not sure how to better explain it.

Sub Button_Differentials_ZeroOut()


   '----------
    'MESSAGE ME THE  BUTTON USING APP CALLER
    '----------
    Dim MsgBoxToggle As String
    MsgBoxToggle = ActiveSheet.Range("admin_MessageSwitch")
    
    '----------
    Select Case TypeName(Application.Caller)
    Case "Range"
        AppCaller = Application.Caller.Address
    Case "String"
        AppCaller = Application.Caller
    Case "Error"
        AppCaller = "Error"
    Case Else
        AppCaller = "Unknown"
    End Select
    
    '----------
    If MsgBoxToggle = "ON" Then
        MsgBox "" _
        & Chr(10) & "Active Workbook is: " & ActiveWorkbook.Name _
        & Chr(10) & "Active Sheet is: " & ActiveSheet.Name _
        & Chr(10) & "" _
        & Chr(10) & "Application Caller = " & AppCaller _
        & Chr(10) & ""
    End If


 
   '----------
    'Find BUTTON LOCATION
    '----------
    Set BUTTON = ActiveSheet.Buttons(Application.Caller)
    Set BUTTON_address = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    
    '----------
    If MsgBoxToggle = "ON" Then
        MsgBox "" _
        & Chr(10) & "Object calling this macro: " & Application.Caller _
        & Chr(10) & "Object location: " & BUTTON_address.Address
    End If
    
    
    '----------
    'Set COLUMN & ROW based on above addressing
    '----------
    With BUTTON.TopLeftCell
    BUTTON_Choice = .Value
        If BUTTON_Choice = "ZeroOut" Then
            BUTTON.Caption = "ZeroOut"
            BUTTON_ChoiceText = "ZeroOut"
        Else
        If BUTTON_Choice = "ZeroOut" Then
            BUTTON.Caption = "ZeroOut"
            BUTTON_ChoiceText = "ZeroOut"
        End If
        End If


    b_Column = .Column
    b_Row = .Row
    End With
    
    '----------
    b_Column = b_Column + 0
    b_Row = b_Row + 0
    
    '----------
    b_ColumnLetter = Left(Cells(1, b_Column).Address(1, 0), InStr(1, Cells(1, b_Column).Address(1, 0), "$") - 1)
    b_Cell = b_ColumnLetter & CStr(b_Row)

 

[RoryA]

Why is that? Application.Caller will be still available thru the chain of calls:

Because it won't be the right button.

 

[RoryA]

You'd need to pass an optional button object:

        Sub Button_Differentials_ZeroOut(Optional btn as Button)


           '----------
            'MESSAGE ME THE  BUTTON USING APP CALLER
            '----------
            Dim MsgBoxToggle As String
            MsgBoxToggle = ActiveSheet.Range("admin_MessageSwitch")
            
            '----------
            Select Case TypeName(Application.Caller)
            Case "Range"
                AppCaller = Application.Caller.Address
            Case "String"
                AppCaller = Application.Caller
            Case "Error"
                AppCaller = "Error"
            Case Else
                AppCaller = "Unknown"
            End Select
            
            '----------
            If MsgBoxToggle = "ON" Then
                MsgBox "" _
                & Chr(10) & "Active Workbook is: " & ActiveWorkbook.Name _
                & Chr(10) & "Active Sheet is: " & ActiveSheet.Name _
                & Chr(10) & "" _
                & Chr(10) & "Application Caller = " & AppCaller _
                & Chr(10) & ""
            End If


         
           '----------
            'Find BUTTON LOCATION
            '----------
            If btn Is Nothing then
               Set BUTTON = ActiveSheet.Buttons(Application.Caller)
            Else
               Set BUTTON = btn
            end if
            Set BUTTON_address = BUTTON.TopLeftCell
            
            '----------
            If MsgBoxToggle = "ON" Then
                MsgBox "" _
                & Chr(10) & "Object calling this macro: " & Application.Caller _
                & Chr(10) & "Object location: " & BUTTON_address.Address
            End If
            
            
            '----------
            'Set COLUMN & ROW based on above addressing
            '----------
            With BUTTON.TopLeftCell
            BUTTON_Choice = .Value
                If BUTTON_Choice = "ZeroOut" Then
                    BUTTON.Caption = "ZeroOut"
                    BUTTON_ChoiceText = "ZeroOut"
                Else
                If BUTTON_Choice = "ZeroOut" Then
                    BUTTON.Caption = "ZeroOut"
                    BUTTON_ChoiceText = "ZeroOut"
                End If
                End If


            b_Column = .Column
            b_Row = .Row
            End With
            
            '----------
            b_Column = b_Column + 0
            b_Row = b_Row + 0
            
            '----------
            b_ColumnLetter = Left(Cells(1, b_Column).Address(1, 0), InStr(1, Cells(1, b_Column).Address(1, 0), "$") - 1)
            b_Cell = b_ColumnLetter & CStr(b_Row)

and then your other button code needs to call the routine and pass the relevant button as an argument.

 

[Sektor]

Because it won't be the right button.

Pardon? Could you give me some example?

 

[RoryA]

The question is about one button calling a macro as if it were another button. Application.Caller can't do that.

 

[Sektor]

The question is about one button calling a macro as if it were another button. Application.Caller can't do that.

Ah... I got it )