B
BJ
Hello
I've got two combo boxes that work with each other. The first allows the
user to select a pricing matrix group (there are 5 total) and the second
allows the user to select a contract term (each group has unique terms) which
selects the appropriate pricing matrix. The problem I am encountering is
that when I've made changes to the workbook (e.g., adding a new worksheet or
when the autorecover function runs) the second combo box reverts to a blank
and the pricing is lost. This requires the user to reenter the contract term
to get the pricing back. I can't figure out why its happening.
Here is my code (abbreviated ...) :
Private Sub ComboBox1_Change()
If ComboBox1.Value = "GroupA" Then
ComboBox2.Value = ""
ComboBox2.ListFillRange = "GroupA_Terms"
End If
If ComboBox1.Value = "GroupB" Then
ComboBox2.ListFillRange = "GroupB_Terms"
End If
....
End Sub
Private Sub ComboBox2_Change()
If ComboBox1.Value = "GroupA" Then
Sheets("Rates").Range("GroupA_Matrix").Copy
Sheets("Assumptions").Range("A29").PasteSpecial Paste:=xlPasteValues,
Transpose:=False
Application.CutCopyMode = False
....
End Sub
If I remove the ... ComboBox2.Value = "" ... line I don't have the problem.
However, if the user switches the ComboBox1 value, the term doesn't change
until they select a new one. This results in error messages as the formulas
can't find the term they have selected.
Any thoughts?
I've got two combo boxes that work with each other. The first allows the
user to select a pricing matrix group (there are 5 total) and the second
allows the user to select a contract term (each group has unique terms) which
selects the appropriate pricing matrix. The problem I am encountering is
that when I've made changes to the workbook (e.g., adding a new worksheet or
when the autorecover function runs) the second combo box reverts to a blank
and the pricing is lost. This requires the user to reenter the contract term
to get the pricing back. I can't figure out why its happening.
Here is my code (abbreviated ...) :
Private Sub ComboBox1_Change()
If ComboBox1.Value = "GroupA" Then
ComboBox2.Value = ""
ComboBox2.ListFillRange = "GroupA_Terms"
End If
If ComboBox1.Value = "GroupB" Then
ComboBox2.ListFillRange = "GroupB_Terms"
End If
....
End Sub
Private Sub ComboBox2_Change()
If ComboBox1.Value = "GroupA" Then
Sheets("Rates").Range("GroupA_Matrix").Copy
Sheets("Assumptions").Range("A29").PasteSpecial Paste:=xlPasteValues,
Transpose:=False
Application.CutCopyMode = False
....
End Sub
If I remove the ... ComboBox2.Value = "" ... line I don't have the problem.
However, if the user switches the ComboBox1 value, the term doesn't change
until they select a new one. This results in error messages as the formulas
can't find the term they have selected.
Any thoughts?