- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
39#
發表於 2016-10-25 10:23
| 只看該作者
本帖最後由 GBKEE 於 2016-10-25 10:28 編輯
回復 38# starbox520
不用很多個按鈕也不要用按鈕
TR排機&產出上,滑鼠移到E欄上的所指定的Customer,秀出表單
TR排機&產出模組的程式碼- Public Sh_Rng As Range, Sh_Ar
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If IsError(Target(1)) Then Unload frmSelector: Exit Sub
- If (Target(1).Row + 1) Mod 5 = 0 And Target(1) <> "" And Target(1).Column = 5 Then
- Set Sh_Rng = Cells(Target(1).Row, "E")
- Ex_Customer_Package
- If IsEmpty(Sh_Ar) Then MsgBox Sh_Rng & "-" & Sh_Rng(1, 2) & vbLf & "找不到": Exit Sub
- Unload frmSelector
- frmSelector.Show False
- Else
- Unload frmSelector
- End If
- End Sub
- Private Sub Ex_Customer_Package()
- Dim i As Integer, ii As Integer, Ar
- Sh_Ar = Ar: i = 2
- With Sheets("工作表2")
- Do While .Cells(i, 1) <> ""
- If .Cells(i, 1) = Sh_Rng And .Cells(i, 2) = Sh_Rng(1, 2) Then
- If IsEmpty(Ar) Then ReDim Ar(1 To 8, 1 To 1) Else ReDim Preserve Ar(1 To 8, 1 To UBound(Ar, 2) + 1)
- For ii = 1 To 8
- Ar(ii, UBound(Ar, 2)) = .Cells(i, ii).Text
- Next
- End If
- i = i + 1
- Loop
- End With
- If IsEmpty(Ar) Then Exit Sub
- Sh_Ar = Application.Transpose(Ar)
- End Sub
複製代碼 frmSelector表單模阻的程式碼- Option Explicit
- Private Sub UserForm_Initialize()
- StartupPosition = 0
- Top = 0
- Left = Windows(1).Width - Width
- lstSelector_設定
- End Sub
- Private Sub lstSelector_設定()
- With lstSelector
- .ColumnCount = 8
- .MultiSelect = 1 ' MultiSelect 屬性 指定物件是否接受多重選取。
- If Not IsEmpty(Sheets("TR排機&產出").Sh_Ar) Then .List = Sheets("TR排機&產出").Sh_Ar
- End With
- End Sub
- Private Sub CommandButton1_Click()
- Dim AA, i As Integer, ii As Integer
- With lstSelector
- For i = 0 To .ListCount - 1
- If .Selected(i) Then
- If IsEmpty(AA) Then ReDim AA(1 To 4, 1 To 1) Else ReDim Preserve AA(1 To 4, 1 To UBound(AA, 2) + 1)
- For ii = 1 To 4
- AA(ii, UBound(AA, 2)) = .List(i, ii - 1)
- Next
- End If
- Next
- End With
- If IsEmpty(AA) Then
- MsgBox "你沒有選取資料"
- ElseIf UBound(AA, 2) > 4 Then
- MsgBox "你選取 超過 4 筆 資料"
- Else
- If MsgBox("共 選取 " & UBound(AA, 2) & " 筆資料" & vbLf & "確定輸入", vbYesNo) = vbYes Then
- With Sheets("TR排機&產出").Sh_Rng.Offset(1)
- .Resize(4, 4) = ""
- .Resize(UBound(AA, 2), UBound(AA)) = Application.Transpose(AA)
- End With
- End If
- End If
- End Sub
複製代碼 |
|