1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
| Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n&, rg As Range
On Error GoTo fin
Application.ScreenUpdating = False
If Target.Column = 10 Then
With Application.WorksheetFunction
If .CountIf(ActiveSheet.Columns(Target.Column), Target.Value) > 1 Then
Set rg = ActiveSheet.Columns(10).Find( _
What:=Target.Value, After:=Target)
rg.ClearContents
End If
End With
End If
fin:
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Not Application.Intersect(Target, Range("L5:l20,l23:l30,l33:l36,l39:l40:l43")) Is Nothing Then 'modifie ton tableau
If Target.Value = "ACTIVE" Then
Target.Value = "EN ATTENTE"
Else
Target.Value = "ACTIVE"
End If
Else
If Not Intersect(Target, Range("B3:B65556")) Is Nothing Then
UserForm1.TextBox2 = ActiveCell.Value
UserForm1.TextBox1 = ActiveCell.Offset(0, 17).Value
UserForm1.TextBox3 = ActiveCell.Offset(0, 1).Value
UserForm1.TextBox4 = ActiveCell.Offset(0, 2).Value
UserForm1.TextBox5 = ActiveCell.Offset(0, 3).Value
UserForm1.TextBox6 = ActiveCell.Offset(0, 6).Value
UserForm1.TextBox7 = ActiveCell.Offset(0, 5).Value
UserForm1.TextBox8 = ActiveCell.Offset(0, 4).Value
UserForm1.Show
Else
If Not Intersect(Target, Range("K5:k200")) Is Nothing Then
If Target.Value = "TICKET" Then
Sheets("Ticket").Range("A6").Value = ActiveCell.Offset(0, -9)
Sheets("Ticket").Range("A9").Value = ActiveCell.Offset(0, -8)
Sheets("Ticket").Range("A14").Value = ActiveCell.Offset(0, -3)
Sheets("Ticket").Range("a7").Value = ActiveCell.Offset(0, 8)
Sheets("Ticket").Range("D2").Value = ActiveCell.Offset(0, -1)
Sheets("Ticket").Visible = True
Sheets("Ticket").Select
ActiveSheet.PageSetup.PrintArea = "A1:E18"
ActiveSheet.PrintOut
Sheets("Ticket").Visible = False
End If
End If
End If
End If
End Sub |
Partager