Option Explicit
Public flag As Boolean
Public flag2 As Boolean
Public dn As Variant
Public class As Variant
Public rf As Variant
Public bd As Variant
Public thk As Variant
Private Sub UserForm_Initialize()
dn = ComboBox1.Value
class = ComboBox2.Value
rf = ComboBox3.Value
bd = ComboBox4.Value
thk = ComboBox5.Value
End Sub
Private Sub ComboBox1_traitement() 'Diametre nominal
If ComboBox1.Value = dn Then
Exit Sub
Else
If Not (ComboBox1.Value = 0.5 Or ComboBox1.Value = 0.75 Or ComboBox1.Value = 1 Or ComboBox1.Value = 1.5 Or ComboBox1.Value = 1.75 Or ComboBox1.Value = 2 Or ComboBox1.Value = 2.5 Or ComboBox1.Value = 3 Or ComboBox1.Value = 4 Or ComboBox1.Value = 5 Or ComboBox1.Value = 6 Or ComboBox1.Value = 8 Or ComboBox1.Value = 10 Or ComboBox1.Value = 12 Or ComboBox1.Value = 14 Or ComboBox1.Value = 16 Or ComboBox1.Value = 18 Or ComboBox1.Value = 20 Or ComboBox1.Value = 24) Then
MsgBox ("Diamètre nominal non normé!")
surbrillance (1)
flag2 = True
Exit Sub
Else
Me.Range("H5") = Replace(ComboBox1.Value, ".", ",")
dn = ComboBox1.Value
flag2 = False
End If
End If
End Sub
Private Sub ComboBox2_traitement() 'class de pression
If ComboBox2.Value = class Then
Exit Sub
Else
If ComboBox2.Value = 150 Or ComboBox2.Value = 300 Or ComboBox2.Value = 400 Or ComboBox2.Value = 600 Or ComboBox2.Value = 900 Or ComboBox2.Value = 1500 Or ComboBox2.Value = 2500 Then
Me.Range("H6") = Replace(ComboBox2.Value, ".", ",")
Else
MsgBox ("Donner une valeur de class de pression égal à 150, 300, 400, 600, 900, 1500 ou 2500!")
surbrillance (2)
End If
End If
End Sub
Private Sub combobox3_traitement() 'talon
If Not (ComboBox3.Value = "RTJ" Or ComboBox3.Value = "RF") Then
MsgBox ("Choisir le type de bride: RF ou RTJ")
surbrillance (3)
flag = True
Else
Me.Range("H7") = ComboBox3.Value
flag = False
End If
End Sub
Sub ComboBox4_traitement() 'Bore diametre
If bd = ComboBox4.Value Then
Exit Sub
Else
If IsNumeric(ComboBox4.Value) Then
If ToggleButton1.Value = True Then
Me.Range("H8") = (Me.Range("C18") - Replace(ComboBox4.Value, ".", ",")) / 2
ComboBox5.Value = Me.Range("H8")
bd = ComboBox4.Value
End If
Else
ComboBox4.Value = ""
MsgBox ("Entrer une valeur numérique pour le diamètre de passage!")
surbrillance (4)
End If
test_couleur
End If
End Sub
Sub Combobox5_traitement() 'thikness
If thk = ComboBox5.Value Then
Exit Sub
Else
If IsNumeric(ComboBox5.Value) And ToggleButton2.Value = True Then
Me.Range("H8") = CDbl(Replace(ComboBox5.Value, ".", ","))
ComboBox4.Value = Range("C18") - 2 * Me.Range("H8")
thk = ComboBox5.Value
Else
MsgBox ("Entrer une valeur numérique pour l'épaisseur de la bride")
surbrillance (5)
End If
test_couleur
End If
End Sub
[COLOR="Blue"]Private Sub ToggleButton1_Click() 'bouton diametre de passage
ComboBox4.Enabled = ToggleButton1
ToggleButton2 = Not ToggleButton1
End Sub
Private Sub ToggleButton2_Click() 'bouton tickness
ComboBox5.Enabled = ToggleButton2
ToggleButton1 = Not ToggleButton2
End Sub[/COLOR]
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'Diametre nominal
If KeyCode & Shift = "90" Or KeyCode & Shift = "91" Or KeyCode & Shift = "130" Or KeyCode & Shift = "131" Then
ComboBox1_traitement
End If
If ToggleButton1 = True And flag2 = False Then
MaCombo 2, 4, KeyCode & Shift
ElseIf ToggleButton1 = False And flag2 = False Then
MaCombo 2, 5, KeyCode & Shift
End If
End Sub
Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'Class de pression
MaCombo 3, 1, KeyCode & Shift
If KeyCode & Shift = "90" Or KeyCode & Shift = "91" Or KeyCode & Shift = "130" Or KeyCode & Shift = "131" Then
ComboBox2_traitement
End If
End Sub
Private Sub ComboBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'Talon
If rf = ComboBox3.Value Then
Else
If KeyCode & Shift = "90" Or KeyCode & Shift = "91" Or KeyCode & Shift = "130" Or KeyCode & Shift = "131" Then
combobox3_traitement
rf = ComboBox3.Value
End If
End If
If ToggleButton1 = True And flag = False Then
MaCombo 4, 2, KeyCode & Shift
ElseIf ToggleButton1 = False And flag = False Then
MaCombo 5, 2, KeyCode & Shift
End If
End Sub
Private Sub ComboBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'Bore diametre
MaCombo 1, 3, KeyCode & Shift
If KeyCode & Shift = "90" Or KeyCode & Shift = "130" Then
ComboBox4_traitement
End If
End Sub
Private Sub ComboBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'Thikness
MaCombo 1, 3, KeyCode & Shift
If KeyCode & Shift = "90" Or KeyCode & Shift = "130" Then
Combobox5_traitement
End If
End Sub
Sub MaCombo(Arg1, Arg2, Arg3)
Dim moncode As Variant
Select Case Arg3
Case "90", "130": moncode = Arg1
Case "91", "131": moncode = Arg2
Case Else: Exit Sub
End Select
ActiveSheet.OLEObjects("ComboBox" & moncode).Activate
ActiveSheet.OLEObjects("ComboBox" & moncode).Object.SelStart = 0
ActiveSheet.OLEObjects("ComboBox" & moncode).Object.SelLength = Len(ActiveSheet.OLEObjects("ComboBox" & moncode).Object.Value)
End Sub
Sub test_couleur()
If CInt(ComboBox4.Value) >= CInt(Me.Range("C18")) Or CInt(ComboBox4.Value) < 0 Then
ComboBox4.BackColor = RGB(204, 0, 0) ' rouge
ComboBox4.ForeColor = RGB(255, 255, 255) ' blanc
ComboBox5.BackColor = RGB(204, 0, 0) ' rouge
ComboBox5.ForeColor = RGB(255, 255, 255) ' blanc
Else
ComboBox4.BackColor = &HC0FFFF ' jaune
ComboBox4.ForeColor = RGB(0, 0, 0) ' noire
ComboBox5.BackColor = &HC0FFFF ' jaune
ComboBox5.ForeColor = RGB(0, 0, 0) ' noire
End If
End Sub
Sub surbrillance(arg)
ActiveSheet.OLEObjects("ComboBox" & arg).Activate
ActiveSheet.OLEObjects("ComboBox" & arg).Object.SelStart = 0
ActiveSheet.OLEObjects("ComboBox" & arg).Object.SelLength = Len(ActiveSheet.OLEObjects("ComboBox" & arg).Object.Value)
End Sub