Sujet Précédent Sujet Suivant

Boucle onglets

Fermé
GTA54 Messages postés 414 Date d'inscription jeudi 23 février 2012 Statut Membre Dernière intervention 7 août 2019 - Modifié par GTA54 le 3/06/2013 à 14:51
GTA54 Messages postés 414 Date d'inscription jeudi 23 février 2012 Statut Membre Derniè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

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


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

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


N'hésitez pas si vous avez la moindre question ! Merci.


"Mais comment faire,
Comment lui dire,
Comment lui faire voir,
Ma planète,
Artificielle..."

-M-

1 réponse

Réponse 1 / 1
Heikro Messages postés 40 Date d'inscription mardi 28 mai 2013 Statut Membre Dernière intervention 5 mars 2019 8
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.
0
GTA54 Messages postés 414 Date d'inscription jeudi 23 février 2012 Statut Membre Dernière intervention 7 août 2019 27
3 juin 2013 à 15:36
Merci.

Mais je la place où ta macro ?
0
GTA54 Messages postés 414 Date d'inscription jeudi 23 février 2012 Statut Membre Dernière intervention 7 août 2019 27
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

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
0
GTA54 Messages postés 414 Date d'inscription jeudi 23 février 2012 Statut Membre Dernière intervention 7 août 2019 27
4 juin 2013 à 08:51
UP
0

Discussions similaires

Mon pc s'allume et s'éteint en boucle
Jack -
Daniel -

16 réponses
Redémarrage en boucle Xiamo mi note 10
Microsam -
Noel -

2 réponses
Ma Freebox redémarre tout le temps
hagbard.c -
WissenAmek -

116 réponses
faire une boucle sans VBA
lucky-luccio -
lucky-luccio -

3 réponses
Boucle en Batch
Lord_Vking -
Foxyo -

22 réponses