User form Date Field Mask or Template

Martel

New Member
Joined
Jun 19, 2019
Messages
26
Hi, I would like a user form date input field where "dummy" characters appear in the field when field is selected i.e. dd/mm/yyyy. These characters should be greyed out. When the user starts input to the field, input would overwrite the "dummy", in black. My attempts up to now have resulted in user input removing the "dummy" characters or appearing in front of. After validating input, field characters would be black. I hope this adequately describes my query. Not sure if I am describing a Mask or a Template? In either case, I have no success yet. I wonder if this is even possible? Thanks for any response.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi
Try following codes in your Userform


Code:
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'allow numeric [0-9] / only
    KeyAscii = DatesOnly(KeyAscii)
End Sub


Private Sub TextBox1_Enter()
    SetTextBox Me.TextBox1, xlDate
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Me.TextBox1) = 0 Then SetTextBox Me.TextBox1, xlText
End Sub


Private Sub UserForm_Initialize()
    SetTextBox Me.TextBox1, xlText
End Sub


Sub SetTextBox(ByVal TextBox As Object, ByVal State As Integer)
    With TextBox
        .Value = IIf(State = xlText, "dd/mm/yyyy", "")
        .ForeColor = IIf(State = xlText, &HE0E0E0, &H80000012)
        .MaxLength = 10
    End With
End Sub


Function DatesOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
    Select Case KeyAscii
'valid entries [0-9] "/"
    Case 47, 48 To 57
'valid
    Case Else
'cancel
        KeyAscii = 0
    End Select
    Set DatesOnly = KeyAscii
End Function

Change the textbox name(s) as required

I have included a function to prevent values other than numeric or / being keyed in .

Not fully tested but hopefully will do what you want and allow you to use Function & Sub with other textboxes if needed

Dave
 
Last edited:
Upvote 0
Many thanks for responses received so far. Unfortunately suggestions offered do not achieve what I am looking for, When entering text box, user is indeed presented with a template showing "dd/mm/yyyy" greyed out. This is good and answers first part of issue. However, it remains true that as soon as user starts to type in the box, the template disappears - which I am trying to avoid. Ideally, the 'grey' template would remain in the background with user entry overtyping each character as entered. As I said before, I accept this may not be possible. Any further ideas welcome.

thanks again. Martel
 
Upvote 0
Hi,
there maybe a workaround of sorts you can try

1 - Add a Label to your userform
2 - set BackColor white
3 - Set ForeColor - light Grey & change Font Size As required
4 - Enter Caption " dd/mm/yyyy" (note space at front)

5 - Place your TextBox OVER the label
6 - Change the BackStyle of the control to fmBackStyleTransparent

Idea may need some adjustment to align as required but may do what you want.

Dave
 
Upvote 0
.
Question : Other than aesthetics ... what is the purpose of doing so ? Having the partial date remain until typed over ?
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top