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
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Make image track as the date changes on a calender
0
Discussion
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.
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: 4889) Apr 10, '18 at 9:56 pm
How would you right that code?
bdalfrey23 (rep: 2) Apr 11, '18 at 9:34 am
Answers
0
To make your life easier, why not just use conditional formatting to highlight the cell in row 3 according to the current date?
0
You asked how I would write the code. You may suggest the following steps to your programmer.
- Find the week in row 3 into which TODAY()'s date falls.
- 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.) - 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.
- 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.