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
| Private Sub CommandButton1_Click()
Dim dest As Range
Dim i As Byte
Dim ch As String
Dim AuditBook As Workbook, DiversBook As Workbook, CurBook As Workbook
Dim Recherche As Range
If TextBox1.Value = "" Then
MsgBox "vous devez entrer une plaque d'immatriculation avant de pouvoir continuer"
Exit Sub
End If
Application.ScreenUpdating = False
Set CurBook = ActiveWorkbook
'Ouverture du fichier Audit
Chemin = ActiveWorkbook.Path & "\" & CurBook.Worksheets("Master").Range("G2").Value ''Change le chemin au besoin
If Not IsOpen(CurBook.Worksheets("Master").Range("G2").Value) Then
Workbooks.Open Filename:=Chemin
Else
Windows(CurBook.Worksheets("Master").Range("G2").Value).Activate
End If
Set AuditBook = ActiveWorkbook
'Ouverture du fichier Divers
Chemin = ActiveWorkbook.Path & "\" & CurBook.Worksheets("Master").Range("G3").Value 'Change le chemin au besoin
If Not IsOpen(CurBook.Worksheets("Master").Range("G3").Value) Then
Workbooks.Open Filename:=Chemin
Else
Windows(CurBook.Worksheets("Master").Range("G3").Value).Activate
End If
Set DiversBook = ActiveWorkbook
With AuditBook.Worksheets("Auditeur")
Set dest = IIf(.Range("A2").Value = "", .Range("A2"), .Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0))
dest.Offset(0, 0).Value = TextBox1.Value
dest.Offset(0, 6).Value = TextBox3.Value
For i = 1 To 4
If Me.Controls("OptionButton" & i).Value = True Then
dest.Offset(0, 3).Value = Me.Controls("OptionButton" & i).Caption
Exit For
End If
Next i
Set Recherche = DiversBook.Sheets("Entretien").Columns("A").Find(Me.TextBox1, LookIn:=xlValues, LookAt:=xlWhole)
If Not Recherche Is Nothing Then
dest.Offset(0, 1).Value = DiversBook.Sheets("Entretien").Range("B" & Recherche.Row)
dest.Offset(0, 2).Value = DiversBook.Sheets("Entretien").Range("C" & Recherche.Row)
Else
MsgBox "Le numéro de plaque n'existe pas dans le fichier Divers"
End If
dest.Offset(0, 5).Value = CurBook.Worksheets("Master").Range("A3").Value
dest.Offset(0, 4).Value = CurBook.Worksheets("Master").Range("A2").Value
AuditBook.Close True
DiversBook.Close True
End With
Worksheets("Master").Select
Range("A1").Select
Unload Me
Application.ScreenUpdating = False
Set CurBook = Nothing
Set AuditBook = Nothing
Set DiversBook = Nothing
End Sub
Function IsOpen(Nom As String)
On Error Resume Next
IsOpen = IsObject(Workbooks(Nom))
On Error GoTo 0
End Function |
Partager