麻辣家族討論版版 - Powered by Discuz! Board

Dim d As Object, Msg As Boolean '這表單模組的私用變數
Private Sub UserForm_Initialize()
TextBoxWorkOrderNumber = New_TextBoxWorkOrderNumber
TextBoxProductNumber_MakeList
TextBoxMachineNumber_MakeList
Msg = True
End Sub
Private Sub TextBoxProductNumber_Change()
With ComboBoxProcess ' 製程控制項
.Clear
If TextBoxProductNumber.ListIndex > -1 Then
If d(TextBoxProductNumber.Value).Rows.Count = 1 Then
.AddItem d(TextBoxProductNumber.Value).Cells(2) '料號只有一個
Else
.List = d(TextBoxProductNumber.Value).Columns(2).Value '相同料號不只一個的製程範圍
End If
.ListIndex = 0
End If
End With
xChicked
End Sub
Private Sub ComboBoxProcess_Change()
LabeExceptTimeShow.Caption = ""
LabelProcessTimeShow.Caption = ""
With ComboBoxProcess
If .ListIndex > -1 Then
' *** d(TextBoxProductNumber.Value) ->Range 物件***
LabeExceptTimeShow.Caption = d(TextBoxProductNumber.Value).Cells(.ListIndex + 1, "C")
LabelProcessTimeShow.Caption = d(TextBoxProductNumber.Value).Cells(.ListIndex + 1, "D")
End If
End With
xChicked
End Sub
Private Sub TextBoxMachineNumber_Change() '帶入機器型號的資料
With TextBoxMachineNumber
LabelMachineShow.Caption = ""
If .ListIndex > -1 Then LabelMachineShow.Caption = .List(.ListIndex, 1)
End With
xChicked
End Sub
Private Sub TextBoxWorkOrderNumber_Change()
xChicked
End Sub
Private Sub CommandButtonSend_Click()
Dim Rng As Range, BtnCode As Integer
If MsgBox("新增派工 - " & TextBoxWorkOrderNumber, vbYesNo + 16 * 2, "資料送出") = vbNo Then Exit Sub
With Sheets("新增派工")
Set Rng = .Range("A1").End(xlDown)
If Rng.Row = Rows.Count Then Set Rng = .Range("A1")
End With
With Rng.Offset(1)
.Cells(1, 1) = TextBoxWorkOrderNumber.Value '工單編號
.Cells(1, 2) = TextBoxProductNumber.Value '料號
.Cells(1, 3) = ComboBoxProcess.Text '製程
.Cells(1, 4) = TextBoxMachineNumber.Value '機器編號
.Cells(1, 5) = LabelMachineShow.Caption '機器型號
.Cells(1, 6) = LabeExceptTimeShow.Caption '除外工時
.Cells(1, 7) = LabelProcessTimeShow.Caption '單次工時
End With
TextBoxProductNumber.ListIndex = -1
TextBoxMachineNumber.ListIndex = -1
ComboBoxProcess.ListIndex = -1
BtnCode = CreateObject("WScript.Shell").popup("此檔案已自動存檔", 1, Caption)
ThisWorkbook.Save
TextBoxWorkOrderNumber = New_TextBoxWorkOrderNumber
BtnCode = CreateObject("WScript.Shell").popup("工單編號 " & TextBoxWorkOrderNumber, 2, Caption)
TextBoxWorkOrderNumber.SetFocus
End Sub
Private Sub CommandButtonExit_Click()
End
End Sub
Private Sub TextBoxProductNumber_MakeList() '製程工時:料號資料
Dim Rng As Range
Set d = CreateObject("scripting.dictionary") '字典物件
Set Rng = Sheets("製程工時").Range("A2")
Do While Rng <> ""
If d.EXISTS(Rng.Value) Then
Set d(Rng.Value) = Union(Rng.Resize(, 4), d(Rng.Value)) '料號, 製程, 除外工時,單次工時
Else
Set d(Rng.Value) = Rng.Resize(, 4) '料號, 製程, 除外工時,單次工時
End If
Set Rng = Rng.Offset(1)
Loop
With TextBoxProductNumber
.List = d.KEYS
.ListIndex = 0
End With
End Sub
Private Sub TextBoxMachineNumber_MakeList() 'List 包含( 機器編號 , 型號)
With Sheets("機器型號")
TextBoxMachineNumber.List = .Range("A2:B" & .Range("A1").End(xlDown).Row).Value
End With
TextBoxMachineNumber.ListIndex = 0
End Sub
Private Function New_TextBoxWorkOrderNumber() As String '新增工單號碼格式: XXX-1234567890
Dim New_No As Variant
With Sheets("新增派工").Range("A1").End(xlDown)
If .Row > 1 And .Cells <> "" Then
New_No = Split(.Cells, "-")
New_No(1) = Format(Val(New_No(1)) + 1, "0000000000")
New_TextBoxWorkOrderNumber = New_No(0) & "-" & New_No(1)
End If
End With
End Function
Private Sub xChicked() '防呆程式
Dim BtnCode As Integer, xOrder, Msg As Boolean
With CommandButtonSend
.Enabled = TextBoxProductNumber.ListIndex > -1 And ComboBoxProcess.ListIndex > -1 And TextBoxMachineNumber.ListIndex > -1
.Enabled = .Enabled And Len(Trim(TextBoxWorkOrderNumber)) = 14
If Len(Trim(TextBoxWorkOrderNumber)) = 14 Then
xOrder = Split(TextBoxWorkOrderNumber, "-")
If UBound(xOrder) = 0 Then Msg = True
If UBound(xOrder) = 1 Then
If Len(xOrder(0)) <> 3 Then Msg = True '前三碼
If Len(xOrder(1)) <> 10 Then Msg = True '後十碼
If Len(xOrder(1)) = 10 And IsNumeric(xOrder(1)) = False Then Msg = True '後十碼需為數字
End If
If Msg Then BtnCode = CreateObject("WScript.Shell").popup("工單編號錯誤 " & TextBoxWorkOrderNumber & vbLf & "如 :xxx-1234567890", 2, Caption)
.Enabled = .Enabled And Msg = False
End If
End With
End Sub

複製代碼