VBA set and call public value from formula

[Kamolga]

Hi,

I have different sheets that use a cell named "RngYear" to calculate dates. So when I change RngYear from 2018 to 2019, all dates are updated

=Date(RngYear,A1,B1)[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]

​

To do so, I have a dropdowdown menu in my ribbon that change the value Range("RngYear") with the value of the dropdown

What annoys me is to have that cell "RngYear" in my workbook. Instead, can I not write the value in the "backstage" (a constant "CstYear" that would change when I update the dropdown) and that I could call from a formula such as

=Date(CstYear,A1,B1)[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]

​

?

Thanks in advance for your help

 

[RoryA]

You don't actually need a cell. A defined name can point to a constant too.

 

[Kamolga]

Hey, I was not aware of that! It never crossed my mind! Thank you very much!

I change the cell reference with = 2018 in name manager and then only had to change my macro from

[COLOR=#222222][FONT=Verdana].Range("RngYear").value=2019[/FONT][/COLOR]

to

[COLOR=#222222][FONT=Verdana].Names("[/FONT][/COLOR]RngYear").value= 2019

It will save me the headaches of difficult protections from user. Thanks again!