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
| Private Sub CommandButton1_Click()
'ma référence
Dim Sh As Shape, Ctr As Long, Ctr1 As Long, Res As Double, ResBas As String
Dim Plg As Range, test, Haut As Double
ResBas = "A1"
With Sheets("Feuil1 (2)")
.Range(.[A22], .Cells(Rows.Count, 5).End(xlUp)).ClearContents
End With
On Error Resume Next
For Each hpb In Sheets("Imp").HPageBreaks
hpb.Delete
Next
On Error GoTo 0
For Each Sh In Sheets("Imp").Shapes
Sh.Delete
Next
Sheets("Imp").Cells.Clear
Sheets("Imp").PageSetup.Orientation = xlLandscape
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Sheets("Feuil1 (2)").[A1] = i + 1
Sheets("Feuil1 (2)").Range("ZoneGraph").Copy
Sheets("Feuil1 (2)").Cells(i * 15 + 22, 1).PasteSpecial xlValues
Set Plg = Sheets("Feuil1 (2)").Cells(i * 15 + 22, 1).Resize(15, 5)
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Feuil1 (2)").Range(Plg.Address), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Imp"
With ActiveChart
.HasTitle = True
.ChartTitle.Text = ['feuil1 (2)'!A4].Value
End With
UserForm1.Hide
With Sheets("Imp")
Set Sh = .Shapes(.Shapes.Count)
Sh.Top = Range(ResBas).Top
Sh.Height = 330
Sh.Width = 530
Sh.Left = 1
ResBas = Sh.BottomRightCell.Offset(2).Address
Set hpb = .HPageBreaks.Add(before:=Range(ResBas))
End With
ListBox1.Selected(i) = False
End If
Next i
Unload UserForm1
End Sub |
Partager