GTA54
Messages postés414Date d'inscriptionjeudi 23 février 2012StatutMembreDernière intervention 7 août 2019
-
Modifié par GTA54 le 3/06/2013 à 14:51
GTA54
Messages postés414Date d'inscriptionjeudi 23 février 2012StatutMembreDernière intervention 7 août 2019
-
4 juin 2013 à 08:51
Bonjour,
J'ai créé une macro qui s'effectue sur le premier onglet d'un fichier qui s'ouvre grâce à ma macro.
J'aimerais maintenant que cette macro s'effectue sur tous les onglets les uns après les autres (sachant que je ne connais pas d'avance les noms d'onglets et que je ne connais pas le nombre d'onglets non plus !)
Voici la macro (avec userform et différents fichier ouvert) :
Private Sub CommandButton4_Click()
Userform1.Hide
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H9").Value = Userform1.TextBox3.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D9").Value = Userform1.TextBox4.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("F9").Value = Userform1.TextBox5.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H10").Value = Userform1.TextBox6.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E10").Value = Userform1.TextBox7.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D6").Value = Userform1.TextBox24.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H6").Value = Userform1.TextBox25.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D7").Value = Userform1.TextBox26.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("I7").Value = Userform1.ComboBox3.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E8").Value = Userform1.TextBox28.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("J8").Value = Userform1.TextBox29.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("G7").Value = Userform1.TextBox30.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B75").Value = "Commentaires : " & Userform1.TextBox31.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E3").Value = Userform1.TextBox26.Value & Userform1.TextBox32.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("O56").Value = Userform1.TextBox33.Value
If OptionButton6.Value = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("M7").Value = 0
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton1.Value = True
tolérance = "Tolérance Bilatérale "
cote = "Inf : " & TextBox3 + vbCr + "Nominal : " & TextBox4 + vbCr + "Sup : " & TextBox5
Else
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton1.Value = False
End If
If OptionButton7.Value = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("M7").Value = 1
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton2.Value = True
tolérance = "Tolérance Unilatérale Supérieure "
cote = "Sup : " & TextBox5
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D9").Value = ""
Else
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton2.Value = False
End If
If OptionButton8.Value = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("M7").Value = 2
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton3.Value = True
tolérance = "Tolérance Unilatérale Inférieure "
cote = "Inf : " & TextBox3
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D9").Value = ""
Else
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton3.Value = False
End If
'If TextBox25.Value = "" Then
'MsgBox "Veuillez remplir la case Désignation Caractéristique.", vbExclamation, "Erreur"
'Userform1.Show
'Else
If OptionButton9 = True Then
TextBox7 = ""
tol = ""
val = "Cmk de " & TextBox6
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K40").Value = "Cmk"
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K39").Value = "Cm"
capa = "Cmk "
ElseIf OptionButton10 = True Then
TextBox6 = ""
tol = ""
val = "Cpk de " & TextBox7
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K40").Value = "Cpk"
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K39").Value = "Cp"
capa = "Cpk "
ElseIf capa = "" Then
MsgBox "Type de capabilité non sélectionné." + vbCr + "" + vbCr + "Cliquez sur Annuler lors du prochain message.", vbExclamation, "ATTENTION"
End If
If tolérance = "" Then
MsgBox "Type de tolérance non sélectionné." + vbCr + "" + vbCr + "Cliquez sur Annuler lors du prochain message.", vbExclamation, "ATTENTION"
End If
Workbooks("Q-R-055-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Activate
Dim x As Variant, r As Long, c As Long
Application.ScreenUpdating = False
x = Range("z37:aa37").Value
For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2)
x(r, c) = Replace(x(r, c), ".", ",")
Next c: Next r
Range("z37:aa37").Value = x
z = Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H6").Value & "- " & val & ".xls"
fichier = b & "\" & z
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").SaveAs fichier
MsgBox "Le fichier est maintenant enregistré et vous pouvez effectuer des modifications si nécessaire." + vbCr + "" + vbCr + "N'oubliez pas d'Enregistrer/Fermer le fichier après modification.", vbExclamation, "Nobel Automotive"
Workbooks(z).Activate
'End If
Case vbCancel 'new
Userform1.Show 'new
End Select 'new
End Sub
Cette macro fait référence aussi à celle ci :
Sub LoiNormale()
Dim Chemin As String
Userform1.MultiPage1.Value = 3
Userform1.Hide
Select Case MsgBox("Votre choix est : Conversion Loi Normale" + vbCr + " " + vbCr + "Voulez-vous continuer et choisir le fichier a convertir ?", vbOKCancel + vbQuestion, "NOBEL AUTOMOTIVE")
Case vbOK
Chemin = Sheets("DATAS").Range("D9")
Workbooks.Open Filename:=Chemin & "\Fichiers Type\Q-R-053-FR-1 - Capability Study - Normal Law"
If Application.Dialogs(xlDialogOpen).Show = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B13").Value = Workbooks(a).Sheets(1).Range("C47").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B14").Value = Workbooks(a).Sheets(1).Range("C48").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B15").Value = Workbooks(a).Sheets(1).Range("C49").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B16").Value = Workbooks(a).Sheets(1).Range("C50").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B17").Value = Workbooks(a).Sheets(1).Range("C51").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B18").Value = Workbooks(a).Sheets(1).Range("C52").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B19").Value = Workbooks(a).Sheets(1).Range("C53").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B20").Value = Workbooks(a).Sheets(1).Range("C54").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B21").Value = Workbooks(a).Sheets(1).Range("C55").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B22").Value = Workbooks(a).Sheets(1).Range("C56").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B23").Value = Workbooks(a).Sheets(1).Range("C57").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B24").Value = Workbooks(a).Sheets(1).Range("C58").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B25").Value = Workbooks(a).Sheets(1).Range("C59").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B26").Value = Workbooks(a).Sheets(1).Range("C60").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B27").Value = Workbooks(a).Sheets(1).Range("C61").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C13").Value = Workbooks(a).Sheets(1).Range("C62").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C14").Value = Workbooks(a).Sheets(1).Range("C63").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C15").Value = Workbooks(a).Sheets(1).Range("C64").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C16").Value = Workbooks(a).Sheets(1).Range("C65").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C17").Value = Workbooks(a).Sheets(1).Range("C66").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C18").Value = Workbooks(a).Sheets(1).Range("C67").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C19").Value = Workbooks(a).Sheets(1).Range("C68").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C20").Value = Workbooks(a).Sheets(1).Range("C69").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C21").Value = Workbooks(a).Sheets(1).Range("C70").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C22").Value = Workbooks(a).Sheets(1).Range("C71").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C23").Value = Workbooks(a).Sheets(1).Range("C72").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C24").Value = Workbooks(a).Sheets(1).Range("C73").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C25").Value = Workbooks(a).Sheets(1).Range("C74").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C26").Value = Workbooks(a).Sheets(1).Range("C75").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C27").Value = Workbooks(a).Sheets(1).Range("C76").Value
Userform1.TextBox4.Value = Workbooks(a).Sheets(1).Range("C39").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D6").Value = Userform1.TextBox24.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H6").Value = Userform1.TextBox25.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D7").Value = Userform1.TextBox26.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("I7").Value = Userform1.ComboBox3.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E8").Value = Userform1.TextBox28.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("J8").Value = Userform1.TextBox29.Value
Workbooks(a).Close
Workbooks("Module de conversion TEST.xls").Activate
Heikro
Messages postés40Date d'inscriptionmardi 28 mai 2013StatutMembreDernière intervention 5 mars 20198 3 juin 2013 à 15:11
Bonjour,
si j'ai bien compris :
Dim WS_Count As Integer
'Comptage du nombre d'onglets
WS_Count = ActiveWorkbook.Worksheets.Count
'Boucle pour faire ta macro sur chaque onglet
For a = 1 To WS_Count
ActiveWorkbook.Worksheets(a).Select
Ta_macro
Next
Ca effectuera ta macro dans chacun des onglets normalement.
GTA54
Messages postés414Date d'inscriptionjeudi 23 février 2012StatutMembreDernière intervention 7 août 201927 3 juin 2013 à 15:36
Merci.
Mais je la place où ta macro ?
GTA54
Messages postés414Date d'inscriptionjeudi 23 février 2012StatutMembreDernière intervention 7 août 201927 3 juin 2013 à 15:44
Je vais décrire l'enchainement du Userform :
1/ Sélectionner une tolérance puis click sur CommandButton1
Macro CommandButton1 =
Private Sub CommandButton1_Click()
If OptionButton1.Value = True Then
capasCES6
ElseIf OptionButton13.Value = True Then
capasCES30
ElseIf OptionButton3.Value = True Then
LoiNormale
ElseIf OptionButton14.Value = True Then
DefautForme
End If
End Sub
En l'occurence ici je choisis la partie LoiNormale.
La macro Loi Normale se lance donc
Sub LoiNormale()
Dim Chemin As String
Userform1.MultiPage1.Value = 3
Userform1.Hide
Select Case MsgBox("Votre choix est : Conversion Loi Normale" + vbCr + " " + vbCr + "Voulez-vous continuer et choisir le fichier a convertir ?", vbOKCancel + vbQuestion, "NOBEL AUTOMOTIVE")
Case vbOK
Chemin = Sheets("DATAS").Range("D9")
Workbooks.Open Filename:=Chemin & "\Fichiers Type\Q-R-053-FR-1 - Capability Study - Normal Law"
If Application.Dialogs(xlDialogOpen).Show = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B13").Value = Workbooks(a).Sheets(1).Range("C47").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B14").Value = Workbooks(a).Sheets(1).Range("C48").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B15").Value = Workbooks(a).Sheets(1).Range("C49").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B16").Value = Workbooks(a).Sheets(1).Range("C50").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B17").Value = Workbooks(a).Sheets(1).Range("C51").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B18").Value = Workbooks(a).Sheets(1).Range("C52").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B19").Value = Workbooks(a).Sheets(1).Range("C53").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B20").Value = Workbooks(a).Sheets(1).Range("C54").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B21").Value = Workbooks(a).Sheets(1).Range("C55").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B22").Value = Workbooks(a).Sheets(1).Range("C56").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B23").Value = Workbooks(a).Sheets(1).Range("C57").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B24").Value = Workbooks(a).Sheets(1).Range("C58").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B25").Value = Workbooks(a).Sheets(1).Range("C59").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B26").Value = Workbooks(a).Sheets(1).Range("C60").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B27").Value = Workbooks(a).Sheets(1).Range("C61").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C13").Value = Workbooks(a).Sheets(1).Range("C62").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C14").Value = Workbooks(a).Sheets(1).Range("C63").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C15").Value = Workbooks(a).Sheets(1).Range("C64").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C16").Value = Workbooks(a).Sheets(1).Range("C65").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C17").Value = Workbooks(a).Sheets(1).Range("C66").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C18").Value = Workbooks(a).Sheets(1).Range("C67").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C19").Value = Workbooks(a).Sheets(1).Range("C68").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C20").Value = Workbooks(a).Sheets(1).Range("C69").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C21").Value = Workbooks(a).Sheets(1).Range("C70").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C22").Value = Workbooks(a).Sheets(1).Range("C71").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C23").Value = Workbooks(a).Sheets(1).Range("C72").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C24").Value = Workbooks(a).Sheets(1).Range("C73").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C25").Value = Workbooks(a).Sheets(1).Range("C74").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C26").Value = Workbooks(a).Sheets(1).Range("C75").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C27").Value = Workbooks(a).Sheets(1).Range("C76").Value
Userform1.TextBox4.Value = Workbooks(a).Sheets(1).Range("C39").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D6").Value = Userform1.TextBox24.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H6").Value = Userform1.TextBox25.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D7").Value = Userform1.TextBox26.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("I7").Value = Userform1.ComboBox3.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E8").Value = Userform1.TextBox28.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("J8").Value = Userform1.TextBox29.Value
Workbooks(a).Close
Workbooks("Module de conversion TEST.xls").Activate
Et pour finir la macro pour terminer l'opération se lance avec le CommandButton4
Private Sub CommandButton4_Click()
Userform1.Hide
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H9").Value = Userform1.TextBox3.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D9").Value = Userform1.TextBox4.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("F9").Value = Userform1.TextBox5.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H10").Value = Userform1.TextBox6.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E10").Value = Userform1.TextBox7.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D6").Value = Userform1.TextBox24.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H6").Value = Userform1.TextBox25.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D7").Value = Userform1.TextBox26.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("I7").Value = Userform1.ComboBox3.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E8").Value = Userform1.TextBox28.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("J8").Value = Userform1.TextBox29.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("G7").Value = Userform1.TextBox30.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B75").Value = "Commentaires : " & Userform1.TextBox31.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E3").Value = Userform1.TextBox26.Value & Userform1.TextBox32.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("O56").Value = Userform1.TextBox33.Value
If OptionButton6.Value = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("M7").Value = 0
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton1.Value = True
tolérance = "Tolérance Bilatérale "
cote = "Inf : " & TextBox3 + vbCr + "Nominal : " & TextBox4 + vbCr + "Sup : " & TextBox5
Else
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton1.Value = False
End If
If OptionButton7.Value = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("M7").Value = 1
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton2.Value = True
tolérance = "Tolérance Unilatérale Supérieure "
cote = "Sup : " & TextBox5
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D9").Value = ""
Else
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton2.Value = False
End If
If OptionButton8.Value = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("M7").Value = 2
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton3.Value = True
tolérance = "Tolérance Unilatérale Inférieure "
cote = "Inf : " & TextBox3
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D9").Value = ""
Else
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton3.Value = False
End If
'If TextBox25.Value = "" Then
'MsgBox "Veuillez remplir la case Désignation Caractéristique.", vbExclamation, "Erreur"
'Userform1.Show
'Else
If OptionButton9 = True Then
TextBox7 = ""
tol = ""
val = "Cmk de " & TextBox6
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K40").Value = "Cmk"
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K39").Value = "Cm"
capa = "Cmk "
ElseIf OptionButton10 = True Then
TextBox6 = ""
tol = ""
val = "Cpk de " & TextBox7
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K40").Value = "Cpk"
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K39").Value = "Cp"
capa = "Cpk "
ElseIf capa = "" Then
MsgBox "Type de capabilité non sélectionné." + vbCr + "" + vbCr + "Cliquez sur Annuler lors du prochain message.", vbExclamation, "ATTENTION"
End If
If tolérance = "" Then
MsgBox "Type de tolérance non sélectionné." + vbCr + "" + vbCr + "Cliquez sur Annuler lors du prochain message.", vbExclamation, "ATTENTION"
End If
Workbooks("Q-R-055-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Activate
Dim x As Variant, r As Long, c As Long
Application.ScreenUpdating = False
x = Range("z37:aa37").Value
For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2)
x(r, c) = Replace(x(r, c), ".", ",")
Next c: Next r
Range("z37:aa37").Value = x
z = Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H6").Value & "- " & val & ".xls"
fichier = b & "\" & z
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").SaveAs fichier
MsgBox "Le fichier est maintenant enregistré et vous pouvez effectuer des modifications si nécessaire." + vbCr + "" + vbCr + "N'oubliez pas d'Enregistrer/Fermer le fichier après modification.", vbExclamation, "Nobel Automotive"
Workbooks(z).Activate
'End If
Case vbCancel 'new
Userform1.Show 'new
End Select 'new
End Sub
GTA54
Messages postés414Date d'inscriptionjeudi 23 février 2012StatutMembreDernière intervention 7 août 201927 4 juin 2013 à 08:51
3 juin 2013 à 15:36
Mais je la place où ta macro ?
3 juin 2013 à 15:44
1/ Sélectionner une tolérance puis click sur CommandButton1
Macro CommandButton1 =
Private Sub CommandButton1_Click()
If OptionButton1.Value = True Then
capasCES6
ElseIf OptionButton13.Value = True Then
capasCES30
ElseIf OptionButton3.Value = True Then
LoiNormale
ElseIf OptionButton14.Value = True Then
DefautForme
End If
End Sub
En l'occurence ici je choisis la partie LoiNormale.
La macro Loi Normale se lance donc
Sub LoiNormale()
Dim Chemin As String
Userform1.MultiPage1.Value = 3
Userform1.Hide
Select Case MsgBox("Votre choix est : Conversion Loi Normale" + vbCr + " " + vbCr + "Voulez-vous continuer et choisir le fichier a convertir ?", vbOKCancel + vbQuestion, "NOBEL AUTOMOTIVE")
Case vbOK
Chemin = Sheets("DATAS").Range("D9")
Workbooks.Open Filename:=Chemin & "\Fichiers Type\Q-R-053-FR-1 - Capability Study - Normal Law"
If Application.Dialogs(xlDialogOpen).Show = True Then
a = ActiveWorkbook.Name
b = ActiveWorkbook.Path
Userform1.TextBox33.Value = "5"
Userform1.TextBox28.Value = "Quickscope"
Userform1.TextBox29.Value = "1951-043"
Userform1.TextBox24.Value = Date
Userform1.TextBox25.Value = Workbooks(a).Sheets(1).Range("A1").Value
Userform1.TextBox32.Value = TextBox26 & " - " & TextBox25 & " - "
Me.ComboBox3.AddItem "PA 6.6"
Me.ComboBox3.AddItem "PA 6.6GF30"
Me.ComboBox3.AddItem "PA 12"
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B13").Value = Workbooks(a).Sheets(1).Range("C47").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B14").Value = Workbooks(a).Sheets(1).Range("C48").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B15").Value = Workbooks(a).Sheets(1).Range("C49").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B16").Value = Workbooks(a).Sheets(1).Range("C50").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B17").Value = Workbooks(a).Sheets(1).Range("C51").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B18").Value = Workbooks(a).Sheets(1).Range("C52").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B19").Value = Workbooks(a).Sheets(1).Range("C53").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B20").Value = Workbooks(a).Sheets(1).Range("C54").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B21").Value = Workbooks(a).Sheets(1).Range("C55").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B22").Value = Workbooks(a).Sheets(1).Range("C56").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B23").Value = Workbooks(a).Sheets(1).Range("C57").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B24").Value = Workbooks(a).Sheets(1).Range("C58").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B25").Value = Workbooks(a).Sheets(1).Range("C59").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B26").Value = Workbooks(a).Sheets(1).Range("C60").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B27").Value = Workbooks(a).Sheets(1).Range("C61").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C13").Value = Workbooks(a).Sheets(1).Range("C62").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C14").Value = Workbooks(a).Sheets(1).Range("C63").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C15").Value = Workbooks(a).Sheets(1).Range("C64").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C16").Value = Workbooks(a).Sheets(1).Range("C65").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C17").Value = Workbooks(a).Sheets(1).Range("C66").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C18").Value = Workbooks(a).Sheets(1).Range("C67").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C19").Value = Workbooks(a).Sheets(1).Range("C68").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C20").Value = Workbooks(a).Sheets(1).Range("C69").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C21").Value = Workbooks(a).Sheets(1).Range("C70").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C22").Value = Workbooks(a).Sheets(1).Range("C71").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C23").Value = Workbooks(a).Sheets(1).Range("C72").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C24").Value = Workbooks(a).Sheets(1).Range("C73").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C25").Value = Workbooks(a).Sheets(1).Range("C74").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C26").Value = Workbooks(a).Sheets(1).Range("C75").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("C27").Value = Workbooks(a).Sheets(1).Range("C76").Value
Userform1.TextBox4.Value = Workbooks(a).Sheets(1).Range("C39").Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D6").Value = Userform1.TextBox24.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H6").Value = Userform1.TextBox25.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D7").Value = Userform1.TextBox26.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("I7").Value = Userform1.ComboBox3.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E8").Value = Userform1.TextBox28.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("J8").Value = Userform1.TextBox29.Value
Workbooks(a).Close
Workbooks("Module de conversion TEST.xls").Activate
Userform1.MultiPage1.page1.Visible = False
Userform1.MultiPage1.page2.Visible = False
Userform1.MultiPage1.page3.Visible = False
Userform1.MultiPage1.page4.Visible = True
Userform1.MultiPage1.Value = 3
Userform1.Show
Else
Dim Classeur As Workbook
For Each Classeur In Workbooks
If Classeur.Name <> ThisWorkbook.Name Then
Classeur.Close
End If
Next Classeur
End If
Case vbCancel
Userform1.MultiPage1.Value = 0
Userform1.OptionButton1.Value = False
Userform1.OptionButton13.Value = False
Userform1.OptionButton3.Value = False
Userform1.OptionButton14.Value = False
Userform1.Show
End Select
End Sub
Et pour finir la macro pour terminer l'opération se lance avec le CommandButton4
Private Sub CommandButton4_Click()
Userform1.Hide
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H9").Value = Userform1.TextBox3.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D9").Value = Userform1.TextBox4.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("F9").Value = Userform1.TextBox5.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H10").Value = Userform1.TextBox6.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E10").Value = Userform1.TextBox7.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D6").Value = Userform1.TextBox24.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H6").Value = Userform1.TextBox25.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D7").Value = Userform1.TextBox26.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("I7").Value = Userform1.ComboBox3.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E8").Value = Userform1.TextBox28.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("J8").Value = Userform1.TextBox29.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("G7").Value = Userform1.TextBox30.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("B75").Value = "Commentaires : " & Userform1.TextBox31.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("E3").Value = Userform1.TextBox26.Value & Userform1.TextBox32.Value
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("O56").Value = Userform1.TextBox33.Value
If OptionButton6.Value = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("M7").Value = 0
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton1.Value = True
tolérance = "Tolérance Bilatérale "
cote = "Inf : " & TextBox3 + vbCr + "Nominal : " & TextBox4 + vbCr + "Sup : " & TextBox5
Else
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton1.Value = False
End If
If OptionButton7.Value = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("M7").Value = 1
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton2.Value = True
tolérance = "Tolérance Unilatérale Supérieure "
cote = "Sup : " & TextBox5
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D9").Value = ""
Else
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton2.Value = False
End If
If OptionButton8.Value = True Then
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("M7").Value = 2
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton3.Value = True
tolérance = "Tolérance Unilatérale Inférieure "
cote = "Inf : " & TextBox3
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("D9").Value = ""
Else
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").OptionButton3.Value = False
End If
'If TextBox25.Value = "" Then
'MsgBox "Veuillez remplir la case Désignation Caractéristique.", vbExclamation, "Erreur"
'Userform1.Show
'Else
If OptionButton9 = True Then
TextBox7 = ""
tol = ""
val = "Cmk de " & TextBox6
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K40").Value = "Cmk"
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K39").Value = "Cm"
capa = "Cmk "
ElseIf OptionButton10 = True Then
TextBox6 = ""
tol = ""
val = "Cpk de " & TextBox7
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K40").Value = "Cpk"
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Range("K39").Value = "Cp"
capa = "Cpk "
ElseIf capa = "" Then
MsgBox "Type de capabilité non sélectionné." + vbCr + "" + vbCr + "Cliquez sur Annuler lors du prochain message.", vbExclamation, "ATTENTION"
End If
If tolérance = "" Then
MsgBox "Type de tolérance non sélectionné." + vbCr + "" + vbCr + "Cliquez sur Annuler lors du prochain message.", vbExclamation, "ATTENTION"
End If
Select Case MsgBox("Votre choix est :" + vbCr + " " + vbCr + tolérance & "avec : " + vbCr + cote + vbCr + "" + vbCr + "Désignation produit : " & TextBox25 + vbCr + "Désignation pièce : " & TextBox26 + vbCr + tol + vbCr + val + vbCr + " " + vbCr + "Ces informations sont-elles correctes ?", vbOKCancel + vbQuestion, "RECAPITULATIF") 'new
Case vbOK 'new
Workbooks("Q-R-055-FR-1 - Capability Study - Normal Law.xls").Sheets("Capa échantillon").Activate
Dim x As Variant, r As Long, c As Long
Application.ScreenUpdating = False
x = Range("z37:aa37").Value
For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2)
x(r, c) = Replace(x(r, c), ".", ",")
Next c: Next r
Range("z37:aa37").Value = x
z = Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").Sheets("capa échantillon").Range("H6").Value & "- " & val & ".xls"
fichier = b & "\" & z
Workbooks("Q-R-053-FR-1 - Capability Study - Normal Law.xls").SaveAs fichier
MsgBox "Le fichier est maintenant enregistré et vous pouvez effectuer des modifications si nécessaire." + vbCr + "" + vbCr + "N'oubliez pas d'Enregistrer/Fermer le fichier après modification.", vbExclamation, "Nobel Automotive"
Workbooks(z).Activate
'End If
Case vbCancel 'new
Userform1.Show 'new
End Select 'new
End Sub
4 juin 2013 à 08:51