Worksheet change macro handling error - Newbie

  • Hi,
    I'm trying to create a macro which calculates the Tax rate based on following criteria - a) Determine whether it is intra-state or inter-state tax, based on comparison of code value in certain cells, b) Based on tax rate selected from a drop down list, update rate column for either intra-state tax or inter-state tax. Further this macro should run, whenever there is a change in selection of drop-down list value, provided the quantity and rate cell values are not empty. Here is the macro code:



    Additionally, I am trying to call the macro on worksheet_change event, like this:


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not Intersect(Range(Target.Address), Range("K18:K31")) Is Nothing Then
    3. Application.EnableEvents = False
    4. Application.Run "mSetGSTRate"
    5. Application.EnableEvents = True
    6. End If
    7. End Sub


    I am having mixed luck with the macro. It does not update every time the value is changed in the drop down list cell, which falls within the range K18:K31. Moreover, the worksheet_change event throws up an error - "Error 1004 - Cannot run macro 'mSetGSTRate' Either the macro may not be available in this workbook or all macros may be disabled."


    The macro is saved as module with code name same as procedure name in the modules section. The worksheet_change code is saved under the worksheet code section, which has been given code name shInv1


    Please bear for any silly mistakes or newbie errors. Thanks for help in advance.


    Ajay Dand.

  • Re: Worksheet change macro handling error - Newbie


    Hello,


    As an initial remark ... you can modify your 'Run' instruction as follows :


    Code
    1. Application.Run ("mSetGSTRate")


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Worksheet change macro handling error - Newbie


    Attach an example workbook. This might work


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not Intersect(Range(Target.Address), Range("K18:K31")) Is Nothing Then
    3. Application.EnableEvents = False
    4. mSetGSTRate
    5. Application.EnableEvents = True
    6. End If
    7. End Sub


    It might be better to change the Procedure name to not be the same as the Module name. I seem to recall this can cause problems.

  • Re: Worksheet change macro handling error - Newbie


    Hi RoyUK,


    Thanks for your response. It worked, but not exactly as you suggested. It was throwing error of No such Function or Sub, without the Run. However, when I changed it to Run ("mSetGSTRate"), and also made the code name different, it worked. So for the moment, it is working. Thanks again for your suggestions.

  • Re: Worksheet change macro handling error - Newbie


    Quote from ajaydand65;793875

    and also made the code name different, it worked


    That was the key change. If the module name and procedure name are the same, you need to specify both:


    Code
    1. Application.Run "mSetGSTRate.mSetGSTRate"


    Note that brackets are not required there, and shouldn't really be used as you are not returning a value from a function.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why