Hi
I have the following code (its in word but figure its still vba) could this be written better, i expect so but my skills are limited. Any ideas?
I have the following code (its in word but figure its still vba) could this be written better, i expect so but my skills are limited. Any ideas?
Code:
Private WithEvents App As Word.Application
Public Sub LiklihoodCombos(pcb1 As ComboBox)
With pcb1
.Value = "Select."
If .ListIndex = -1 Then
.AddItem "Select."
End If
.Value = "Low"
If .ListIndex = -1 Then
.AddItem "Low"
End If
.Value = "Moderate"
If .ListIndex = -1 Then
.AddItem "Moderate"
End If
.Value = "High"
If .ListIndex = -1 Then
.AddItem "High"
End If
.Value = "Very High"
If .ListIndex = -1 Then
.AddItem "Very High"
End If
End With
End Sub
Public Sub ImpactCombos(pcb2 As ComboBox)
With pcb2
.Value = "Select."
If .ListIndex = -1 Then
.AddItem "Select."
End If
.Value = "Low"
If .ListIndex = -1 Then
.AddItem "Low"
End If
.Value = "Moderate"
If .ListIndex = -1 Then
.AddItem "Moderate"
End If
.Value = "High"
If .ListIndex = -1 Then
.AddItem "High"
End If
.Value = "Very High"
If .ListIndex = -1 Then
.AddItem "Very High"
End If
End With
End Sub
Private Sub Document_Open()
Set App = Word.Application
'First Row
LiklihoodCombos ComboBox1
ImpactCombos ComboBox2
ComboBox1.ListIndex = TextBox2.Value
ComboBox2.ListIndex = TextBox3.Value
'Second Row
LiklihoodCombos ComboBox3
ImpactCombos ComboBox4
ComboBox3.ListIndex = TextBox5.Value
ComboBox4.ListIndex = TextBox6.Value
'Third Row
LiklihoodCombos ComboBox5
ImpactCombos ComboBox6
ComboBox5.ListIndex = TextBox8.Value
ComboBox6.ListIndex = TextBox9.Value
'Fourth Row
LiklihoodCombos ComboBox7
ImpactCombos ComboBox8
ComboBox7.ListIndex = TextBox11.Value
ComboBox8.ListIndex = TextBox12.Value
'Fifth Row
LiklihoodCombos ComboBox9
ImpactCombos ComboBox10
ComboBox9.ListIndex = TextBox14.Value
ComboBox10.ListIndex = TextBox15.Value
End Sub
Private Sub ComboBox1_Change()
cmb1 = ComboBox1.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox2.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox1 = cmb1 * cmb2
End Sub
Private Sub ComboBox2_Change()
cmb1 = ComboBox1.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox2.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox1 = cmb1 * cmb2
End Sub
Private Sub ComboBox3_Change()
cmb1 = ComboBox3.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox4.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox4 = cmb1 * cmb2
End Sub
Private Sub ComboBox4_Change()
cmb1 = ComboBox3.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox4.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox4 = cmb1 * cmb2
End Sub
Private Sub ComboBox5_Change()
cmb1 = ComboBox5.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox6.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox7 = cmb1 * cmb2
End Sub
Private Sub ComboBox6_Change()
cmb1 = ComboBox5.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox6.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox7 = cmb1 * cmb2
End Sub
Private Sub ComboBox7_Change()
cmb1 = ComboBox7.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox8.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox10 = cmb1 * cmb2
End Sub
Private Sub ComboBox8_Change()
cmb1 = ComboBox7.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox8.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox10 = cmb1 * cmb2
End Sub
Private Sub ComboBox9_Change()
cmb1 = ComboBox9.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox10.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox13 = cmb1 * cmb2
End Sub
Private Sub ComboBox10_Change()
cmb1 = ComboBox9.Value
Select Case cmb1
Case Is = "Select."
cmb1 = 0
Case Is = "Low"
cmb1 = 0.3
Case Is = "Moderate"
cmb1 = 0.5
Case Is = "High"
cmb1 = 0.7
Case Is = "Very High"
cmb1 = 1
End Select
cmb2 = ComboBox10.Value
Select Case cmb2
Case Is = "Select."
cmb2 = 0
Case Is = "Low"
cmb2 = 4
Case Is = "Moderate"
cmb2 = 6
Case Is = "High"
cmb2 = 8
Case Is = "Very High"
cmb2 = 10
End Select
TextBox13 = cmb1 * cmb2
End Sub
Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
MsgBox (ComboBox1.ListIndex)
TextBox2 = ComboBox1.ListIndex
TextBox3 = ComboBox2.ListIndex
TextBox5 = ComboBox3.ListIndex
TextBox6 = ComboBox4.ListIndex
TextBox8 = ComboBox5.ListIndex
TextBox9 = ComboBox6.ListIndex
TextBox11 = ComboBox7.ListIndex
TextBox12 = ComboBox8.ListIndex
TextBox14 = ComboBox9.ListIndex
TextBox15 = ComboBox10.ListIndex
End Sub