Private Sub CommandButton2_Click()
Dim i As Integer
Dim ii As Integer
Dim X As Integer
Dim match As Byte
If Me.ComboCDF.Value = '' Or _
Me.ComboNatDep.Value = '' Or _
Me.TxtMontVar.Value = '' Or _
Me.Comment.Value = '' Or _
Me.ComboNumCip.Value = '' And Me.ComboNumCip.Enabled = True Or _
Me.ComboNatCIP.Value = '' And Me.ComboNumCip.Enabled = True Then
msgbox 'Veuillez renseigner tous les champs qui ont un * !', vbCritical, 'ERREUR SAISIE'
Exit Sub
End If
i = Sheets('LE').Range('a65536').End(xlUp).Row + 1 'première ligne vide en colonne A
'contrôle de duplication
For X = 4 To i
If ComboCDF = Sheets('LE').Range('A' & X) And _
ComboNatDep = Sheets('LE').Range('C' & X) And _
ComboNumCip = Sheets('LE').Range('E' & X) And _
ComboNatCIP = Sheets('LE').Range('F' & X) Then
match = match + 1: ii = X
End If
Next X
If match > 0 Then
msgbox 'Duplication trouvée dans la database ! Positionnez-vous sur la ligne et cliquez sur modifier !', vbCritical, 'DUPLICATION'
Exit Sub
End If
If i < 4 Then i = 4
With Sheets('LE')
.Cells(i, 1) = Me.ComboCDF.Text
.Cells(i, 2) = CDbl(Me.TxtMontBud.Value)
.Cells(i, 3) = Me.ComboNatDep
.Cells(i, 4) = CDbl(Me.TxtMontVar.Value)
.Cells(i, 5) = Me.ComboNumCip
.Cells(i, 6) = Me.ComboNatCIP
'.Cells(i, 7) = CDbl(Me.TxtMontCIP.value)
.Cells(i, 9) = Me.Comment
.Cells(i, 10) = CDbl(Me.TxtMontLE.Value)
.Cells(i, 11) = Me.Label29
.Cells(i, 12) = Me.TxtSection
End With
ActiveWorkbook.Names.Add Name:='Tab_LE', RefersToR1C1:='='LE'!R1C1:' & 'R' & i & 'C' & 12
Unload LE
'Mise en forme
Sheets('LE').Cells.EntireColumn.AutoFit
Columns('L:L').ColumnWidth = 0.08
Application.Goto Reference:='Tab_LE'
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range('A1:K2').Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollColumn = 3
Range('C4').Select
End Sub