|
Private Sub CommandButton1_Click() '录入按钮
Dim txt As Control
Dim a, i As Integer
Dim br(1 To 100)
Dim list
With Worksheets("制坯工序")
a = .Cells(Rows.Count, 1).End(xlUp).Row '统计最后一个非空单元格的所在行
For i = 4 To 7
n = n + 1
If Controls("ComboBox" & i).Value <> "" And TextBox3.Value <> "" Then
a = a + 1
.Range("c" & a) = Controls("combobox" & i).Value '用变量代替模块名称
.Range("b" & a) = ComboBox8.Value
.Range("a" & a) = DTPicker1.Value
.Range("d" & a) = Controls("textbox" & i + 1).Value
hh1 = Controls("textbox" & i + 5 + 4 * (n - 1)).Value
mm1 = Controls("textbox" & i + 6 + 4 * (n - 1)).Value
.Range("e" & a) = Format(hh1 & ":" & mm1, "hh:mm")
hh2 = Controls("textbox" & i + 7 + 4 * (n - 1)).Value
mm2 = Controls("textbox" & i + 8 + 4 * (n - 1)).Value
.Range("f" & a) = Format(hh2 & ":" & mm2, "hh:mm")
.Range("g" & a) = TextBox3.Text
.Range("h" & a) = Now() '存储录入时间
End If
Next i
End With
On Error Resume Next '则在这句往后的代码就算出错也会继续执行
For Each txt In UserForm5.Controls
If txt.Name <> "DTPicker1" Then
txt.Text = ""
End If
Next
ListView1.ListItems.Clear
With Worksheets("制坯工序")
b = .Cells(Rows.Count, 1).End(xlUp).Row
For Each c In .Range("h1:h" & b)
If Format(c.Value, "yyyy-mm-dd") = Format(Date, "yyyy-mm-dd") Then
k = k + 1
br(k) = c.Row
End If
Next
S = Application.Min(br)
l = Application.Max(br)
For i = S To l
If .Cells(i, 1) = UserForm5.DTPicker1.Value Then
On Error Resume Next
Set list = ListView1.ListItems.Add(Text:=.Cells(i, 1))
Set li = list.ListSubItems.Add(Text:=.Cells(i, 2))
Set li = list.ListSubItems.Add(Text:=.Cells(i, 3))
Set li = list.ListSubItems.Add(Text:=.Cells(i, 4))
If .Cells(i, 5) <> "" Then
Set li = list.ListSubItems.Add(Text:=Format(.Cells(i, 5), "hh:mm"))
Else
Set li = list.ListSubItems.Add(Text:=.Cells(i, 5))
End If
If .Cells(i, 6) <> "" Then
Set li = list.ListSubItems.Add(Text:=Format(.Cells(i, 6), "hh:mm"))
Else
Set li = list.ListSubItems.Add(Text:=.Cells(i, 6))
End If
Set li = list.ListSubItems.Add(Text:=.Cells(i, 7))
Set li = list.ListSubItems.Add(Text:=.Cells(i, 8))
End If
Next i
End With
End Sub
|
|