Boucle onglets

GTA54 Messages postés 499 Statut Membre -  
GTA54 Messages postés 499 Statut Membre -
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

  1.
    Heikro Messages postés 43 Statut Membre 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.
    0
    1. GTA54 Messages postés 499 Statut Membre 30
       
      Merci.

      Mais je la place où ta macro ?
      0
    2. GTA54 Messages postés 499 Statut Membre 30
       
      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
    3. GTA54 Messages postés 499 Statut Membre 30
       
      UP
      0

Discussions similaires

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

16 réponses
Mon ordinateur s'allume et s'eteint en boucle
mhmd_elmrni -
BiGaMeRz10 -

10 réponses
Mon PC s'allume puis s'éteint tout seul. Que faire ?
La gouille -
Kcb -

28 réponses
Comment supprimer les onglets?
VictimeDeLinformatique -
VictimeDeLinformatique -

2 réponses
Ma Livebox redémarre en boucle !
Utilisateur anonyme -
Utilisateur anonyme -

5 réponses