Make image track as the date changes on a calender


i am trying to make the image i added to the spreadsheet move on its own according to todays date rather than having to manually move it everyday... is there a way to do that... i have attached a screen shot to show the "TODAY" line that i move manually



This would require VBA. Not difficult but requiring the following information.
1. The precise width of each daily column.
2. The name and nature of the shape containing the word "TODAY"
3. The exact width of that shape (or is it an ActiveX control?)
4. Is there a Workbook_Open procedure in the code of this workbook? If so, post a copy of it.
I am angling toward adding code to your project which runs automatically when the workbook is opened, activates the worksheet "OPS White Board" and moves the shape to the day's correct position.
Variatus (rep: 2504) Apr 10, '18 at 9:56 pm
How would you right that code?
bdalfrey23 (rep: 2) Apr 11, '18 at 9:34 am
Add to Discussion



To make your life easier, why not just use conditional formatting to highlight the cell in row 3 according to the current date?



The cells in row 3 are merged and comprise a week.
Variatus (rep: 2504) Apr 10, '18 at 9:47 pm
Ah good point, I was a bit confused about it's setup from the image.
don (rep: 1576) Apr 11, '18 at 3:04 am
Add to Discussion

You asked how I would write the code. You may suggest the following steps to your programmer.

  1. Find the week in row 3 into which TODAY()'s date falls.
  2. Find the column within that week based on TODAY()'s weekday.
    (Yes, you are right, I should have also asked which weekday is the first in each of your worksheet's weeks.)
  3. Calculate the required position of the shape's top left corner relative to the top left corner of cell in row 1 of the column identified by the date and the size of the shape itself.
  4. Set the shape's Top and Left properties accordingly.
  • Use the Workbook_Open event to automate the process. If your workbook already has other code running at that time integrate the above into the existing.
  • In order to allow the above code to run the sheet "OPS White Board" must be active. Therefore the Workbook_Open event procedure must be made to activate it.

Answer the Question

You must create an account to use the forum. Create an Account or Login