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
| Sub calclongmois()
Dim col As Integer, lmois As Double
Dim h As Double, w As Double, l As Double, t As Double
Dim jmois As Integer, jdep As Date, diff As Integer
Dim i As Integer, j As Integer, k As Integer
diff = Weekday(DateSerial(Planning.Cells(1, 1), 1, 1), vbMonday)
jdep = DateSerial(Planning.Cells(1, 1), 1, 1)
lmois = 0
For i = 1 To 12 'placement des 12 mois
If i <> 12 Then
jmois = DateSerial(Planning.Cells(1, 1), i + 1, 1) - jdep 'calcul du nb de jours/mois
Else
jmois = DateSerial(Planning.Cells(1, 1) + 1, 1, 1) - jdep 'dernier mois
End If
jdep = DateSerial(Planning.Cells(1, 1), i + 1, 1) 'départ
With Planning.Range("A1")
col = .Width
h = .Height
w = jmois * col / 7
l = 2 * col - (diff * col / 7) + lmois 'le premier n'est pas souvent un lundi!!!!
t = .Top
lmois = lmois + jmois * col / 7 'mois suivant
Planning.Shapes.AddTextbox(msoTextOrientationHorizontal, l, t, w, h).Select
With Selection
d = FormatDateTime(DateSerial(Planning.Cells(1, 1), i, 1), vbLongDate) 'recherche texte du mois
k = 0
For j = 1 To 2 'après 2 espaces
k = InStr(k + 1, d, " ")
Next j
.Characters.Text = Right(d, Len(d) - k)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End With
Next i
End Sub |
Partager