本帖最後由 av8d 於 2013-6-17 11:01 編輯
修改前- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim Rng As Range, I As Integer
- With Target
- '.Select '多一次確認
- If .Column = 4 Or .Column = 12 Then
- If Cells(.Row, 4) <> "" And Cells(.Row, 12) <> "" Then
- For I = 1 To Cells(Rows.Count, .Column).End(xlUp).Row
- If Cells(I, 4) = Cells(.Row, 4) And Cells(I, 12) = Cells(.Row, 12) And I <> .Row Then
- If Rng Is Nothing Then
- Set Rng = Union(Cells(I, 4), Cells(I, 12))
- Else
- Set Rng = Union(Rng, Cells(I, 4), Cells(I, 12))
- End If
- End If
- Next
- If Not Rng Is Nothing Then
- Set Rng = Union(Rng, Cells(.Row, 4), Cells(.Row, 12))
- Rng.Select
- 'MsgBox Cells(.Row, 4) & vbNewLine & Cells(.Row, 12) & vbNewLine & "出現重複 "
- UserForm2.Show
- End If
- End If
- End If
- End With
-
- If Target.Cells.Count > 1 Then Exit Sub
- If Union(Target, [K:K]).Address <> [K:K].Address Then Exit Sub
- If Target = "紅色" Then
- UserForm1.Show
- End If
- End Sub
複製代碼 修改後- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim Rng As Range, I As Integer
- With Target
- '.Select '多一次確認
- If .Column = 4 Or .Column = 12 Then
- If Cells(.Row, 4) <> "" And Cells(.Row, 12) <> "" Then
- For I = 1 To Cells(Rows.Count, .Column).End(xlUp).Row
- If Cells(I, 4) = Cells(.Row, 4) And Cells(I, 12) = Cells(.Row, 12) And I <> .Row Then
- If Rng Is Nothing Then
- Set Rng = Union(Cells(I, 4), Cells(I, 12))
- Else
- Set Rng = Union(Rng, Cells(I, 4), Cells(I, 12))
- End If
- End If
- Next
- If Not Rng Is Nothing Then
- Set Rng = Union(Rng, Cells(.Row, 4), Cells(.Row, 12))
- Rng.Select
- 'MsgBox Cells(.Row, 4) & vbNewLine & Cells(.Row, 12) & vbNewLine & "出現重複 "
- UserForm2.Show
- End If
- End If
- End If
- End With
-
- If Target.Cells.Count > 1 Then Exit Sub
- If Union(Target, [K:K]).Address <> [K:K].Address Then Exit Sub
- If Target = "紅色" Then
- UserForm1.Show
- End If
-
- If Target.Cells.Count > 1 Then Exit Sub
- If Union(Target, [K:K]).Address <> [K:K].Address Then Exit Sub
- If Target = "藍色" Then
- UserForm3.Show
- End If
- End Sub
複製代碼 測試結果:UserForm1和UserForm2有出來,新增的UserForm3沒出來。 |