Sorry, here is the file
Printable View
Sorry, here is the file
Bob, thanks sincerely for your efforts on this. No need to apologise. The calendar pop-up with double-click is awesome.Quote:
Originally Posted by xld
Agree with you that this route is difficult, but I reckon if we can just get your class module to offset the target cell by one every time the user enters the date via the calendar we can avoid this problem without having any issues with checking for manual user input.Quote:
Originally Posted by xld
This is a really good idea. I reckon though coupling the disabling of the "X" on the Userform along with the previous point of moving off the target cell after entry would allow the objective of non manual user-entry relatively easy to achieve.Quote:
Originally Posted by xld
To hammer the point of non-manual entry, it is probably easiest to make the Userform modal, so that the user doesn't (accidentally manage to get out of the form without entering something).
What do you think about these suggestions? And if you agree, could you please show me how to implement these.
thanks and regards,
Doesn't really matter if I agree or not, you know your users and you have obviously have thought it through.
Does this do what you want?
Bob, this is wonderful, does exactly what I want :clap:.Quote:
Originally Posted by xld
I am marking this thread solved.
Having solved the core problem, and I know I am getting carried away, but I have an additional query. At the moment, the Userform pops up as default with the year 1930 (the FIRST_YEAR variable).
This is cool, but for a couple of cells B9 and B10, I would like it to display on this year and the previous year. This is a form of validation to ensure that only dates in these years are entered and also to make it convenient for the users to enter it via the Userform.
I know that it could be done by applying simple data validation for these cells, but I don't want to confuse these users too much and have the calendar gently guide them, along with written prompts.
Any ideas on how to do this, in effect it will be calling the Userform but passing through FIRST_YEAR and LAST_YEAR as paramaters when calling on the Userform. Any ideas if this is easily possible?
Here is one way
nifty stuff, gotta love Classes ehh.Quote:
Originally Posted by xld
A couple of queries:
Q1: how did you get the orange cells to start at 2008 if the range from 1930-2008 (just wondering how it figures out the start year for the drop down).
Q2: Also, I forgot to ask which line of code disabled the "X" mark for the Userform?
Thanks again.
I certainly do. I rarely if ever have a substantial project nowadays that doesn't have classes.Quote:
Originally Posted by xluser2007
It checks in the FormLoad procedure if the current year is within first year and last year, if so, it sets the ListIndex property to wher this year is within the array of years. If it isn't, it sets ListIndex to 0, the first in the series.Quote:
Originally Posted by xluser2007
There is some Userform_QueryClose event code where I set the Cancel property to True if the CloseMode is 0 (the X).Quote:
Originally Posted by xluser2007
BTW, you can call APIs to remove the X from the caption bar of the form, I just don't see the point personally.
Thank you for the explanations Bob, always amazed by your grasp of this stuff.Quote:
Originally Posted by xld
Yeah, in the spirit of getting carried away, I went and started searching for this functionality. I found Stephen Bullen's FormFun example, which is pretty cool. he also needs the Userform_QueryClose event code as you have implemented to prevent the the Alt+F4 shortcut to close the form, so I think the method you have proposed is best.Quote:
Originally Posted by xld
I might just add in a MsgBox to explain that "X" has been disabled on the UserForm to your existing code.
Many thanks for your help and insights bob. And also Mark and steve for your earlier posts.
Nice to learn something new.
Hi Bob,
In post #25, you kindly showed me how to allow the calendar to only show specific years for specific cells by passing those cells and the constraints to the Userform.
I have just one more query on this. Is it possible for certain cells, which already have a calendar pop up to be coded to display 30 June values for those specified years.
I have attached my Sample workbook. In the named range cell "Section2_b" and "Section6_b", I would like the user to select a Date using the calendar, but only a 30 June date. If they Select a non-30 June date then there should be quick vbOKonly Msgbox (which is modal), telling them to input it correctly, and the modal calendar Userform should appear again till they select a 30 June date.
Any help with this is appreciated.
regards,
Sample workbook attached as per post#30.
Why bother with the calendar if they can only have one date, why not just load 30th June when they select either of those cells?
Good point Bob,Quote:
Originally Posted by xld
But it could be 30 June of any previous year up to say 10 years.
The alternative was to allow them to pick a year in a new cell and have the 30 june value come through.
I was just wanting to use the single availible input cell, and use the prebuilt modal calendar to validate the input with a MsgBox. This is because for all other user-filled date inputs the User requires the calendar, so I figured best to go ahead with it.
The otehr question i had was, for these cells, is it possible to have the calendar display only 30 June dates by changing some code to remove all months and days except 30 june, just curious?
As per your suggestion, a good alternative could be to create a validation list of 30 june dates and just create a drop-down to pick from.
I was more curious than anything - the calendar form is pretty nifty.
Thanks for your insights as always Bob.
Okay, didn't fully appreciate that.Quote:
Originally Posted by xluser2007
It is a question of appropriateness IMO. Giving them a full-blown calendar and then throwing an error if they pick any 364 of 365 possible dates seems a tad perverse to me.Quote:
Originally Posted by xluser2007
As it is a custom calendar not a calendar control, that should be possible ... but wouldn't b e how I would do it. You would just pass some value to the form for this instance, say a Boolean variable June30Only, and build the calendar withQuote:
Originally Posted by xluser2007
[vba]
Private Sub Build_Calendar()
Dim mpDate As Date
'the routine that actually builds the calendar each time
If CreateCal = True Then
CalendarFrm.Caption = " " & CB_Mth.Value & " " & CB_Yr.Value
'sets the focus for the todays date button
CommandButton1.SetFocus
For i = 1 To 42
mpDate = DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value)))
If i < Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
Controls("D" & (i)).Caption = Format(mpDate, "d")
Controls("D" & (i)).ControlTipText = Format(mpDate, "m/d/yy")
ElseIf i >= Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
Controls("D" & (i)).Caption = Format(mpDate, "d")
Controls("D" & (i)).ControlTipText = Format(mpDate, "m/d/yy")
End If
If Format(mpDate, "mmmm") = ((CB_Mth.Value)) Then
If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H80000018 '&H80000010
Controls("D" & (i)).Font.Bold = True
If Format(mpDate, "m/d/yy") = Format(ThisDay, "m/d/yy") Then Controls("D" & (i)).SetFocus
Else
If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H8000000F
Controls("D" & (i)).Font.Bold = False
End If
Controls("D" & (i)).Enabled = Not June30Only Or (Day(mpDate) = 30 And Month(mpDate) = 6)
Next
End If
End Sub
[/vba]
and add this code in the FormLoad procedure before Call Build_Calendar
[vba]
If June30Only Then
CB_Mth.ListIndex = 5
End If
[/vba]
That is the way I would do it. Far better to have an appropriate diaogue IMO that just using something becuase you use it elsewhere.Quote:
Originally Posted by xluser2007
[quote=xld]Okay, didn't fully appreciate that.
It is a question of appropriateness IMO. Giving them a full-blown calendar and then throwing an error if they pick any 364 of 365 possible dates seems a tad perverse to me.
[quote]
Absolutely agree Bob, simple listed data validation is the best way to go.
This is excellent Bob. I am amazed that every time I put up a bit of a challenge you quickly come up with a nifty solution. Well done!Quote:
Originally Posted by xld
Yeah the Calendar has many good uses, but I understand it shouldn't be overused just because it merely looks good to the user (or designer ;)).Quote:
Originally Posted by xld
kind regards