Re : msgbox dansun userform
bonjour Fred0o
merci pour ta réponse mais j'ai essayé et cela ne fonctionne pas, je pense que j'ai oublié quelque chose mais quoi ? là je sèche ...
tu trouveras ci dessous le code complet de l'userform où j'ai intégré la sub en question :
Private Sub ComboBox1_Change()
Sheets("Fiche descriptive").Range("c31") = ComboBox1
End Sub
Private Sub ComboBox2_Change()
Sheets("Fiche descriptive").Range("c32") = ComboBox2
End Sub
Private Sub ComboBox3_Change()
Sheets("Fiche descriptive").Range("C33") = ComboBox3
End Sub
Private Sub ComboBox4_Change()
Sheets("Fiche descriptive").Range("c34") = ComboBox4
End Sub
Private Sub ComboBox5_Change()
Sheets("Fiche descriptive").Range("c35") = ComboBox5
End Sub
Private Sub ComboBox6_Change()
Sheets("Fiche descriptive").Range("c36") = ComboBox6
End Sub
Private Sub ComboBox7_Change()
Sheets("Fiche descriptive").Range("c37") = ComboBox7
End Sub
Private Sub ComboBox8_Change()
Sheets("Fiche descriptive").Range("C38") = ComboBox8
End Sub
Private Sub ComboBox9_Change()
Sheets("Fiche descriptive").Range("C39") = ComboBox9
End Sub
Private Sub ComboBox10_Change()
Sheets("Fiche descriptive").Range("C40") = ComboBox10
End Sub
'' Pas utilisé
'Private Sub ComboBox12_Change()
'Sheets("Fiche descriptive").Range("c24") = ComboBox12
'End Sub
Private Sub TextBox5_AfterUpdate()
' Vérifier la valeur après la saisie, au passage a un autre contrôle
If Me.TextBox5.Value = 2.27 Then
' Si la valeur correspond à une valeur donnée, poser la question
If MsgBox("Etes-vous sûr !?", vbQuestion + vbYesNo, "ATTENTION ...") = vbNo Then
' Si l'utilisateur répond non, on efface le contenu
Me.TextBox5.Value = ""
End If
End If
End Sub
Private Sub CommandButton1_Click()
Dim I As Integer, K As Integer, Col As Integer
' Utilité ?
With Sheets("Fiche descriptive")
For I = 0 To 9
Me.Controls("TextBox" & 7 + (I * 8)) = .Range("H" & 31 + I)
Me.Controls("TextBox" & 8 + (I * 8)) = .Range("I" & 31 + I)
Next I
Me.TextBox81 = .Range("H41")
Me.TextBox82 = .Range("I41")
End With
For I = 0 To 9
For K = 1 To 6
With Me.Controls("TextBox" & (I * 8) + K)
If .Value <> "" Then
Col = K
If K > 2 Then Col = K + 1
Sheets("Fiche descriptive").Cells(31 + I, Col) = Val(Replace(.Value, ",", "."))
End If
End With
Next K
Next I
Me.Hide
UserForm4.Show 0
End Sub
Private Sub CommandButton2_Click()
' Précédent
Me.Hide
UserForm2.Show 0
End Sub
Private Sub UserForm_Activate()
Call determine(2)
With Me
.StartUpPosition = 3
.Width = Application.Width
.Height = Application.Height
.Left = 0
.Top = 0
End With
End Sub
Private Sub UserForm_Resize()
Dim I As Integer
Dim Ctrl As Control
On Error Resume Next
For Each Ctrl In Controls
I = I + 1
Ctrl.Width = Me.Width / (Largeur_Usf / l(I))
Ctrl.Height = Me.Height / (Hauteur_Usf / h(I))
Ctrl.Left = Me.Width / (Largeur_Usf / LeftBouton(I))
Ctrl.Top = Me.Height / (Hauteur_Usf / TopBouton(I))
Ctrl.FontSize = ((Me.Height + Me.Width) / 8) / (FontBouton * 2)
Next Ctrl
End Sub
Private Sub UserForm_Initialize()
Dim Nb As Integer
Dim I As Integer, K As Integer
For I = 0 To 9
For K = 2 To 6
Me.Controls("TextBox" & (I * 8) + K).Tag = I
ReDim Preserve TBox(Nb)
Set TBox(Nb).Boite = Me.Controls("TextBox" & (I * 8) + K)
Nb = Nb + 1
Next K
Me.Controls("TextBox" & (I * 8) + 7).Locked = True ' On bloque les totaux tonnage par ligne
Me.Controls("TextBox" & (I * 8) + 8).Locked = True ' On bloque les totaux cubage par ligne
Next I
Me.TextBox81.Locked = True ' On bloque le total général tonnage
Me.TextBox82.Locked = True ' On bloque le total général cubage
End Sub
et j'ai aussi un module de classe, voici le code :
Option Explicit
Public WithEvents Boite As MSForms.TextBox
Private Sub Boite_Change()
Dim I As Integer
Dim Num As Integer
Dim Total As Double
Num = 7 + (Val(Boite.Tag) * 8)
'Num = Val(Mid(Boite.Name, 8))
With UserForm3
' Debug.Print Num; " = "; Num - 5; " * "; Num - 4
' Debug.Print Num + 1; " = "; Num - 5; " * "; Num - 3; " * "; Num - 2; " * "; Num - 1
Total = Val(Replace(.Controls("TextBox" & Num - 5), ",", ".")) * Val(Replace(.Controls("TextBox" & Num - 4), ",", "."))
If Total <> 0 Then
.Controls("TextBox" & Num) = Format(Total, "0.00")
.Controls("TextBox" & Num).BackColor = &H80000005
Else
.Controls("TextBox" & Num) = ""
.Controls("TextBox" & Num).BackColor = &HFF&
End If
Total = Val(Replace(.Controls("TextBox" & Num - 5), ",", ".")) * Val(Replace(.Controls("TextBox" & Num - 3), ",", ".")) * _
Val(Replace(.Controls("TextBox" & Num - 2), ",", ".")) * Val(Replace(.Controls("TextBox" & Num - 1), ",", "."))
If Total <> 0 Then
.Controls("TextBox" & Num + 1) = Format(Total, "0.000")
.Controls("TextBox" & Num + 1).BackColor = &H80000005
Else
.Controls("TextBox" & Num + 1) = ""
.Controls("TextBox" & Num + 1).BackColor = &HFF&
End If
Total = 0
For I = 7 To 79 Step 8
Total = Total + Val(Replace(.Controls("TextBox" & I), ",", "."))
Next I
If Total <> 0 Then
.TextBox81 = Format(Total, "0.000")
.TextBox81.BackColor = &H80000005
Else
.TextBox81 = ""
.TextBox81.BackColor = &HFF&
End If
Total = 0
For I = 8 To 80 Step 8
Total = Total + Val(Replace(.Controls("TextBox" & I), ",", "."))
Next I
If Total <> 0 Then
.TextBox82 = Format(Total, "0.000")
.TextBox82.BackColor = &H80000005
Else
.TextBox82 = ""
.TextBox82.BackColor = &HFF&
End If
' .Controls("TextBox" & Num) = Val(Replace(.Controls("TextBox" & Num - 5), ",", ".")) * Val(Replace(.Controls("TextBox" & Num - 4), ",", "."))
' .Controls("TextBox" & Num + 1) = Val(Replace(.Controls("TextBox" & Num - 5), ",", ".")) * Val(Replace(.Controls("TextBox" & Num - 3), ",", ".")) * _
' Val(Replace(.Controls("TextBox" & Num - 2), ",", ".")) * Val(Replace(.Controls("TextBox" & Num - 1), ",", "."))
End With
' Select Case (Val(Mid(Boite.Name, 8)) - 2) Mod 8
' Case 0, 1
' Dest = 1
' End Select
End Sub
merci de vos lumières
chb44
