OmegaPrime
New Member
- Joined
- Jun 16, 2015
- Messages
- 1
Hello all
Here is my situation, I am constructing a roster and the format is thus: Sheet1 is MENU and Sheets2-13 are calendar months. Each month is set out in 5 one week blocks (Monday-Sunday) so the days are static as this format for the days best suits the end user.
Currently I am using 2 arguments in my date formula:
1: a static cell containing 01/01/1900 and
2: a spin box that the years 2014-2040
and the formula is thus: =DATE(MENU!$D$3,MENU!$C$3,-2)
So the end of the formula is variable and is changed from 1 through to -6 so that the 1st of Jan falls to its correct day e.g. this year its on Thursday and next year it will be on Friday.
What i want is to remove the need for the end user to have to update that variable every year and have the formula do it automatically based on the year being being changed.
My second problem is my menu uses buttons constructed from shapes as I would prefer to have more design control over them. So I need each button to go to its corresponding sheet bearing in mind that the final delivered project will have the sheets hidden. I have found a few ideas for making this work but none seemed to work for me. I am pretty new to this so maybe I just did not understand properly the VBA code offered in these solutions.
Thanks in advance for any and all help!
Here is my situation, I am constructing a roster and the format is thus: Sheet1 is MENU and Sheets2-13 are calendar months. Each month is set out in 5 one week blocks (Monday-Sunday) so the days are static as this format for the days best suits the end user.
Currently I am using 2 arguments in my date formula:
1: a static cell containing 01/01/1900 and
2: a spin box that the years 2014-2040
and the formula is thus: =DATE(MENU!$D$3,MENU!$C$3,-2)
So the end of the formula is variable and is changed from 1 through to -6 so that the 1st of Jan falls to its correct day e.g. this year its on Thursday and next year it will be on Friday.
What i want is to remove the need for the end user to have to update that variable every year and have the formula do it automatically based on the year being being changed.
My second problem is my menu uses buttons constructed from shapes as I would prefer to have more design control over them. So I need each button to go to its corresponding sheet bearing in mind that the final delivered project will have the sheets hidden. I have found a few ideas for making this work but none seemed to work for me. I am pretty new to this so maybe I just did not understand properly the VBA code offered in these solutions.
Thanks in advance for any and all help!
