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 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
| Private Sub Valider_Click()
Dim hde As String
Dim ha As String
Dim valhde As String
Dim valha As String
Dim lignede As Variant
Dim plageSel As Range
'Reservation.Hide
'Application.ScreenUpdating = False
lafeuille = gymnase.Value
lassociation = association.Value
lejour = jour.Value
lheurede = "'" & heurede.Value
lheurea = "'" & heurea.Value
Call TraitementAssoc
With Sheets("Recap")
ligne = .Range("A1").End(xlDown).Offset(1, 0).Row
.Range("A" & ligne).Value = lafeuille
.Cells(ligne, 2).Value = lassociation
.Cells(ligne, 3).Value = lejour
.Cells(ligne, 4).Value = lheurede
.Cells(ligne, 5).Value = lheurea
End With
With Sheets(lafeuille)
'Détermination de la ligne de et de la ligne à pour le planing
lignede = .Range("A:A").Find(lheurede).Row
If IsError(lignede) Then MsgBox "HDE pas trouvé": Exit Sub
lignea = .Range("A:A").Find(lheurea).Row
If IsError(lignea) Then MsgBox "HA pas trouvé": Exit Sub
'Recherche dans planing si la plage a affecter est deja prise
vide = 0
Select Case lejour
Case "Lundi"
If Application.CountA(Range(.Cells(lignede, 3), .Cells(lignea, 3))) > 0 Then vide = 1
Set plageSel = Range(.Cells(lignede, 3), .Cells(lignea, 3))
Case "Mardi"
If Application.CountA(Range(.Cells(lignede, 4), .Cells(lignea, 4))) > 0 Then vide = 1
Set plageSel = Range(.Cells(lignede, 4), .Cells(lignea, 4))
Case "Mercredi"
If Application.CountA(Range(.Cells(lignede, 5), .Cells(lignea, 5))) > 0 Then vide = 1
Set plageSel = Range(.Cells(lignede, 5), .Cells(lignea, 5))
Case "Jeudi"
If Application.CountA(Range(.Cells(lignede, 6), .Cells(lignea, 6))) > 0 Then vide = 1
Set plageSel = Range(.Cells(lignede, 6), .Cells(lignea, 6))
Case "Vendredi"
If Application.CountA(Range(.Cells(lignede, 7), .Cells(lignea, 7))) > 0 Then vide = 1
Set plageSel = Range(.Cells(lignede, 7), .Cells(lignea, 7))
Case "Samedi"
If Application.CountA(Range(.Cells(lignede, 8), .Cells(lignea, 8))) > 0 Then vide = 1
Set plageSel = Range(.Cells(lignede, 8), .Cells(lignea, 8))
Case "Dimanche"
If Application.CountA(Range(.Cells(lignede, 9), .Cells(lignea, 9))) > 0 Then vide = 1
Set plageSel = Range(.Cells(lignede, 9), .Cells(lignea, 9))
End Select
End With
If vide = 1 Then
MsgBox "Plage déjà occupée partiellement ou en totalité!!!"
Sheets("Recap").Range("A1").End(xlDown).EntireRow.Delete
Exit Sub
End If
'Affectation de la plage mise en gras ecriture bleue et fusion des cellules
plageSel.Borders(xlInsideHorizontal).LineStyle = xlNone
With plageSel
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
With plageSel.Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
plageSel.Value = lassociation
End Sub |
Partager