Solved: Userform Calendar Control

[perhol]

I am using lucas' Userform Calendar Control, changed by xld to start the week on mondays, and it is nearly perfect.

When the control is visible, holding the mouse over a control, a tip (like a tooltip) pops up. VBA calls the tip ControlTipText

The tip shows the date in the form "m-d-yy", but, living in Denmark, i would like the tip to show in the form "d-m-yy" or not show at all.

I can change it to show in the form i want, but that creates another problem.

By pressing the control, the date is inserted in the selected cell. If i don't change the ControlTipText, the chosen date is inserted correct in the form "dd. mmmm", ie. 10. marts (danish for march).
If i change the ControlTipText to "d-m-yy", the wrong date is inserted in the selected cell. If i have chosen 10. march, 3. october is inserted.


Can anyone change the ControlTipText to show in the form "d-m-yy" or not show at all.

Here is the vba that builds the calendar:
[VBA]Private Sub Build_Calendar()
'Rutinen der rent faktisk opbygger kalenderen hver gang
If CreateCal = True Then
CalendarFrm.Caption = " " & CB_Mth.Value & " " & CB_Yr.Value
'S?tter focus p? knappen for dagsdato
CommandButton1.SetFocus
For i = 1 To 42
If i < Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value), 2)), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value), 2)), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
ElseIf i >= Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) _
& "/1/" & (CB_Yr.Value), 2)), ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value), 2)), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
End If
If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value), 2)), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "mmmm") = ((CB_Mth.Value)) Then
If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H80000018
Controls("D" & (i)).Font.Bold = True
If Controls("D" & (i)).BackColor = &H80000016 Then Controls("D" & (i)).BackColor = &H80000003
Controls("D" & (i)).Font.Bold = True
If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value), 2)), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "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
Next
End If
End Sub[/VBA]

[Bob Phillips]

Make sure that your target cells are also formatted to the date format you want, they are probably still the US format.

[perhol]

No, it is in the format "d. mmmm".
It seemes that the inserted date is taken from the ControlTipText.

I just want the ControlTipText to show in "d-m-yy" or not at all.

[Bob Phillips]

Post your workbook and I will take a look.

[perhol]

The calendar control is used in only 1 cell in sheet1 (Uge1).
The workbook contains 19 sheets, 2 forms (including lucas' Calendar Control) and 9 modules.
It have a size of 1.71 MB



OK, i tried to uplod - and then i found out that i could only uplod 244 kb.

I will delet all but sheet1 and the form and code in question and then try again.

[perhol]

Well, here it is:

[Bob Phillips]

I just ran it on cell Q@, and selected 9th Jan, and it inserted 9. January.

So it works for me.

[perhol]

without changing anything?

[Bob Phillips]

Yeah mate. I changed nothing. I know your Excel version is Danish, but mine is English, not US, so I should suffer the same problems as you.

The only other thing that I can suggest is to check your regional settings in Control Panel (sorry, no idea what that migt be in Danis), and make sure that the Short and Long date format are not US style.

[perhol]

If you use without changing anything, and select 9. January, a small popup (ControlTipText) will show 1-9-08.
To avoid confusing the users, i would like the small popup (ControlTipText) to show 9-1-08 (that is the format for a date in danish), or the small popup (ControlTipText) to not show at all.
The date is however inserted correct in the cell. And my regional settings are set correct.

[Bob Phillips]

Okay, I see what you mean now.

If I changed the ControlTip text to an non-ambiguous format, like d-mmm-yyyy, and it works fine.

Other than that, I wouldn't use Controltip text, I would build it from the data on the form, like

[vba]

Private Sub D1_Click()
'Denne rutine, og alle de f?lgende, repr?senterer knapperne for dagene p? formularen,
'finder den nuv?rende v?rdi af teksten p? den knap der trykkes p? og placerer den i
'den aktive celle og lukker formularen
ActiveCell.Value = DateValue(D1.Caption & "-" & Me.CB_Mth & "-" & Me.CB_Yr)
Unload Me

End Sub
[/vba]

[perhol]

Your suggestion about an non-ambiguous format, like d-mmm-yyyy, works fine.
Thankyou