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
| Sub FormGraph()
Dim y As Integer, Impr As String
Dim FormTempo As VbComponent
Dim NewButton As MSForms.CommandButton
Dim NewLabel As MSForms.Label
Dim NewComboBox As MSForms.ComboBox
Dim Formname As String
Set FormTempo = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
'Create USF
With FormTempo
.Properties("Caption") = "Choix de l'imprimante"
.Properties("Height") = 105
.Properties("Width") = 345
End With
Formname = FormTempo.Name
' Add a combobox
Set NewComboBox = FormTempo.Designer.Controls _
.Add("forms.ComboBox.1")
With NewComboBox
.Left = 12
.Top = 30
.Height = 18
.Width = 312
.Name = "Combo1"
End With
' Add a label
Set NewLabel = FormTempo.Designer.Controls _
.Add("forms.label.1")
With NewLabel
.Caption = "Choisissez l'imprimante"
.Left = 12
.Top = 6
.Height = 18
.Width = 312
End With
' Add a CommandButton
Set NewButton = FormTempo.Designer.Controls _
.Add("forms.CommandButton.1")
With NewButton
.Caption = "OK"
.Left = 144
.Top = 54
.Height = 24
.Width = 72
End With
With ThisWorkbook.VBProject.VBComponents("UserForm1").CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Sub UserForm_Initialize()"
.InsertLines Line + 2, "listPrint"
.InsertLines Line + 3, "For y = 0 To x"
.InsertLines Line + 4, "Impr = CStr(TabPrinter(y))"
.InsertLines Line + 5, "Me.Controls(""Combo1"").AddItem Impr"
.InsertLines Line + 6, "Next y"
.InsertLines Line + 7, "End Sub"
.InsertLines Line + 8, ""
'Controle evenementielle du bouton
.InsertLines Line + 9, "Private Sub CommandButton1_Click()"
.InsertLines Line + 10, "Dim xlSheet As Excel.Worksheet"
.InsertLines Line + 11, "Dim Graphs As Object"
.InsertLines Line + 12, "Set xlSheet = ThisWorkbook.Sheets(""Graph"")"
.InsertLines Line + 13, "For Each Graphs In xlSheet.ChartObjects"
.InsertLines Line + 14, "Graphs.Chart.PrintOut ActivePrinter:= Combo1.value"
.InsertLines Line + 15, "Next"
.InsertLines Line + 16, "Me.Hide"
.InsertLines Line + 17, "End Sub"
End With
' Show the form
Load VBA.UserForms.Add(Formname)
VBA.UserForms.Add(Formname).Show
reponse = MsgBox("Voulez-vous ré-imprimer les graphiques?", vbYesNo, "Fin de l'impression")
If reponse = 7 Then
ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents("UserForm1")
ElseIf reponse = 6 Then
VBA.UserForms.Add(Formname).Show
End If
End Sub |
Partager