Calling add-in macro(with variable) gives '1004' error

  • (XL2007, Vista) I am creating a new worklbook from a template. One of the sheets in the new workbook has a selection of three cells as "buttons". The Worksheet_SelectionChange event is supposed to call a macro in an add-in and pass the Target.Address as a string variable.

    I did just have the three cells call three separate macros from the add-in, without passing any variables, and those worked fine. But then it dawned on me that I couldn't add any new functionality in files created on that template because I couldn't get to the VBE to add a call to a new macro. So if I just call a macro in the add-in that figured out what to do based on the Target.Address, then I could update the add-in as needed with new functions.

    Now, though, calling the add-in macro gets me a 1004 error:
    "Cannont run the macro 'MyAddIn.xlam!doTarget(strAddress)'. The macro may not be available in this workbook, or all macros may be disabled."

    This error occurs no matter which cell I select on the sheet. The code stops on the "Application.Run" line.

    Any help on fixing this?
    Ed

    In worksheet:

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim strAddress As String
    strAddress = Target.Address
    Application.Run "MyAddIn.xlam!doTarget(strAddress)"
    End Sub



    In add-in:

    Code
    Sub doTarget(strAddress As String)
    Select Case strAddress
      Case "$H$23:$J$24"
        Call doRows
      Case "$H$27:$J$28"
        Call doNames
      Case "$H$31:$J$32"
        Call doFreeze
    End Select
    End Sub
  • Re: Calling add-in macro(with variable) gives '1004' error


    Try

    Code
    Application.Run("MyAddIn.xlam!doTarget(strAddress)")


    Attila

  • Re: Calling add-in macro(with variable) gives '1004' error


    Probably because strAddress is contained inside the quote marks.


    Try changing it to:

    Code
    ...dotarget(" & chr$(34) & strAddress & Chr$(34) &")"


    Please excuse. breity of reply... mobile phone.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!