Call Macro based on Named Range with Worksheet_Change event

[Charliebravo]

Hi there, I would like to trigger a macro with the macro name derived from a named range of the cell changed during Worksheet_Change event. My code is not working as the error mesage say that the Macro is not available in workbook or macros is not enabled. Please see code below:

Named range of changed cell: Disc_GrowthRate1_EndDate

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim t As String
Let t = Target.Name.Name

Application.Run t

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Exampe of Macro:

Sub Disc_GrowthRate1_EndDate()

Range("Disc_GrowthRate1_EndDate").Value = WorksheetFunction.EoMonth(Range("Disc_GrowthRate1_EndDate").Value, 0)

Range("Disc_GrowthRate1_EndAge").Formula = "=LET(FY,DATEDIF(EOMONTH(C_DOB,0)+1,Disc_GrowthRate1_EndDate+1,""Y""),M,DATEDIF(EOMONTH(C_DOB,0)+1,Disc_GrowthRate1_EndDate+1,""M""),FY+(M-FY*12)/100)"
Range("Disc_GrowthRate1_EndYears").Formula = "=LET(FY,DATEDIF(Disc_GrowthRate1_StartDate,Disc_GrowthRate1_EndDate+1,""Y""),M,DATEDIF(Disc_GrowthRate1_StartDate,Disc_GrowthRate1_EndDate+1,""M""),FY+(M-FY*12)/100)"
Range("Disc_GrowthRate1_EndMonths").Formula = "=DATEDIF(Disc_GrowthRate1_StartDate,Disc_GrowthRate1_EndDate+1,""M"")"

Range("Disc_GrowthRate1_EndDate").Borders.Color = RGB(226, 39, 38)
Range("Disc_GrowthRate1_EndAge,Disc_GrowthRate1_EndMonths,Disc_GrowthRate1_EndYears").Borders.Color = RGB(217, 217, 217)

Call Disc_GrowthRate_Macro3


End Sub


Hope anyone can help me. Thanks

 

[Joe4]

Welcome to the Board!

Through a Google Search, I found other posts on this topic, but could not find anything that seemed to work.
But I do have a question.

One potential issue that I see if that you are not limiting the range that the "Worksheet_Change" event procedure code should fire on. So that code would fire on any change to any cell, not just the one you have named "Disc_GrowthRate1_EndDate". That will probably result in errors, as you update other cells.

Also, maybe we can investigate other ways of accomplishing what you are ultimately trying to do, but first we need to answer a few questions:
- Is there some reason why you want to run it in this fashion, as opposed to calling the procedure directly in your code?
- Just how many named ranges do you have that are also the names of procedures?
- Do the location of those named ranges ever change?

 

[Charliebravo]

Hi Joe,

Thank you for your reply.

Some background: I'm creating a cash flow tool for investment and retirement planning with a lot of variables to cater for all possible scenarios.
There are groups of inputs related to each other, so there is a relationship between them. For example: You can either insert the retirement date, retirement age, years after start date or months after start date which update in line the related cells with what you have inserted. I have loads of these type of inter dependent cells.

The input data is then used in a cash flow spreadsheet to do future projections in order to illustrate the outcome in a chart.

I had all my code in a Worksheet_Change Event limiting each named range followed by its code. This procedure ended up giving me an error: Procedure is long.

My thought process was to add the code in a Macro_Sub Procedure and have it triggered once the named cell changed. This is working but I have a lot of limited cells and I will have to add more in future which means I will run into the same error. So I was looking to reduce the coding under Worksheet_Change by having a catch all event. If you change a cell which is not a named range and/or have no code, this will run into an error and with a error handler I can then Exit Sub. So this will only work with named ranges where there is corresponding sub procedures.

Maybe there is a more elegant or optimal way for dealing with the issue.

I hope the above make sense.