Instead of using frames, may consider labels and here's the idea 👉ruclips.net/video/KPZlSRe-y3Q/видео.html Hope you like it. Thanks for watching and have a nice day.
Hello, and thank you for subscribing to my channel! 😊 I just wanted to let you know that this channel is not primarily focused on Excel content anymore. I don't post Excel-related videos here as often (or at all now). If you're interested in Excel topics, I post them over on my the other channel, @ahsing_excel_VBA. Feel free to check it out if you'd like! I hope you find the contents useful. Thanks again for watching. Wishing you a wonderful day! 🌟
Very nice video. I tried to make a worksheet by my self with your tutorial. The calendar shows up, but the frames don't work. Also the label2 goes only from January to December: commandbutton3 doesn't work from January 2023 to December 2022 and commandbutton4 doesn't work from December 2023 to January 2024. I don't understand why......It seems all codes you made are correct. I would like to show you my worksheet but I don't know how..... Please help me.....Thankyou very much..... Have a nice day
Hi Antonio. This is the limitation of using a frame where it works only when we click on the border/corner but not center of the frame. A better control is Label where we can click on any area that we like. The reason of using frame is we can use the same code for all frames but not for labels. There are pros and cons. Since CommandButton3 and CommandButton4 have conditions, kindly check if the date format used in the code recognized by your system. If you want to show me your worksheet, may send to me at ahsing.math.excel@gmail.com. Kindly notify me here after sent. Thanks for watching and have a nice day.
Hi teacher Thank you for these tutorials, I made such thanks to you, but when I want to move from December to a new year, or from January to a previous year, I find a problem, and the same thing even in the file you created. What is the solution please.
Hi Lamraft. Did you receive any error message and what was it? Is the cause of the problem due to date format? eg. if you are using American date format "mm/dd/yyyy" then amendments needed. Kindly change those involved statements to the desired format.
This is the best calendar video, I learned so much. Thank you. I'm having an issue and can't find an answer. I have a set button on my Userform2 to show the calendar (Userform3). The calendar works as expected & the date is entered into Userform2.TextBox1, as expected via Private Sub Frame02_Click(), for example. The problem occurs when I begin at my main menu (Userform1), then show Userform2, then click on the set button. The calendar (Userform3) acts as expected, but the Userform2.TextBox1 is no longer populated. I get the error: Userform2.TextBox1 = I'm thinking it is a simple error that shows my inexperience with userforms, since it works with 1 userform, but not when I'm 2 userforms deep. I tried moving the code from Userform3 to Userform2, but then it doesn't recognize the Userform3 variables. I've tried making the Userform3 Public vs Private. I've tried Modal and Modeless. I've tried declaring the Userform2 while in Userform3. I've tried Userform3.Hide instead of Unload Me. Can you please help me solve the error?
Thanks and my pleasure. Regarding your issue, are you transferring values from one UserForm to another? If that's the case, this ruclips.net/video/jUkna9VBKcU/видео.html may help you. Thanks for watching and have a nice day.
Thanks. Yes, there is. We can make use of the "selection change" procedure and call the UserForm whenever needed. May consider this video ruclips.net/video/aTsSCy-6eXo/видео.html for better understanding. Thanks for watching and have a nice day.
@@ahsingtvthank you so much for all the help. I don’t have access to the date picker 😢. Work has blocked me from add ins. Well this formula still work with this date picker?
Hi! First of all thank you for this lesson. I have a problem about a line (a=DateValue...) in Assigndate sub. I get this error ⇒Run-time error '13' Type mismatch I have seen error in this code: a = DateValue ("1 " & " . " & userform1.labelmonth & " . "userform1.labelyear) I have tried these: Dim a as string dim a as Integer Dim a as Boolean etc like this. Could you help me please! Thank you again
@@ahsingtv thank you so much for your quick reply. I have tried its. Interestingly, the userform gives an error when the excel file is first opened on computers with date format "dd/mm/yyyy" but then continues to normal work. It just show error when first open But when the excel file has the date format "mm/dd/yyyy" (we have a computer that uses the american date format), the userform gives the error I specified, and the userform gives the type mismatch error every time. I will try again and check the codes again. I hope I can find reason of this error
Dear expert, I reviewed my codes again. I saw I deleted the captions of labelmonth and labelyear labels 🤦♂ When I pressed the "debug" button in the error window, the a=DateValue(.....) line appeared to be empty for "a" value. because I deleted the Captions. This is the cause of the error I see. 👍 Thank you very very much again. I subscribe to your channel and turn on all notifications 🙂👏
Thanks for your support but please don't call me expert, I feel shy 😅. By the way, this channel is a bit "mixed", not recommended to turn on all notifications 😂 If you are interested, here a channel for you ruclips.net/channel/UCwrvrmndwwWd0vWk2jTfMsw Is a new channel that only shares videos related to Excel and VBA, no others. Wish you have a nice day.
Hi. The link to the DatePicker is included in the video's description. As for 'Microsoft MonthView Control 6.0,' you can follow the steps outlined in this page: answers.microsoft.com/en-us/msoffice/forum/all/microsoft-vba-monthview-control-is-missing/d7761078-90c7-4885-9e26-84f87ba5156d. However, please note that it's possible the control may no longer be available. Hope this helps. Thanks for watching and have a nice day.
hi , may i ask u why the sub assigndate() become yellow colour and "variable are not defined? Sub Assigndate() a = DateValue("1 " & UserForm1.Label2 & " " & UserForm1.Label1) firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8 If Day(firstsun + 1)
Hi Nur. Line 12 should be Frame.Caption = Format(Day(firstday + i), "0#") but not Frame.Caption = Format(Day(firstday + 1), "0#"). However, this is not the cause of the error. Kindly check the names of UserForm, Labels and Frames then define all the variables used. For eg. "Dim a as Date", in case your version requires you to define all the variables before using. Hope it helps. Thanks for watching and have a nice day.
Hi sir, i have question. how can i set multiple date picker in 1 userform? i use command button instead of frame. my calendar userform : Private Sub CommandButton1_Click() grn.TextBox2.Value = calendar1.Controls(ActiveControl.Name).Caption & " " & calendar1.Label2.Caption & " " & calendar1.Label1.Caption Unload Me End Sub
Hi. Explaining this solely with words is challenging. So, I've created a video at ruclips.net/video/StgbKbC9dHU/видео.html, which I believe is what you're looking for. I hope you find it helpful. Have a great day!
Sir, i got 2 more questions: 1- Is there a way to keep the month label in the form as a name ("january, march, etc.") but when the date is paste in our sheet is pasterd as numers (i.e. "01/04/2023")? 2- Is there a way to change the display (visualy) of the calendar when we click on the button without moving the button? (say i have the button in A1 but when i click it it pops up in M28 (aprox).
Hi Eugenio. For Q1, can and there are two methods: 1- Sheet1.Cells(7, "G") = Format(UserForm1.Controls(ActiveControl.Name).Caption & " " & UserForm1.Label2.Caption & " " & UserForm1.Label1.Caption, "dd mmmm yyyy") 2- Sheet1.Cells(7,"G").NumberFormat = "dd mmmm yyyy" For Q2, may try: UserForm1.StartUpPosition = 0 UserForm1.Top = Range("M28").Top - ActiveWindow.UsableHeight + ActiveWindow.Height UserForm1.Left = Range("M28").Left Kindly take note that adjustment might be needed. May type numbers for top and left positions directly see which method matches your need better. Hope they help. Have a nice day.
Thank you for the video! I am having some issues, spent a day and i can't figure out where the problem. The calendar is showing wrong first Sunday, while i did the same steps and wrote the same things as you did in the video. Do you think you can assist in figuring out where is the error? Sub Assigndate() a = DateValue("1 " & Calendar.Label2 & " " & Calendar.Label1) firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8 If Day(firstsun + 1)
My pleasure 😊 I have read through the code, since it is the same as mine, thus I guess the problem is not from the code. Is it possible that the frames are not in sequence? Since we are using "For each Frame ...", thus the numbering is very important. Kindly click through those frames, one by one, check and see if the names (numbering) followed a correct sequence (row 1: Frame1 -> ... -> Frame7, row 2: Frame8 -> ... -> Frame14, and so on). If that's was not the case, maybe our systems' first days are different. For this case, simply use trial and error method, i.e., change "8" in "firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8" and "firstday = firstsun - 8" to "7". Hope one of them helps. Thanks for watching and have a nice day.
@@ahsingtv Thank you so much for your reply. the frame numbering is correct, however when changing -8 +8 to -7/+7 nothing changes, nor +6/-6 ... starting +5/-5 the calendar changes but it is still wrong. Well at +8/-8 the lock is correct, however the numbering sequence is wrong, for September 2022 the first day that can be picked is 04-sep-2022 while 01,02, 03 are locked... and at the end after 31 there are 01,02,03 opened but these should be for October. and September is 30 days, it is showing 31.
Oh Man!!! It took me one day to figure out the issue.... it was a ridiculous Typo in the Firstday, i had written in Fristday . Anyway, Thank you so much for the video. You are a genius! keep up the good work.
Glad to hear that your issue is solved. I didn't notice you typed wrongly until you told me 🤣 Luckily you found it or else another day to go 😂😂 Kindly ignore my previous reply. Could only see you have solved the problem after replying and refreshing the web page.
Hi, can we amend the frame sub routines to include multiple cells whereby we want to add a date? eg Sheet1.Cells(1,2, "A") etc? If so, what is the syntax for this? Thanks
Hi Dom. Yes, we can include multiple cells. May try one of the following. Method 1: Sheet1.Cells(1,"A") = Date Sheet1.Cells(2,"A") = Date 'and so on if any. Method 2: For Each cell in Sheet1.Cells.Range("A1, A2, etc if any") cell = Date Next '(to have better understanding may consider this video ruclips.net/video/yrsJX3LnnHc/видео.html that shares how to include multiple cells or ranges at about 4:00s) Hope it helps. Thanks for watching and have a nice day.
I have question the sequences of the frames from the second line become 33 and not like what it suppose to be but I do the same with you how can I fix it
Hi Fatimah. I have two suggestions: 1. rename the frames to the correct numbers one by one, 2. delete all frames, save and close Excel then reopen again, only then insert the frames. The way to click and drag those frames affects the sequences. 07:47 tells the correct method: from last to first, click one by one with Ctrl key. Hope it helps. Thanks for watching and have nice day.
Awesome Mr. Now, it seems to me that frames make the userform slow :( Could you change the days(frames) for labels? but that it only works on the days, I say this because it can affect other labels such as the month and the years, thanks.
Thanks. May I know which version are you using? Because it works smooth for me (MS 365, 2022). The reason I was using frames is because label is not an active control, which my current codes do not work for labels. If want to use labels, instead, then have to insert class module (may refer to answers.microsoft.com/en-us/msoffice/forum/all/get-the-name-of-the-label-just-clicked/81b557f5-7828-4a24-a7ca-e8cb9f44775b). However, the idea of the method is something like restructure/reassigning ALL controls on the UserForm in order to "convert" labels from "non-active" to "active control". With this method, another issue arises, all controls are affected, then the coding have to be rewritten. This is why I opted for frame since copying and pasting are easier (for me) as compared to structuring codes that have to consider more aspect. Thanks for watching and have a nice day.
@@ahsingtv Actually I changed it to labels. I shrinked the form and it gets activated only if clicked on bottom right of the frame and cannot tell the users to click on a specif spot. Thank you for this code though. We were using a tool that was created in the mid 2000 which was using mscal.ocx and I was unable to find a replacement MS 365 This code helped.
Yup. Is something like ruclips.net/video/NU38OqZJm-M/видео.html. The video shows how to add image control during runtime (at about 7:30s of the video), we just have to change it to frame. Hope it helps and have a nice day.
Good morning, dear Sir Thank you for this video. I managed to reproduce the calendar and it works. But I have an additional concern. In fact, I am designing a form whose name is: FrmSaisiePompe Then we must enter the date in this form, so I wanted that once we choose the date in the calendar, the date is displayed in the textbox of the date that I renamed: TxtDate And once the date is selected, the calendar disappears then I continue filling in the form. Please help me
Hi and good morning. This can be done by changing all the "End" in each sub of frame to "Unload Me". For example, change Private Sub Frame1_Click() Sheet1.Cells(7, "G") = UserForm1.Controls(ActiveControl.Name).Caption & " " & UserForm1.Label2.Caption & " " & UserForm1.Label1.Caption End End Sub to Private Sub Frame1_Click() Sheet1.Cells(7, "G") = UserForm1.Controls(ActiveControl.Name).Caption & " " & UserForm1.Label2.Caption & " " & UserForm1.Label1.Caption Unload Me End Sub Hope this helps. Thanks for watching and have a nice day.
Hi, I cant see what the problem is here ? with this message code (run-time error 13) Private Sub CommandButton3_Click() If UserForm1.Label2.Caption = "January" Then UserForm1.Label2.Caption = "December" Else UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & "2022")) - 1) End If Assigndate
Hi Ewan. Try add a space before 2022 see if settled. UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & " 2022")) - 1) Hope it helps. Thanks for watching and have a nice day.
Hi, this error typically indicates that a Variant used in an expression has an incorrect subtype. Kindly check on the variables used for UserForm3.Label2 and UserForm3.Label1. Also, please check if the date format used is recognized by Excel on your system. Thanks for watching and have a nice day.
appreciate the lectures. I am having issues with the same code you used for label2 (months) Private Sub CommandButton3_Click() If UserForm1.Label2.Caption = "December" Then UserForm1.Label2.Caption = "January" Else UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & " 2023"))) - 1 End If Assigndate End Sub Private Sub CommandButton4_Click() If UserForm1.Label2.Caption = "December" Then UserForm1.Label2.Caption = "January" Else UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & " 2023"))) + 1 End If Assigndate End If End Sub
Hi Nezir. For CommandButton3_Click(), line 4, the adjustment should be made before end of the MonthName function, i.e. UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & " 2023"))-1), the same goes for the other button. In addition for CommanButton4_Click(), extra "End If" found, delete the "End If" after "Assigndate". Hope these help. Thanks for watching and have a nice day.
@@ahsingtv thanks a lot. corrected the code to your suggestion and now my March month all the way to September is faded out for dates and they are not selectable but higher year like 2023 works. Not sure what I did wrong
Hi Sir! Really great video, many thanks!! I do have a problem tough. maybe you can help me out: everything in the form is working fine, everything but the numbers in the forms (i have 42 frames displaying the number "31") and i can't find the answer. I am from argentina and we use spanish name for the months (i.e. january = enero), maybe that is causing issues. I think it has to be with the "assigndate" macro but i dont really know. Here is the code i wrote: Sub calendario_boton() a = DateValue("1 " & Pitbull_calendar.Label3 & " " & Pitbull_calendar.Label1) firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8 If Day(firstsun + 1)
Hi Eugenio. I think the cause is not because of different languages since if the language is set in the system, then Excel would be able to understand it. I believe the cause is from line 10 (exclude line break and macro's name), should be "If TypeName(frame) = "Frame" Then" but not "If TypeName(frame) = "frame" Then". Kindly take note that, strings are case sensitive, i.e. "Frame" ≠ "frame". Hope this helps. Thanks for watching and have a nice day 😊
Instead of using frames, may consider labels and here's the idea 👉ruclips.net/video/KPZlSRe-y3Q/видео.html
Hope you like it. Thanks for watching and have a nice day.
Excellent. Simply everything I was looking to understand. Thank you very much for this excellent video. I have now subscribed. 😁
Hello, and thank you for subscribing to my channel! 😊
I just wanted to let you know that this channel is not primarily focused on Excel content anymore. I don't post Excel-related videos here as often (or at all now). If you're interested in Excel topics, I post them over on my the other channel, @ahsing_excel_VBA. Feel free to check it out if you'd like! I hope you find the contents useful.
Thanks again for watching. Wishing you a wonderful day! 🌟
Thanks for sharing.
My pleasure. Thanks for watching and have a nice day.
Very nice video.
I tried to make a worksheet by my self with your tutorial.
The calendar shows up, but the frames don't work. Also the label2 goes only from January to December: commandbutton3 doesn't work from January 2023 to December 2022 and commandbutton4 doesn't work from December 2023 to January 2024. I don't understand why......It seems all codes you made are correct.
I would like to show you my worksheet but I don't know how..... Please help me.....Thankyou very much..... Have a nice day
Hi Antonio. This is the limitation of using a frame where it works only when we click on the border/corner but not center of the frame. A better control is Label where we can click on any area that we like. The reason of using frame is we can use the same code for all frames but not for labels. There are pros and cons.
Since CommandButton3 and CommandButton4 have conditions, kindly check if the date format used in the code recognized by your system.
If you want to show me your worksheet, may send to me at ahsing.math.excel@gmail.com.
Kindly notify me here after sent.
Thanks for watching and have a nice day.
Hi Antonio, kindly check your email. Have a nice day.
Teşekkürler, Gayet güzel bir içerik olmuş.
Teşekkürler!
İzlediğiniz için teşekkürler ve iyi günler
Hi teacher
Thank you for these tutorials, I made such thanks to you, but when I want to move from December to a new year, or from January to a previous year, I find a problem, and the same thing even in the file you created. What is the solution please.
Hi Lamraft. Did you receive any error message and what was it?
Is the cause of the problem due to date format? eg. if you are using American date format "mm/dd/yyyy" then amendments needed. Kindly change those involved statements to the desired format.
This is the best calendar video, I learned so much. Thank you.
I'm having an issue and can't find an answer.
I have a set button on my Userform2 to show the calendar (Userform3).
The calendar works as expected & the date is entered into Userform2.TextBox1, as expected via Private Sub Frame02_Click(), for example.
The problem occurs when I begin at my main menu (Userform1), then show Userform2, then click on the set button.
The calendar (Userform3) acts as expected, but the Userform2.TextBox1 is no longer populated.
I get the error: Userform2.TextBox1 =
I'm thinking it is a simple error that shows my inexperience with userforms, since it works with 1 userform, but not when I'm 2 userforms deep.
I tried moving the code from Userform3 to Userform2, but then it doesn't recognize the Userform3 variables.
I've tried making the Userform3 Public vs Private. I've tried Modal and Modeless. I've tried declaring the Userform2 while in Userform3. I've tried Userform3.Hide instead of Unload Me.
Can you please help me solve the error?
Thanks and my pleasure.
Regarding your issue, are you transferring values from one UserForm to another? If that's the case, this ruclips.net/video/jUkna9VBKcU/видео.html may help you.
Thanks for watching and have a nice day.
Awesome work this is amazing. Is there away to get different dates in multiple different cells.
Thanks.
Yes, there is. We can make use of the "selection change" procedure and call the UserForm whenever needed.
May consider this video ruclips.net/video/aTsSCy-6eXo/видео.html for better understanding.
Thanks for watching and have a nice day.
@@ahsingtvthank you so much for all the help. I don’t have access to the date picker 😢. Work has blocked me from add ins. Well this formula still work with this date picker?
Hi. We can replace the add-in-picker by the UserForm and it should work.
Consider the code "UserFrom1.Show" when intersection occurs. Hope this helps.
@@ahsingtvthank you so much I’m still new to VBA and still learning this helped me a lot thank you so much for the information
Hi! First of all thank you for this lesson. I have a problem about a line (a=DateValue...) in Assigndate sub. I get this error ⇒Run-time error '13'
Type mismatch
I have seen error in this code:
a = DateValue ("1 " & " . " & userform1.labelmonth & " . "userform1.labelyear)
I have tried these:
Dim a as string
dim a as Integer
Dim a as Boolean
etc like this.
Could you help me please!
Thank you again
Hi Iskender. Try this: a = DateValue ("1 " & userform1.labelmonth & " " & userform1.labelyear)
Hope it helps. Thanks for watching and have a nice day.
@@ahsingtv thank you so much for your quick reply. I have tried its. Interestingly, the userform gives an error when the excel file is first opened on computers with date format "dd/mm/yyyy" but then continues to normal work. It just show error when first open
But when the excel file has the date format "mm/dd/yyyy" (we have a computer that uses the american date format), the userform gives the error I specified, and the userform gives the type mismatch error every time. I will try again and check the codes again.
I hope I can find reason of this error
Dear expert, I reviewed my codes again. I saw I deleted the captions of labelmonth and labelyear labels 🤦♂ When I pressed the "debug" button in the error window, the a=DateValue(.....) line appeared to be empty for "a" value. because I deleted the Captions. This is the cause of the error I see. 👍
Thank you very very much again. I subscribe to your channel and turn on all notifications 🙂👏
Thanks for your support but please don't call me expert, I feel shy 😅. By the way, this channel is a bit "mixed", not recommended to turn on all notifications 😂
If you are interested, here a channel for you ruclips.net/channel/UCwrvrmndwwWd0vWk2jTfMsw
Is a new channel that only shares videos related to Excel and VBA, no others.
Wish you have a nice day.
I Have the same problem i don't know what to do
Hi sir. From which site can I download DatePicker and Microsoft MonthView Control 6.0 for Excel Office2016? Thanks
Hi. The link to the DatePicker is included in the video's description. As for 'Microsoft MonthView Control 6.0,' you can follow the steps outlined in this page: answers.microsoft.com/en-us/msoffice/forum/all/microsoft-vba-monthview-control-is-missing/d7761078-90c7-4885-9e26-84f87ba5156d. However, please note that it's possible the control may no longer be available. Hope this helps. Thanks for watching and have a nice day.
hi , may i ask u why the sub assigndate() become yellow colour and "variable are not defined?
Sub Assigndate()
a = DateValue("1 " & UserForm1.Label2 & " " & UserForm1.Label1)
firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8
If Day(firstsun + 1)
Hi Nur. Line 12 should be Frame.Caption = Format(Day(firstday + i), "0#") but not Frame.Caption = Format(Day(firstday + 1), "0#").
However, this is not the cause of the error. Kindly check the names of UserForm, Labels and Frames then define all the variables used. For eg. "Dim a as Date", in case your version requires you to define all the variables before using.
Hope it helps. Thanks for watching and have a nice day.
Hi sir, i have question. how can i set multiple date picker in 1 userform? i use command button instead of frame.
my calendar userform :
Private Sub CommandButton1_Click()
grn.TextBox2.Value = calendar1.Controls(ActiveControl.Name).Caption & " " & calendar1.Label2.Caption & " " & calendar1.Label1.Caption
Unload Me
End Sub
Hi. Explaining this solely with words is challenging. So, I've created a video at ruclips.net/video/StgbKbC9dHU/видео.html, which I believe is what you're looking for. I hope you find it helpful. Have a great day!
Great
Thanks!
Thanks for watching and have a nice day.
Sir, i got 2 more questions:
1- Is there a way to keep the month label in the form as a name ("january, march, etc.") but when the date is paste in our sheet is pasterd as numers (i.e. "01/04/2023")?
2- Is there a way to change the display (visualy) of the calendar when we click on the button without moving the button? (say i have the button in A1 but when i click it it pops up in M28 (aprox).
Hi Eugenio.
For Q1, can and there are two methods:
1- Sheet1.Cells(7, "G") = Format(UserForm1.Controls(ActiveControl.Name).Caption & " " & UserForm1.Label2.Caption & " " & UserForm1.Label1.Caption, "dd mmmm yyyy")
2- Sheet1.Cells(7,"G").NumberFormat = "dd mmmm yyyy"
For Q2, may try:
UserForm1.StartUpPosition = 0
UserForm1.Top = Range("M28").Top - ActiveWindow.UsableHeight + ActiveWindow.Height
UserForm1.Left = Range("M28").Left
Kindly take note that adjustment might be needed. May type numbers for top and left positions directly see which method matches your need better.
Hope they help. Have a nice day.
Thank you for the video!
I am having some issues, spent a day and i can't figure out where the problem.
The calendar is showing wrong first Sunday, while i did the same steps and wrote the same things as you did in the video.
Do you think you can assist in figuring out where is the error?
Sub Assigndate()
a = DateValue("1 " & Calendar.Label2 & " " & Calendar.Label1)
firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8
If Day(firstsun + 1)
My pleasure 😊
I have read through the code, since it is the same as mine, thus I guess the problem is not from the code. Is it possible that the frames are not in sequence?
Since we are using "For each Frame ...", thus the numbering is very important. Kindly click through those frames, one by one, check and see if the names (numbering) followed a correct sequence (row 1: Frame1 -> ... -> Frame7, row 2: Frame8 -> ... -> Frame14, and so on).
If that's was not the case, maybe our systems' first days are different. For this case, simply use trial and error method, i.e., change "8" in "firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8" and "firstday = firstsun - 8" to "7".
Hope one of them helps.
Thanks for watching and have a nice day.
@@ahsingtv Thank you so much for your reply. the frame numbering is correct, however when changing -8 +8 to -7/+7 nothing changes, nor +6/-6 ... starting +5/-5 the calendar changes but it is still wrong.
Well at +8/-8 the lock is correct, however the numbering sequence is wrong, for September 2022 the first day that can be picked is 04-sep-2022 while 01,02, 03 are locked... and at the end after 31 there are 01,02,03 opened but these should be for October.
and September is 30 days, it is showing 31.
Oh Man!!! It took me one day to figure out the issue.... it was a ridiculous Typo in the Firstday, i had written in Fristday .
Anyway, Thank you so much for the video. You are a genius! keep up the good work.
Do you mind to send your file to me at ahsing.math.excel@gmail.com?
Kindly drop me a message here once it is sent.
Glad to hear that your issue is solved.
I didn't notice you typed wrongly until you told me 🤣
Luckily you found it or else another day to go 😂😂
Kindly ignore my previous reply. Could only see you have solved the problem after replying and refreshing the web page.
Hi, can we amend the frame sub routines to include multiple cells whereby we want to add a date? eg Sheet1.Cells(1,2, "A") etc? If so, what is the syntax for this? Thanks
Hi Dom. Yes, we can include multiple cells.
May try one of the following.
Method 1:
Sheet1.Cells(1,"A") = Date
Sheet1.Cells(2,"A") = Date
'and so on if any.
Method 2:
For Each cell in Sheet1.Cells.Range("A1, A2, etc if any")
cell = Date
Next
'(to have better understanding may consider this video ruclips.net/video/yrsJX3LnnHc/видео.html that shares how to include multiple cells or ranges at about 4:00s)
Hope it helps. Thanks for watching and have a nice day.
nice job :D
Thanks! Thanks for watching and have a nice day :D
I have question
the sequences of the frames from the second line become 33 and not like what it suppose to be but I do the same with you
how can I fix it
Hi Fatimah. I have two suggestions:
1. rename the frames to the correct numbers one by one,
2. delete all frames, save and close Excel then reopen again, only then insert the frames.
The way to click and drag those frames affects the sequences. 07:47 tells the correct method: from last to first, click one by one with Ctrl key.
Hope it helps.
Thanks for watching and have nice day.
Awesome Mr.
Now, it seems to me that frames make the userform slow :(
Could you change the days(frames) for labels? but that it only works on the days, I say this because it can affect other labels such as the month and the years, thanks.
Thanks.
May I know which version are you using? Because it works smooth for me (MS 365, 2022).
The reason I was using frames is because label is not an active control, which my current codes do not work for labels.
If want to use labels, instead, then have to insert class module (may refer to answers.microsoft.com/en-us/msoffice/forum/all/get-the-name-of-the-label-just-clicked/81b557f5-7828-4a24-a7ca-e8cb9f44775b). However, the idea of the method is something like restructure/reassigning ALL controls on the UserForm in order to "convert" labels from "non-active" to "active control". With this method, another issue arises, all controls are affected, then the coding have to be rewritten. This is why I opted for frame since copying and pasting are easier (for me) as compared to structuring codes that have to consider more aspect.
Thanks for watching and have a nice day.
@@ahsingtv Actually I changed it to labels. I shrinked the form and it gets activated only if clicked on bottom right of the frame and cannot tell the users to click on a specif spot. Thank you for this code though. We were using a tool that was created in the mid 2000 which was using mscal.ocx and I was unable to find a replacement MS 365 This code helped.
Is it possible to add frames to the form with vba code.
Yup. Is something like ruclips.net/video/NU38OqZJm-M/видео.html.
The video shows how to add image control during runtime (at about 7:30s of the video), we just have to change it to frame.
Hope it helps and have a nice day.
Good morning, dear Sir
Thank you for this video.
I managed to reproduce the calendar and it works.
But I have an additional concern.
In fact, I am designing a form whose name is: FrmSaisiePompe
Then we must enter the date in this form, so I wanted that once we choose the date in the calendar, the date is displayed in the textbox of the date that I renamed: TxtDate
And once the date is selected, the calendar disappears then I continue filling in the form.
Please help me
Hi and good morning. This can be done by changing all the "End" in each sub of frame to "Unload Me".
For example, change
Private Sub Frame1_Click()
Sheet1.Cells(7, "G") = UserForm1.Controls(ActiveControl.Name).Caption & " " & UserForm1.Label2.Caption & " " & UserForm1.Label1.Caption
End
End Sub
to
Private Sub Frame1_Click()
Sheet1.Cells(7, "G") = UserForm1.Controls(ActiveControl.Name).Caption & " " & UserForm1.Label2.Caption & " " & UserForm1.Label1.Caption
Unload Me
End Sub
Hope this helps.
Thanks for watching and have a nice day.
Hi, I cant see what the problem is here ? with this message code (run-time error 13)
Private Sub CommandButton3_Click()
If UserForm1.Label2.Caption = "January" Then
UserForm1.Label2.Caption = "December"
Else
UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & "2022")) - 1)
End If
Assigndate
Hi Ewan. Try add a space before 2022 see if settled.
UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & " 2022")) - 1)
Hope it helps. Thanks for watching and have a nice day.
Error 13 type mismatch at code : a=DayValue("1 " & calendar.lable2 & " " & calendar.label1) ?
Hi. It is "DateValue", not "DayValue".
Thanks for watching and have a nice day.
Seçili Frame (Gün) zemin renklenirse daha iyi olur.
Hi, editable template available, feel free to edit in accordance with your preference 😃
Have a nice day 😊
if i'm using one label with this "monthname(month(now))&" "&year(now)", how should i do with the only two command button?
Hi Kent. May try
1. Me.Label1.Caption = MonthName(Month(DateValue(Me.Label1.Caption) + 31)) & " " & Year(DateValue(Me.Label1.Caption) + 31) 'for next month
2. Me.Label1.Caption = MonthName(Month(DateValue(Me.Label1.Caption) - 1)) & " " & Year(DateValue(Me.Label1.Caption) - 1) 'for previous month
Hope it helps. Thanks for watching and have a nice day.
I am getting run time error 13 on the line
a = DateValue("1 " & UserForm3.Label2 & " " & UserForm3.label1)
Hi, this error typically indicates that a Variant used in an expression has an incorrect subtype. Kindly check on the variables used for UserForm3.Label2 and UserForm3.Label1. Also, please check if the date format used is recognized by Excel on your system.
Thanks for watching and have a nice day.
😎
😎
appreciate the lectures. I am having issues with the same code you used for label2 (months)
Private Sub CommandButton3_Click()
If UserForm1.Label2.Caption = "December" Then
UserForm1.Label2.Caption = "January"
Else
UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & " 2023"))) - 1
End If
Assigndate
End Sub
Private Sub CommandButton4_Click()
If UserForm1.Label2.Caption = "December" Then
UserForm1.Label2.Caption = "January"
Else
UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & " 2023"))) + 1
End If
Assigndate
End If
End Sub
Hi Nezir.
For CommandButton3_Click(), line 4, the adjustment should be made before end of the MonthName function, i.e. UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & " 2023"))-1), the same goes for the other button.
In addition for CommanButton4_Click(), extra "End If" found, delete the "End If" after "Assigndate".
Hope these help.
Thanks for watching and have a nice day.
@@ahsingtv thanks a lot. corrected the code to your suggestion and now my March month all the way to September is faded out for dates and they are not selectable but higher year like 2023 works. Not sure what I did wrong
figured it out. I had a 1 instead of i in an area. thanks a lot. works good now :)
My pleasure. Have a nice day 😊
Hi Sir! Really great video, many thanks!!
I do have a problem tough. maybe you can help me out:
everything in the form is working fine, everything but the numbers in the forms (i have 42 frames displaying the number "31") and i can't find the answer.
I am from argentina and we use spanish name for the months (i.e. january = enero), maybe that is causing issues.
I think it has to be with the "assigndate" macro but i dont really know.
Here is the code i wrote:
Sub calendario_boton()
a = DateValue("1 " & Pitbull_calendar.Label3 & " " & Pitbull_calendar.Label1)
firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8
If Day(firstsun + 1)
the frames are correctly listed
Hi Eugenio. I think the cause is not because of different languages since if the language is set in the system, then Excel would be able to understand it. I believe the cause is from line 10 (exclude line break and macro's name), should be "If TypeName(frame) = "Frame" Then" but not "If TypeName(frame) = "frame" Then".
Kindly take note that, strings are case sensitive, i.e. "Frame" ≠ "frame". Hope this helps.
Thanks for watching and have a nice day 😊
@@ahsingtv Thank you VERY MUCH!!! you are the MVP
My pleasure. Have a nice day.