文本框和单元格格式 VBA

Question

我有一个用户表单，其中有一个文本框，允许我输入日期，然后将该日期复制到格式化为长日期"2020年1月1日"的列/单元格中。但是，当我在用户表单文本框中输入日期"01/01/2020"时，该日期不会更正为单元格格式。有人知道我该怎么解决这个问题吗？这是代码：

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

提前感谢您的帮助！：(

Answer 1

下面的代码必须安装在UserForm的代码模块中。当您在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

结束子

它会首先检查你是否输入了日期，如果你的输入不被理解，它会抱怨。然后，它将使用DateValue而不是文本字符串将条目写入工作表。接下来，它将把相同的DateValue加6，然后格式化为日期字符串写入TextBox21。观察TextBox6中的字符串如何转换为真实日期(一个数字(，用于计算，然后再转换回可以输入到TextBox的字符串。

您可能想知道为什么TextBox6的值被分配给变量而不是直接使用。首先，这样寻址更容易。另一方面，为变量指定一个有意义的名称更容易。最后，访问变量比引用用户表单更快(存储在工作表中的变量也是如此(。