CODEWITHSUNDEEP
vbauserformcell-formatting
new11
new11

Reputation: 55

Textbox & Cell Format VBA

I have a user form with a text box that allows me to enter a date, this date then copies into a column/cell formatted as a long date “1 Jan 2020”. However, when I enter a date as “01/01/2020” into the user form text box, the date does not correct to the cell format. Does anyone have any ideas on how I could fix this? This is the code:

Dim lrow As Long
Dim LO As ListObject
Dim LEO As Range
Set ws = Worksheets("2020_Data")
Set LO = ws.ListObjects("Table1")
'lrow = ws.Cells(Rows.Count, "B") .End(xlUp). .Offset(1, 0) .Row
With LO.Range.Columns(2)
Set LEO = .Find(what:="", after:=.Cells(1), LookIn:=xlValues, _
    searchorder:=xlByRows, searchdirection:=xlNext)
If Not LEO Is Nothing Then
    lrow = LEO.Row
'        MsgBox LEO.Row 'First empty row at column B
End If
End With
'rw = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookInValues) .Row + 
1
With ws
   .Cells(lrow, "B").Value = DateValue(Me.TextBox6.Value)
   .Cells(lrow, "C").Value = DateValue(Me.TextBox21.Value)
   .Cells(lrow, "F").Value = Me.ComboBox2.Value
   .Cells(lrow, "G").Value = Me.ComboBox3.Value
   .Cells(lrow, "I").Value = Me.TextBox16.Value
   .Cells(lrow, "H").Value = Me.TextBox17.Value
   .Cells(lrow, "J").Value = Me.TextBox18.Value
End With
 'Clear Input Controls.
 Me.TextBox6 = ""
 Me.TextBox21 = ""
 Me.ComboBox2 = ""
 Me.ComboBox3 = ""
 Me.TextBox16 = ""
 Me.TextBox17 = ""
 Me.TextBox18 = "" 
End Sub

Thanks in advance for your help! :)

Upvotes: 1

Views: 866

Answers (1)

Variatus
Variatus

Reputation: 14383

The code below must be installed in your UserForm's code module. It will run when you complete the change in TextBox6.

Private Sub TextBox6_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim TbxVal As String

With TextBox6
    TbxVal = .Value
    If IsDate(TbxVal) Then
        Cells(6, "B").Value = DateValue(TbxVal)
        With TextBox21
            If Len(.Value) = 0 Then
                ' this command would prevent any event procedure
                ' attached to TextBox21 from being executed
                ' based on the entry about to be made.
                Application.EnableEvents = False
                .Value = Format(DateValue(TbxVal) + 6, "dd mmm, yyyy")
                Application.EnableEvents = True
            End If
        End With
    Else
        MsgBox "Enter a valid date", vbExclamation, "Invalid date format"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(TbxVal)
        Cancel = True
    End If
End With

End Sub

It will first check if you entered a date and complain if your entry wasn't understood. Then it will write the entry to the sheet using the DateValue, not the text string. Next it will write that same DateValue increased by 6 and then formatted as a date string to TextBox21. Observe how the string from TextBox6 is converted to a true date (a number), used in a calculation, and then converted back to a string that can be fed to the TextBox.

You may wonder why the value of TextBox6 is assigned to a variable instead of being used directly. For one, the addressing is easier that way. For another, it's easier to give a meaningful name to the variable. And, finally, accessing a variable is faster than referring to the user form (the same holds true for variables stored in worksheets).

Upvotes: 0

Related Questions