Private Sub OK_REGULATOR_DATA_Click()
Dim i As long, derlig As Long, k as integer
derlig = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To 9
If Me.Controls("TextBox" & i).Text = "" Then
messsage i
Exit Sub
End If
Next
For i = 2 To derlig
toto = Array(TextBox1, TextBox2, TextBox3, TextBox4, ComboBox9, TextBox8, TextBox5, ComboBox6, ComboBox7, ComboBox8)
For k = 1 To 10
Worksheets("Feuil2").Cells(i, k).Value = toto(k).Value
Next
Worksheets("Feuil2").Cells(i, 11).FormulaR1C1 = DateValue(Date)
Unload Me
Next
End Sub
Private Sub messsage(i As Integer)
MsgBox "veuillez saisir une valeur dans la textbox Textbox" & i
End Sub
Private Sub OK_REGULATOR_DATA_Click() Dim nbErr As Integer nbErr = 0 ' Si aucune condition remplie => 10 erreurs.. ' Pas d'écriture.. Dim NbErrMax As Integer NbErrMax = 10 'prochaine ligne vide i = Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row + 1 If REGULATOR_DATA.TextBox1.Value = "" Then 'Customer Name Message nbErr = nbErr + 1 Else If REGULATOR_DATA.TextBox2.Value = "" Then 'Project Name Message nbErr = nbErr + 1 Else If REGULATOR_DATA.TextBox3.Value = "" Then 'P/N Project Message nbErr = nbErr + 1 Else If REGULATOR_DATA.TextBox4.Value = "" Then 'S/N Project Message nbErr = nbErr + 1 Else If REGULATOR_DATA.ComboBox9.Value = "" Then 'Product Name Message nbErr = nbErr + 1 Else If REGULATOR_DATA.TextBox8.Value = "" Then 'P/N Product Message nbErr = nbErr + 1 Else If REGULATOR_DATA.TextBox5.Value = "" Then 'S/N Product Message nbErr = nbErr + 1 Else If REGULATOR_DATA.ComboBox6.Value = "" Then 'Bestcom Version Message nbErr = nbErr + 1 Else If REGULATOR_DATA.ComboBox7.Value = "" Then 'Firmware Version Message nbErr = nbErr + 1 Else If REGULATOR_DATA.ComboBox8.Value = "" Then 'Performed By Message nbErr = nbErr + 1 Else End If End If End If End If End If End If End If End If End If End If If nbErr < 10 Then Feuil2.Cells(i, 1).Value = REGULATOR_DATA.TextBox1.Value 'Customer Name Feuil2.Cells(i, 2).Value = REGULATOR_DATA.TextBox2.Value 'Project Name Feuil2.Cells(i, 3).Value = REGULATOR_DATA.TextBox3.Value 'P/N Project Feuil2.Cells(i, 4).Value = REGULATOR_DATA.TextBox4.Value 'S/N Project Feuil2.Cells(i, 5).Value = REGULATOR_DATA.ComboBox9.Value 'Product Name Feuil2.Cells(i, 6).Value = REGULATOR_DATA.TextBox8.Value 'P/N Product Feuil2.Cells(i, 7).Value = REGULATOR_DATA.TextBox5.Value 'S/N Product Feuil2.Cells(i, 8).Value = REGULATOR_DATA.ComboBox6.Value 'Bestcom Version Feuil2.Cells(i, 9).Value = REGULATOR_DATA.ComboBox7.Value 'Firmware Version Feuil2.Cells(i, 10).Value = REGULATOR_DATA.ComboBox8.Value 'Performed By Feuil2.Cells(i, 11).FormulaR1C1 = DateValue(Date) 'Date End If Unload Me End Sub
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questiontoto = Array(TextBox1, TextBox2, TextBox3, TextBox4, ComboBox9, TextBox8, TextBox5, ComboBox6, ComboBox7, ComboBox8)par :
toto = Array("", TextBox1, TextBox2, TextBox3, TextBox4, ComboBox9, TextBox8, TextBox5, ComboBox6, ComboBox7, ComboBox8)--
Worksheets("Feuil2").Cells(i, k+1).Value = toto(k).Valueet même si seules 10 opérations seront faites, je les évite (suis très avare).
comment faire pour qu'un message apparaît lorsque toutes les conditions ne sont remplie et bloquer la fonction d'écriture de la ligne?
la ligne ne doit pas être écrite.Donc la réponse d'Ucfoutu doit totalement répondre à ton besoin.
Private Sub OK_REGULATOR_DATA_Click()
Dim i As Long, derlig As Long, k As Integer
derlig = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To 9
If Me.Controls("TextBox" & i).Text = "" Then
messsage i
Exit Sub
End If
Next
For i = 2 To derlig
Worksheets("Feuil2").Cells(i, k + 1).Value = toto(k).Value
For k = 1 To 10
Worksheets("Feuil2").Cells(i, k).Value = toto(k).Value
Next
Worksheets("Feuil2").Cells(i, 11).FormulaR1C1 = DateValue(Date)
Unload Me
Next
End Sub
Private Sub messsage(i)
MsgBox "veuillez saisir une valeur dans la textbox Textbox" & i
End Sub
Dim i As Integer, derlig As Long, k As Integer2) c'est bien :
Private Sub messsage(i As Integer)3) où est passée ma ligne disant :
toto = Array(TextBox1, TextBox2, TextBox3, TextBox4, ComboBox9, TextBox8, TextBox5, ComboBox6, ComboBox7, ComboBox8)??
Private Sub OK_REGULATOR_DATA_Click()
'i est la variante qui corespond à la ligne, les numéros de colonnes sont fixes
i = 2
'ecrit les données dans la prochaine ligne vide
While Feuil2.Cells(i, 1).Value <> ""
i = i + 1
Wend
If REGULATOR_DATA.TextBox1.Value = "" Then 'Customer Name
Feuil2.Cells(i, 1).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
If REGULATOR_DATA.TextBox2.Value = "" Then 'Project Name
Feuil2.Cells(i, 2).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
If REGULATOR_DATA.TextBox3.Value = "" Then 'P/N Project
Feuil2.Cells(i, 3).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
If REGULATOR_DATA.TextBox4.Value = "" Then 'S/N Project
Feuil2.Cells(i, 4).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
If REGULATOR_DATA.ComboBox9.Value = "" Then 'Product Name
Feuil2.Cells(i, 5).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
If REGULATOR_DATA.TextBox8.Value = "" Then 'P/N Product
Feuil2.Cells(i, 6).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
If REGULATOR_DATA.TextBox5.Value = "" Then 'S/N Product
Feuil2.Cells(i, 7).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
If REGULATOR_DATA.ComboBox6.Value = "" Then 'Bestcom Version
Feuil2.Cells(i, 8).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
If REGULATOR_DATA.ComboBox7.Value = "" Then 'Firmware Version
Feuil2.Cells(i, 9).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
If REGULATOR_DATA.ComboBox8.Value = "" Then 'Performed By
Feuil2.Cells(i, 10).Value = ""
Feuil2.Cells(i, 11).Value = ""
message
Else
ActiveSheet.Unprotect
Feuil2.Cells(i, 1).Value = REGULATOR_DATA.TextBox1.Value 'Customer Name
Feuil2.Cells(i, 2).Value = REGULATOR_DATA.TextBox2.Value 'Project Name
Feuil2.Cells(i, 3).Value = REGULATOR_DATA.TextBox3.Value 'P/N Project
Feuil2.Cells(i, 4).Value = REGULATOR_DATA.TextBox4.Value 'S/N Project
Feuil2.Cells(i, 5).Value = REGULATOR_DATA.ComboBox9.Value 'Product Name
Feuil2.Cells(i, 6).Value = REGULATOR_DATA.TextBox8.Value 'P/N Product
Feuil2.Cells(i, 7).Value = REGULATOR_DATA.TextBox5.Value 'S/N Product
Feuil2.Cells(i, 8).Value = REGULATOR_DATA.ComboBox6.Value 'Bestcom Version
Feuil2.Cells(i, 9).Value = REGULATOR_DATA.ComboBox7.Value 'Firmware Version
Feuil2.Cells(i, 10).Value = REGULATOR_DATA.ComboBox8.Value 'Performed By
Feuil2.Cells(i, 11).FormulaR1C1 = DateValue(Date) 'Date
Unload Me
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
17 déc. 2013 à 10:29
Je pense que K doit commencer à 0 par à 1