Date perfection with Excel tips and tricks - Step 3

Welcome to another Excel video! This is Step 3 on how Excel can help with dates and how to build dynamic calendars. Our step-by-step guide continues with linked pictures with easy to follow explanation of the Visual Basic code to automate your spreadsheets for your users.
#excel #exceltricks #neurodiversity #microsoft #fyp #dates #datefunction #excelformula #visualbasic #calendar

Delivered by @Changetipsandtools

You can get a copy of the full template on the Change Tips and Tools Patreon which will really help the channel: https://www.patreon.com/ChangeTipsandTools/shop/how-i-help-my-neurodiverse-kids-with-227125

The Daisy Helper Playlist:https://www.youtube.com/playlist?list=PLHo4KrxeEewnB2M9S13ahnx27NRCLVLBu

Supporting file from where we left off in Step 2 can be found here: https://drive.google.com/drive/folders/16INs4WXF9AVTNLN9AI7_vJRvf5j6jce4?usp=sharing

Link to Step 2 video here: https://youtu.be/x0r7E8JRAEM?si=uw3DfAO7gYCRbHfD

This video contains:
00:00 - Intro
00:22 - Overview of what we need to do
00:55 - Remove sheet grid lines and make the source calendars the same size
01:48 - Use linked picture functionality for dynamic calendar visuals
04:54 - How to stop pictures and shapes resizing with cells
05:44 - How to give shapes and pictures custom names
07:13 - Create a button with a shape
08:52 - Lets write some Visual Basic together line-by-line
09:14 - How to open the Visual Basic code window
09:24 - Create a Visual Basic Module
09:49 - Write a procedure in our Visual Basic Module
10:34 - Amend text on the "ViewCalendar" button
12:37 - Declare Variables
14:05 - Workbook protection, Sheet protection and Screen updating settings with Visual Basic
16:56 - Check our button status to determine our next step with an IF Statement
19:37 - Check the active cell's row and column to decide an action with nested IF statements
23:46 - How to step through code and test it in the code window
24:15 - How to check the cell value is a valid date and write action if it has a date accordingly
26:17 - Oh side note, watch for this ERROR!!
29:02 - How to handle a cell that does not have a valid date by using our Variables
31:15 - How to monitor the values assigned to variables in real-time using the Watch Window
37:31 - Add an Else statement on the column check
38:16 - Add an Else statement for the row check
38:59 - Make our calendar visuals visible
40:49 - Change the button text to "Hide Calendars"
41:30 - Add an Else statement to the button check to hide our calendar visuals and change button text accordingly
42:29 - Reapply protection and switch on the screen updating
44:11 - Debug and Compile code to check for errors
44:42 - Assign our code (Macro) to our button

Тэги:

#Data_Visualization #Excel #Excel_VBA #Donut_Chart #Excel_Macros #Excel_Tutorial #Excel_Tricks #Excel_Charts #Excel_Tips #Excel_Formulas #Excel_Design #Clock_Tutorial #Excel_Hacks #Excel_Techniques #neurodiversity #change_tips_and_tools #fyp #date_function_excel_点用 #date_in_excel_formula #add_dates_in_excel #date_format_in_excel #calendars_in_excel #dynamic_calendar #excel_tips #excel #linked_picture_in_excel #visual_basic #excel_tutorial

Скачать видео