Posez votre question Signaler

Boucle onglets

GTA54 553Messages postés jeudi 23 février 2012Date d'inscription 12 mai 2015 Dernière intervention - Dernière réponse le 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-
Lire la suite 
Réponse
+0
moins plus
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 553Messages postés jeudi 23 février 2012Date d'inscription 12 mai 2015 Dernière intervention - 3 juin 2013 à 15:36
Merci.

Mais je la place où ta macro ?
Répondre
GTA54 553Messages postés jeudi 23 février 2012Date d'inscription 12 mai 2015 Dernière intervention - 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
Répondre
GTA54 553Messages postés jeudi 23 février 2012Date d'inscription 12 mai 2015 Dernière intervention - 4 juin 2013 à 08:51
UP
Répondre
Ajouter un commentaire
Ce document intitulé «  Boucle onglets  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes.

Le fait d'être membre vous permet d'avoir des options supplémentaires.