Formatting VBA Userform Text boxes Without Deleting the Actual Contents

[Neileee]

I have userform text boxes with dates in them in the format "dd/mm/yyyy". I want to be able to show however only the "dd". The issue is that if I use this code:

Label20 = Format(Label20, "dd")

Then the actual contents of my data gets lost. Ie using the code

msgbox label20

returns only the day value and not the entire date.

Is there a way to format the text box so only the day value shows but still keeps the entire data?

Thanks!

 

[Kenneth Hobson]

Use the Tag property to store secondary information.

 

[mikerickson]

One technique would be to have the .Text property of the text box hold (and show) the formatted value, while the .Tag property holds the actual value.

If you use code like this, you will have to look to the .Tag property for the date the text box holds, as in the CommandButton1_Click code below.

Private Sub TextBox1_Change()
    If TextBox1.Parent.ActiveControl.Name <> TextBox1.Name Then
        ProcessTextBoxDate TextBox1
    End If
End Sub

Private Sub TextBox1_AfterUpdate()
    ProcessTextBoxDate TextBox1
End Sub

Private Sub ProcessTextBoxDate(aTextbox As MSForms.TextBox)
    Dim dtTemp As Date

    With aTextbox

        If IsDate(.Text) Then
            .Tag = CDate(.Text)
        ElseIf 1 <= Val(.Text) And Val(.Text) <= 31 Then
            If Not IsDate(.Tag) Then .Tag = CDate(Int(Now()))
            dtTemp = DateValue(.Tag)
            .Tag = DateSerial(Year(dtTemp), Month(dtTemp), Val(.Text))
        Else
            .Tag = .Text
        End If

        TextBox1.Text = Format(.Tag, "dd")
      
    End With
End Sub

Private Sub CommandButton1_Click()
    MsgBox "The date in TextBox1 is " & CDate(TextBox1.Tag)
End Sub

 

[mikerickson]

On further reflection, using the .ControlTipText property to store the underlying full date would be a better user interface. The user can see the dd value in the TextBox and see the full date by hovering over the text box.

Private Sub TextBox1_Change()
    If TextBox1.Parent.ActiveControl.Name <> TextBox1.Name Then
        ProcessDateTextBox TextBox1
    End If
End Sub

Private Sub TextBox1_AfterUpdate()
    ProcessDateTextBox TextBox1
End Sub

Private Sub ProcessDateTextBox(aTextbox As MSForms.TextBox)
    Dim dtTemp As Date
    With aTextbox

        If IsDate(.Text) Then
            .ControlTipText = CDate(.Text)

        ElseIf (1 <= Val(.Text)) And (Val(.Text) <= 31) Then
            If Not IsDate(.ControlTipText) Then .ControlTipText = Date
            dtTemp = DateValue(.ControlTipText)
            .ControlTipText = DateSerial(Year(dtTemp), Month(dtTemp), Val(.Text))

        Else
            .ControlTipText = 0
        End If
       
    End With
End Sub

Private Sub UserForm_Initialize()
    ProcessDateTextBox TextBox1
End Sub

Private Sub CommandButton1_Click()
    MsgBox "The date in TextBox1 is " & CDate(TextBox1.ControlTipText)
End Sub