User form Date Field Mask or Template

[Martel]

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.​

 

[Logit]

.
This is one method :

https://stackoverflow.com/questions/21536192/textbox-text-disappear-on-text-entry-excel-vba

 

[dmt32]

Hi
Try following codes in your Userform

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

 

[Martel]

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

 

[dmt32]

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

 

[Logit]

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