Excel VBA SpinButton获取时间为小数且数据异常的技术求助

我在Excel里做了个数据记录用的用户窗体，分两个Frame：一个用TextBox、ComboBox做数据录入，另一个展示已录入的数据。加了SpinButton控件用来循环浏览Excel里的数据，数据包含起止日期和时间。现在日期能正常获取格式，但时间返回小数，出不来hh:mm格式。另外试直接用单元格引用时，点SpinButton后整行12的数据都变成时间格式，还出现循环引用问题。

问题代码 #

Private Sub SpinButton1_Change()
Dim s1, s2
Dim cAdd1 As String
Dim cAdd2 As String

Dim M As VbMsgBoxResult
cAdd1 = "L"
cAdd2 = "AA"

If SpinButton1.Value > 1 Then
s2 = SpinButton1.Value

s1 = "A" & s2
TextBoxEntry.ControlSource = s1

s1 = "B" & s2
ComboBoxOperator.ControlSource = s1

s1 = "C" & s2
TextSales.ControlSource = s1

s1 = "D" & s2
TextCustomer.ControlSource = s1

s1 = "E" & s2
TextJobNo.ControlSource = s1

s1 = "F" & s2
TextTitle.ControlSource = s1

s1 = "G" & s2
TextPagestxt.ControlSource = s1

s1 = "H" & s2
TextPagescvr.ControlSource = s1

s1 = "I" & s2
TextJobsize.ControlSource = s1

s1 = "J" & s2
TextColor.ControlSource = s1

s1 = "K" & s2
ComboBoxDate.ControlSource = s1

's1 = "L" & s2
'ComboBoxTime.ControlSource = s1 ' Giving time in decimal

Worksheets("Sheet1").Cells(12, s2).Value = timeValue(ComboBoxTime.Text)  'Data change and circular reference came when back spin

'Range("S1").Value = timeValue(ComboBoxTime.Text)
s1 = "M" & s2
Textsplop.ControlSource = s1

s1 = "N" & s2
ComboBoxStatus.ControlSource = s1

s1 = "O" & s2
TextPPop.ControlSource = s1

s1 = "P" & s2
ComboBoxOPStatus.ControlSource = s1

s1 = "Y" & s2
ComboBoxCorrection.ControlSource = s1

s1 = "Z" & s2
ComboBoxCDate.ControlSource = s1

's1 = "AA" & s2
'ComboBoxCTime.ControlSource = s1 ' Giving time in decimal required in HH:MM

Worksheets("Sheet1").Cells(27, s2).Value = timeValue(ComboBoxCTime.Text) 'Complete row data change to time.

s1 = "V" & s2
ComboBoxJobtype.ControlSource = s1
End If
End Sub

问题现象 #

• 使用ControlSource获取时间时，返回的是小数而非hh:mm格式
• 尝试直接单元格赋值后，点击SpinButton会导致第12行整行数据被转换为时间格式，同时出现循环引用

对比情况 #

• 使用前：表格数据格式正常，日期、时间及其他数据各列显示正确
• 使用后：第12行所有单元格变为时间格式，原数据被篡改

解决方案 #

1. 解决时间显示为小数的问题 #

Excel里时间本质是小数（1代表一整天），直接用ControlSource绑定会显示原始值。要让控件显示hh:mm格式，放弃ControlSource，手动读取单元格值并格式化：

' 读取开始时间单元格并格式化显示到ComboBox
s1 = "L" & s2
ComboBoxTime.Text = Format(Worksheets("Sheet1").Range(s1).Value, "hh:mm")

' 同理处理结束时间
s1 = "AA" & s2
ComboBoxCTime.Text = Format(Worksheets("Sheet1").Range(s1).Value, "hh:mm")

2. 修复整行格式被篡改的问题 #

你代码里的单元格引用搞反了行和列：Cells(行号, 列号)，之前写的Cells(12, s2)是修改第12行的第s2列，而实际应该修改第s2行的L列（第12列）。修正后的赋值代码：

' 保存开始时间到对应单元格（L列，s2行）
Worksheets("Sheet1").Cells(s2, "L").Value = TimeValue(ComboBoxTime.Text)
' 保存结束时间到对应单元格（AA列，s2行）
Worksheets("Sheet1").Cells(s2, "AA").Value = TimeValue(ComboBoxCTime.Text)

3. 避免循环引用 #

循环引用是因为控件绑定和单元格赋值互相触发事件，建议：

• 全部改用手动读取/写入单元格，不用ControlSource
• 操作单元格前禁用事件，操作完再开启：

Private Sub SpinButton1_Change()
    Dim s1, s2
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    ' 禁用事件防止循环触发
    Application.EnableEvents = False
    
    If SpinButton1.Value > 1 Then
        s2 = SpinButton1.Value
        
        ' 手动读取各单元格值到控件（示例，其他控件同理）
        TextBoxEntry.Text = ws.Cells(s2, "A").Value
        ComboBoxOperator.Text = ws.Cells(s2, "B").Value
        ComboBoxDate.Text = Format(ws.Cells(s2, "K").Value, "yyyy-mm-dd") ' 日期格式化
        ComboBoxTime.Text = Format(ws.Cells(s2, "L").Value, "hh:mm") ' 时间格式化
        ComboBoxCDate.Text = Format(ws.Cells(s2, "Z").Value, "yyyy-mm-dd")
        ComboBoxCTime.Text = Format(ws.Cells(s2, "AA").Value, "hh:mm")
        ' ...其他控件按此格式补充...
    End If
    
    ' 恢复事件
    Application.EnableEvents = True
End Sub

额外建议 #

• 把数据保存逻辑单独放到“保存”按钮里，不要在SpinButton的Change事件里做保存，避免误操作
• 给SpinButton设置合理的Min和Max值，防止超出数据行范围

内容的提问来源于stack exchange，提问作者Ashish Srivastava