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:
- Private Sub mSetGSTRate()
- Const strFmSt As String = "$H$4"
- Const strToSt As String = "$D$13"
- ' VERIFY IF TO STATE GST CODE IS NOT EMPTY
- If Not shInv1.Range(strToSt) = "" Then
- ' VERIFY IF THE ACTIVE CELL FALLS WITHIN RANGE
- If Not Application.Intersect(ActiveCell, Range("K18", "K31")) Is Nothing Then
- ' IF TO & FROM STATE GST CODE ARE EQUAL SET CGST & SGST
- If Range(strFmSt).Value = Range(strToSt).Value Then
- ActiveCell.Offset(0, 1).Value = ActiveCell.Value * 0.5
- ActiveCell.Offset(0, 3).Value = ActiveCell.Value * 0.5
- ActiveCell.Offset(0, 5).Value = ""
- ' IF TO & FROM STATE GST CODE ARE NOT EQUAL SET IGST
- ActiveCell.Offset(0, 1).Value = ""
- ActiveCell.Offset(0, 3).Value = ""
- ActiveCell.Offset(0, 5).Value = ActiveCell.Value
- End If
- VBA.Interaction.MsgBox Prompt:="Active Cell out of Range"
- End If
- VBA.Interaction.MsgBox Prompt:="To State Code not present"
- End If
- End Sub
Additionally, I am trying to call the macro on worksheet_change event, like this:
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.