- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
5#
發表於 2016-6-19 08:25
| 只看該作者
本帖最後由 GBKEE 於 2016-6-22 05:00 編輯
回復 3# bsy4life
順便請教一下combobox無法用滑鼠滾動是要怎麼改
不是有方向鍵可用
work 表單模組程式碼試試看
TextBoxProductNumber,TextBoxMachineNumber,ComboBoxProcess
皆改為 ComboBox 的控制項 其 BoundColumn 屬性 請指定=1- 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
|
|
發表於 2016-6-15 12:13
| 
