How To Add 3 Different Date Picker Calendars in Microsoft Excel [Free Download]

Aug 14, 2023

How To Add 3 Different Date Picker Calendars in Microsoft Excel [Free Download]

🔥รับเทมเพลตที่ดีที่สุด 250 แบบที่นี่► https://bit.ly/250WKBK-Desc
📢 รับคุณสมบัติของคุณเพิ่มใน PATREON ► https://bit.ly/PatreonDesc
👉ขายซอฟต์แวร์ EXCEL-Based ของคุณเอง► http://bit.ly/MentorProgram_YTDesc
👨‍💼เข้าร่วมคลาสมาสเตอร์แดชบอร์ดที่น่าทึ่งของฉันที่นี่► http://bit.ly/Dashboard_YTDesc
📣เรียนรู้ VBA จากพื้นฐาน ► https://bit.ly/VBACourse_Discount

ในการฝึกอบรมสัปดาห์นี้ ฉันจะแสดงวิธีเพิ่มปฏิทิน 3 ประเภทใน Excel

📣30-HOUR VBA COURSE► https://bit.ly/VBACourse_Discount

⬇ดาวน์โหลดเวิร์กบุ๊กสัปดาห์นี้ฟรี:
📩 การใช้อีเมลของคุณ: http://bit.ly/3_Calendars
💬 FACEBOOK MESSENGER : http://bit.ly/3Cal_FbDl

หลักสูตร EXCEL:
►DASHBOARD MASTERCLASS: http://bit.ly/Dashboard_YTDesc
►โปรแกรมการให้คำปรึกษา: http://bit.ly/MentorProgram_YTDesc
► หลักสูตร VBA สำหรับผู้เริ่มต้น: https://bit.ly/FreeVBACourse

ผลิตภัณฑ์ EXCEL:
►คู่มือทรัพยากร Excel ขั้นสูง: http://bit.ly/UERG_YTDesc
►250 สมุดงาน Excel: https://bit.ly/250WKBK-Desc
►Automate การเขียนโปรแกรม VBA ของคุณ: https://bit.ly/AutoMacroAddIn
►สร้างแดชบอร์ดและแผนภูมิในไม่กี่วินาที: https://bit.ly/Pine-Bi
►ปกป้องแอปพลิเคชัน Excel ของคุณจากการโจรกรรม: http://bit.ly/SecureExcel

🧐ค้นหาผลิตภัณฑ์และหลักสูตร Excel ที่ดีที่สุดได้ที่นี่: http://bit.ly/ExcelProducts

การประทับเวลา:

0:00 - บทนำ
0:27 - ภาพรวม
2:58 - กิจกรรมเปลี่ยนการเลือก
14:49 - การจัดรูปแบบตามเงื่อนไข
18:33 - เซลล์เดือนหน้า
19:40 - เซลล์วันนี้

เข้าร่วมชุมชนของเรา:
►กลุ่มเฟสบุ๊ค: http://bit.ly/groupexcel
►เฟสบุ๊คแฟนเพจ: https://bit.ly/EFF_FanPage

ติดตามฉันในบัญชีโซเชียลมีเดียของฉัน:
🐦ทวิตเตอร์:   / excel4freelance
🌏เว็บไซต์: https://www.ExcelForFreelancers.com
🔗LinkedIn:   / excelforfreelancers
👤เฟสบุ๊ค:   / excelforfreelancers
📸 อินสตาแกรม:   / excelforfreelancers
🎥 Rumble: https://rumble.com/c/ExcelForFreelancers
👤ชาวบ้าน: https://locals.com/member/ExcelFreela…
✉โทรเลข: https://t.me/ExcelForFreelancers
📲Gab: https://gab.com/ExcelForFreelancers

เกี่ยวกับเรา:
ฉันช่วยผู้ที่คลั่งไคล้ Microsoft Excel เปลี่ยนความหลงใหลในตัวเองให้เป็นผลกำไร เพื่อให้พวกเขาสามารถสร้างรายได้แบบพาสซีฟโดยใช้ทักษะของพวกเขาโดยไม่ต้องเสียเวลาแลกเงิน

ฉันชอบทำวิดีโอเหล่านี้ให้คุณทุกสัปดาห์ วิธีที่ยอดเยี่ยมในการสนับสนุน Excel For Freelancer เพื่อให้การฝึกอบรมเหล่านี้ฟรีทุกสัปดาห์คือการสนับสนุนเราผ่านหนึ่งในผลิตภัณฑ์ที่น่าทึ่งที่นำเสนอซึ่งจะช่วยให้คุณเพิ่มพูนทักษะ Excel และบรรลุความฝันของคุณ

รับการแจ้งเตือนทันทีด้วยวิดีโอการฝึกอบรมและสมุดงานใหม่ฟรีโดยสมัครรับข้อมูล

อัปโหลดวิดีโอการฝึกอบรมใหม่ทุกวันอังคาร
ขอบคุณมากสำหรับการสนับสนุนอย่างต่อเนื่องของคุณ,
Randy Austin

#exceltraining #excelcalendar #ฟรีปฏิทิน

Content

0 -> hello and welcome this is Randy with Excel for freelancers and today's training we're going to be

4.95 -> showing you three different excel pop-up calendars we have two different pop-ups one is a form Basin

12.51 -> this one is a shape base and we also have an in sell calendar that we're going to show you and I'm

17.94 -> looking forward to getting started thank you Oh righty let's get started today I'm going to show

28.56 -> you three great calendars two of which are going to be pop-up calendars one is going to be a forum

34.02 -> pop-up calendar and the other is going to be a shape based pop-up calendar and the third is going

42.48 -> to be an in sell calendar that you can use that's not pop-up all three are great and each one has

50.7 -> its own pros and cons in which we're going to go over so let's go ahead and start out with the form

56.64 -> pop-up calendar that is the first one here on the left this is a form base that's based on an Excel

63.09 -> form that's used through the VBA this one I did not create myself this is a very popular one that

68.7 -> can be downloaded and of course I am going to give you a copy as well so you can have that for you

74.7 -> this is a pretty simple calendar you just select the month select the year and it's pretty standard

82.11 -> and it's stable so I like that some of the pros are that it's very easy to set up it doesn't break

90.96 -> easy it's a secure so I like that and there's really no coding that's required very slightly

99.72 -> and we'll go ahead and let's go ahead and the VBA model and I'm gonna show you how that's done and

104.85 -> to open VBA we'll go over to the file and options to make sure you have the Developer tab and in the

113.34 -> customize ribbon you want to make sure that the Developer tab is selected there all right so we'll

120.3 -> go ahead and go to the developers tab visual basic or all f11 World War as well and so in once inside

128.34 -> the VBA you can see our form we have a calendar form here we'll double click on that and that is

135.42 -> the form that we're using in our first pop-up form pop-up calendar that is a form based calendar and

141.93 -> if you double click on somewhere inside you can also see all the code that comes along with it

147.3 -> we're not gonna go over that code in detail but it's basically what makes that calendar run and

154.14 -> I'll make sure that your copy has everything you need so you don't have to worry about that the

159.78 -> code that we're going to concern ourselves with is on sheet 1 and that's how to activate this

164.82 -> particular calendar and there's a lot of code on this not a lot but there's a good amount of code

169.02 -> on this but for this particular form Papa we're only going to be concerned with this particular

175.83 -> code right here and that's and that's on selection change right selection change okay that means that

186.21 -> anytime we select this cell e7 we want if we want to show that coliform and basically all that is is

194.43 -> the form name calendar form and then dot show okay so that means every time e 7 is selected

201.36 -> that calendar is going to be showing and then the code inside the calendar itself will hide

206.97 -> it once you select something so basically once we stop once we click on it that form shows and you

212.55 -> see that's a 7 so that's basically how this wart works and you can select the months the years on

218.76 -> this and it's pretty basic it's pretty solid and you can't the pros of this are it's very easy to

227.13 -> set up and to set that up you know to move it inside if you have a new workbook right and you

233.55 -> have no calendar form you can save it in another workbook and then open both workbooks and then

239.73 -> drag it drag it over into the new workbook you can also write take this calendar form right and you

246.39 -> can export it ok export it that into a folder of yours right once it's exported into you somewhere

252.48 -> you know that you know where it is you can then import it right so if I go to import right and

257.5 -> I go over to my to my where my files are and I look in my files I'll be able to find that

264.01 -> located in where I keep my my code library and you'll see it here under so I've got that saved

271.87 -> all my macros saved in a specific folder there so that I can just pull them out or forms and

277.27 -> so you can save that there as well and once you so once you pull it in all you need to do is then is

282.67 -> add this code to whatever sheet write work select whatever she'd go into selection change write and

289.45 -> then add this and that's pretty simple so I like that I like in this particular it's very easy to

294.52 -> set up very easy to run the downsides of it are a few it's got it's got a kind of an older and

299.32 -> look and feel you can't really change the colors too much you can a little bit through properties

304.36 -> but it doesn't look great we don't have a lot of options when it comes to colors and fades or

309.55 -> borders or things like that's somewhat limited so we're kind of stuck with this grey color here and

314.23 -> or some of the other color options are not that great and if it we were going to change you it

320.56 -> would be somewhat you know cumbersome because it does take a while and also another thing to

325.84 -> position this to position this particular pop-up we do have to go into the properties so back into

334.3 -> the VBA clicking on calendar form and then we're gonna click properties up here okay properties

341.98 -> here there's two items that change the location of this under properties one is left okay left

350.02 -> and you can see left we've set it at sixty if we increase this number it's going to move to

355.42 -> the right more so if we increase this to two let's say to sixty right and then we go ahead and click

363.07 -> it you know it's going to end up somewhere over to the right and basically the same idea is if

368.92 -> we also have top okay if we go down here under scroll down and we look at top it's the same

378.16 -> thing the more this number increases the lower it's gonna be alright we'll go ahead and change

383.2 -> this back to sixty and so that's how you position is some of the downsides let's say let's say you

390.58 -> want a calendar here and you want another pop up here in another pop up here I would probably just

396.61 -> used three calendars or you can use some of the coding positioning properties by cells there's a

402.67 -> few way to positions it but nothing nothing too simple on the positioning so that's one of the

407.59 -> cons of it so there's a few pros and cons with this one so that's it for the pop-up calendar on

415.12 -> the forum base next up we have the shape based pop-up calendar here okay and though I'll miss

422.02 -> a shape based pop-up calendar this one I did create myself whereas the form base I did not

427.63 -> create that this one I did created and this one I like it because it has the ability to change

433.93 -> the colors simply by clicking the properties so that's a very nice feature there's multiple

439.51 -> themes and colors that you can choose from to match your workbook another thing I like about

444.49 -> this is it's always going to pier we don't have to set the position of it it always appear below

451.42 -> the cell selected perfectly so I do like that feature and we also have the ability to move to

457.87 -> the month go through the months so that's kind of a nice feature and we can always end the selected

464.56 -> day we always have that the selected they will parent orange so we always know what day is there

468.82 -> so that's another feature some of the cons of this there is a little bit more of a complexity when it

475.09 -> comes to implementing this a little bit and that involves a little bit more VBA when you're when

482.08 -> you're moving it over but for this particular one I do have another workbook with just this shape

490.42 -> pop-up and this workbook contains instructions and I'll show that to you in a little bit it can

495.34 -> change construct instructions as well as a YouTube video that will go through how to implement it and

501.85 -> basically what we're doing is we're just going to copy over a sheet and we're gonna copy over

506.71 -> this shape and it's not that difficult but that is it's a little bit more complicated when when

513.1 -> we're copying over the code and let's go ahead and take a look at that code back into the VBA under

518.83 -> sheet 1 and that code is here and basically it's right here and all you have to do is copy this

526.48 -> code over from the workbook that I'm gonna give you over to your workbook and whatever she you

531.58 -> want and the big difference here is they're just really one change and that's this that's this if

536.56 -> not intersect hi seven okay all we need to do is change this cell to whatever cell you want

542.98 -> that calendar and if you have more than one you could let's say it was a I seven and j7 you could

548.11 -> do multiples two and the good thing about this one is if you have multiple cells that calendars

553.84 -> gonna that calendar is going to appear regardless of where so let's keep it at that let's see what

558.91 -> that looks like even though it's not really so we have J seven up here and even if we so

563.53 -> you see how we've included other ones it's gonna always appear right below the cell which is nice

570.04 -> and whereas the form-based we don't have that ability alright so so you can see how it's go

576.04 -> ahead and take those two out i just wanted to show you that how you can easily and quickly add it to

581.11 -> morse to add more cells in the calendar location will automatically appear below and I like that

586.03 -> about shape based it's very easy to position so that's one of the things that one of the issues

592.51 -> that you that that could be possibility is this is a shape based so if the [ __ ] is not protected it

598.45 -> could be deleted but I have written some code on this so we're if it does get deleted it gets

603.52 -> automatically I'm gonna go ahead and press the Delete key okay it's not there now it's gone it's

607.75 -> not it's off the sheet completely however if you click anywhere else you see that flash that flash

613.24 -> basically now it's there okay it's back and what what I've done is I've written some code and I've

619.51 -> included a hidden sheet generally this calendar pop-up sheet will be hidden automatically okay

624.52 -> this sheet gets automatically created and there's a backup there's a backup calendar here so if the

631.18 -> if if on the hidden sheet I'm sorry if on your sheet this shape gets deleted right or or for any

638.17 -> reason it's gonna automatically copy over that so that's kind of a nice feature with shape based we

642.34 -> have to add in more security because if she's not protected it could be do it so I've done

647.11 -> that another downside of it if you're using this yourself and your experience that's no problem but

652.31 -> this the shape-based does tend to get could get a little bit out of whack like that you know if

657.65 -> you're if you're she does not protect that I do recommend protecting it so that will help that so

665.51 -> and the protection is something you'll have to add because part of this is grouping and ungrouping is

670.97 -> so protection would need to be in the code and it's it's not very difficult but I can go over

676.61 -> that with you now but basically the idea is that we want to under the modules okay if you go into

685.31 -> the pop-up calendar I've added some notes here and you can see this is all the code and all you

692.24 -> have to do is copy this module over to workbook so it's very simple just a copy over there's no

697.19 -> don't have to worry about changing anything in this code so it's in that sense it's very very

701.9 -> easy and so also I've put some MUX put some notes in here of how what's going on whether it's hiding

711.74 -> it so you can look at it if you're interested in how this particular calendar works and so

718.82 -> that might be helpful for you as well all right so that is the date based pop-up on shapes so I

729.29 -> hope you like that one and lastly we have a really cool called in cell calendar and I developed this

736.28 -> one if you have the space in your sheet and this is based on cells so this doesn't move this is a

743.72 -> great way because it you can really have a lot of flexibility you can go previous month next

749 -> month you can select a month right you can select a year you can click on today's date so it's got

756.17 -> some really cool features and it's very easy to use and that's assuming that you have space in

761.24 -> your workbook right if you have the space to put this in it's great and I've used this on a couple

766.28 -> of my workbooks for scheduling where it where I do have the space so the pros of this would be it's

771.29 -> simple to use it's got a today button previous and next so it's very easy to use we also have the we

777.26 -> should also have a lot of ability because it's cell based we can we can customize the look and

781.37 -> feel of it any which way you like which is really nice and it's very easy to to look and also if you

789.95 -> protected then the the formulas won't be deleted so that's kind of nice when you do protect it if

796.94 -> you do that make sure when you protect it right you want to make sure you can select the lock

801.86 -> cells because if if these are protected and you cannot select the lock cells it won't work so

806.21 -> you want to make sure to select to be able to select lock cells so that's really important if

811.37 -> the sheets protected and that way there's actually three or four different ways of protecting this

817.25 -> one is had an instance where I did not want to I wanted to lock all the protective cells so let's

824.75 -> say let's say I had an issue I don't want people to click on their I don't you know so that means

829.73 -> this would be protected and I don't want them to click which means I had to lock this but I can't

834.41 -> do it for this cuz it's all based on the sheet so basically what I did is like an undo feature

838.97 -> using VBA and I'll go over that in another lesson but the idea is that there's many ways to protect

844.34 -> this so you don't have to worry you can ask me if there's other ways you'd like to protect it some

850.04 -> of the other pros are you have the ability to also in this particular I'm going to move this over so

857.6 -> you can see it in this instance I've blocked out son Sunday and Saturday as if they were workdays

864.05 -> but if you want to activate them or you can like if you're not working on Monday and Thursday you

869.06 -> could easily block that so that's kind of a nice little feature to gray out any non work days

874.49 -> or days that you don't want to be viewable this calendar is actually quite simple we can go ahead

881.9 -> and look at the conditional formatting it's based a lot on conditional formatting and let's put use

885.83 -> one of these and then we'll go into conditional formatting I'll explain a few of the rules that

891.65 -> we have and the first one is Wednesday okay Wednesday is false and basically what I've done

897.59 -> is I've assigned this Wednesday column here right here this Wednesday where the check mark is if

905.72 -> that is checked it's true if it's unchecked then that whole column will grayed out so basically

910.43 -> that's the way that we hide and unhide work days if they're working make them gray month this

917.42 -> formula month basically it's go ahead and look at that so we can look at that formula the month of

922.34 -> afore teen of is benign and I'll explain that to you and basically what I have done in this case

929.3 -> is I've added conditional formatting to the last two rows basically what I want is if this month

937.49 -> here September is not the same as the month here right I want to grade out so basically I want to

944.6 -> gray out the next month right so one of the things that's important to you you saw that that I'll go

950.81 -> back into condition for me so we can go back over that particular rule again as you'll see

958.31 -> in in this applies to Oh 14 through you 15 okay that's the last two rows okay and look one of

968.33 -> the important things is under under the Edit rule look how Oh 14 has no dollar signs okay it's not

975.11 -> fixed okay that's because I want this same rule to apply for every cell in this range because I

982.64 -> don't want to write this particular conditional formatting 14 different times so that means that

988.94 -> this is going to apply however benign benign and we'll go over what what's in benign benign is the

994.88 -> month number of September okay benign and I'll show you that is the month number so benign is

999.89 -> in this case it's nine okay so let's go ahead and scroll over and this particular calendar does use

1006.94 -> these particular formulas and remember benign here okay and that is match P 8 P 8 is September in our

1017.2 -> case and what are we matching we're matching the months and where are the months it's here months

1023.29 -> are here okay so basically it says I want to match September I'm going to use the match formula and

1028 -> I want to find out what position September is September's in the ninth position okay so all

1033.25 -> that match does and we have the same thing for a year we want to know what year what position is

1038.44 -> year in 2017 over here is in the second position of the years it's the second one and this is going

1045.55 -> to be helpful because when we use next and when we use previous we have to know what month is next

1052.43 -> right because if we get down to January right we don't you know we don't want we want it to go back

1061.4 -> up to December but more importantly we want it to go to the previous year so when we click January

1067.64 -> the year goes to this the year goes to 2016 and December and likewise if we're in December we

1077.39 -> want the year to go to 2017 and I'll show you the code that dead works with that so you can see how

1083.03 -> that works okay and we're going to go back into in cell calendar that's the module and there's

1090.2 -> really basically three three macros just three macros one is for today okay that's the today

1098.06 -> button here okay the second one is previous month alright and next month so all we've done here is

1108.8 -> we're in this let's say next month we're saying if b9 remember b9 is the month number if it's 12 that

1115.1 -> means we're already in December so that means we need to say okay hey p8 the next month is January

1120.35 -> right and we want to make sure that we add one more year so t7 is our year field t7 let me go

1129.17 -> ahead and move that over t7 is our year field so we want to add one that's all it's doing okay

1134.33 -> we're just adding one to that so it's relatively simple and we're saying okay if it's not 12 if

1140.54 -> that month is not 12 else in that case simply just add one more month this is p8 the month is

1147.56 -> equal to a plus 12 b9 valium that's a little bit confusing but all we're all is doing is saying

1155.81 -> okay if it's February what's the next one what is the next one so that means if it's two we'll

1162.65 -> just fit a plus 12 plus so we're just finding the next one you know we're doing that through

1169.01 -> a little bit of math and call an A so that's all it is we're just locating the next month with that

1174.23 -> with that code so I think that that helpful and basically the last one is today and with today

1180.44 -> is all we want to do is we want to set em seven okay is the Celt set cell to the current date

1188.32 -> okay and we have em seven is our date okay and what is the current date before okay we've set

1195.82 -> that as today so before is our current date so in that case we've just set it up there and we also

1203.47 -> want to make sure we've set it to the current year okay so we've set again B five is the current year

1210.19 -> and B 5 is all it is is the year of today okay and then the same thing for Day Day is the day

1217.18 -> of today so that ensures that we can we can put in the exact right current date that helps us put in

1224.14 -> put in whatever today's date is so that's all all that code does okay alright so that is it for the

1232.6 -> in cell calendar and that's how that works so I think there's some pretty good there's a little

1239.62 -> bit of VBA and a little bit of code that requires but I'm gonna go ahead and give you this sheet so

1243.76 -> you have nothing to worry about on that I hope that helps you that is how all three different

1250.27 -> pop-up counter work in fact I use all three in different workbooks in different situations I

1256.03 -> actually use all three so you may wells want to use all three depending upon the type of work that

1261.76 -> you're creating and depending on the instance so I hope that that finds you very helpful and I will

1270.01 -> go ahead and show you the independent shake based calendar now on the workbook I'm gonna include

1278.29 -> two workbooks I'm gonna include this one then I'm included one workbook with just the shape

1284.11 -> based calendar so that you can easily transfer that over so I'll go ahead and get to that right

1288.34 -> now okay I'm gonna include this workbook with you you may already have it if not this is the pop-up

1294.52 -> date picker this is the shape based pop-up that you just saw and as you can see it's the same

1299.29 -> the same shape however this particular workbook includes a full set of instructions on how to

1305.47 -> take this calendar and incorporate it in your own workbook I've also made a youtube video you just

1311.26 -> click on this link and I'll walk you through on a YouTube video that's why we can go into too much

1317.14 -> detail on how to copy this particular date picker over to yours because I've done that already in a

1323.71 -> YouTube video so I've I've given you that and I'm going to include this as well in the package so

1331.39 -> that you have both both of the workbooks with you so that's going to be really helpful and I

1338.23 -> hope that you enjoy it if you have any questions or comments please let me know I'm really really

1343.54 -> happy to be able to give you this training and teaching all I ask you to do is that if you can

1349.75 -> share this video in your favorite excel group or your favorite Facebook group and if you can

1356.59 -> share it on your wall I would greatly appreciate it thank you so much for watching and I'll see

1360.94 -> you next time please don't forget to subscribe to our You Tube channel and select these send

1366.37 -> me all notifications options so you can get our videos to you right away as soon as we create them

Source: https://www.youtube.com/watch?v=PLAJ08LP7kE