Boucle onglets

GTA54 Messages postés 414 Date d'inscription Statut Membre Dernière intervention -
GTA54 Messages postés 414 Date d'inscription Statut Membre Dernière intervention - 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-

Afficher la suite

A voir également:

Boucle onglets
Restaurer les onglets chrome - Guide
Boucle excel sans macro - Forum Excel
Imprimer tous les onglets pdf - Forum Bureautique
Mon pc s'allume et s'éteint en boucle ✓ - Forum Matériel & Système
Ouvrir plusieurs onglets en même temps - Guide

1 réponse

Réponse 1 / 1

Heikro Messages postés 40 Date d'inscription Statut Membre Dernière intervention 8

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és 414 Date d'inscription Statut Membre Dernière intervention 29

Merci.

Mais je la place où ta macro ?

GTA54 Messages postés 414 Date d'inscription Statut Membre Dernière intervention 29

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

GTA54 Messages postés 414 Date d'inscription Statut Membre Dernière intervention 29

Discussions similaires

Mon pc s'allume et s'éteint en boucle

Mon ordinateur s'allume et s'eteint en boucle

tele s'eteind et s'allume en boucle

problème tv décodeur orange

Redmi redémarre en boucle - Fastboost impossible

Votre réponse

Discussions similaires