Hi,
I hope you can help me spot the mistake in my code. I've been searching for a couple of days now but no luck.
Background: In my project, I have two categories of variables, "decision factors" and "decision criterion". Each factor can have criteria. For this reason, I saved them in a two-dimensional array decisionFactors(factor, criterion) in which (factor,0) represents the original factor name. Some factors are given at the start, then the user can add more factors or criteria. Additionally, there's an integer array NumberOfCriteria(factor) that obviously counts the number of criteria that are assigned to each factor. This all works perfectly fine. The problem comes with editing them. For this, I created the following user form:
temp.png
in which the content of the lower ComboBox depends on the selected factor in the upper one. On edit, the user can then enter a new name for the selected factor/criterion which then overwrites the value in the decisionFactors array. I checked this step with a MsgBox and the respective element does have the new value. The ComboBoxes, however, do not adopt the new value although I wrote a refresh routine. There seems to be a mistake within this routine but I cannot find it.
The code is as follows:
In the user form posted above (extract):
Private Sub EditButton_Click()
[...]
If criterionChange = True Then
ActiveSheet.Cells(ContentInRow(decisionFactor(ComboBox1.ListIndex, ComboBox2.ListIndex + 1)), 2) = newName
decisionFactor(ComboBox1.ListIndex, ComboBox2.ListIndex) = newName
MsgBox decisionFactor(ComboBox1.ListIndex, ComboBox2.ListIndex)
Unload Me
ElseIf factorChange = True Then
ActiveSheet.Cells(ContentInRow(decisionFactor(ComboBox1.ListIndex, 0)), 2) = newName
decisionFactor(ComboBox1.ListIndex, 0) = newName
MsgBox decisionFactor(ComboBox1.ListIndex, 0)
RefreshCombobox ComboBox1, True
Unload Me
End If
End Sub
Private Sub ComboBox1_Change()
Label2.Enabled = True
ComboBox2.Enabled = True
RefreshCombobox ComboBox2, False, ComboBox1.ListIndex
End Sub
In the user form in which the new name can be entered:
Private Sub ConfirmButton_Click()
If OptionButton1 = True Then
If AlreadyExists(TextBox1.text, True) = False Then
newName = TextBox1.text
Unload Me
ElseIf AlreadyExists(TextBox1.text, False, Me.Tag) = False Then
newName = TextBox1.text
Unload Me
Else
MsgBox "This criterion or factor already exists."
End If
Else
MsgBox "Please select one of the options."
End If
End Sub
Refresh routine:
Public Sub RefreshCombobox(combobox As Object, IsFactor As Boolean, Optional factor As Integer)
Dim i As Integer
If IsFactor = True Then
For i = LBound(decisionFactor, 1) To UBound(decisionFactor, 1)
If (decisionFactor(i, 0)) = "" Then
Exit For
End If
Next i
numberOfFactors = i - 1
For i = 0 To numberOfFactors
combobox.AddItem (decisionFactor(i, 0))
Next i
Else
For i = 1 To NumberOfCriteria(factor)
combobox.AddItem (decisionFactor(factor, i))
Next i
End If
End Sub
PS: I'm well aware that my coding is probably far from elegant but the goal for now is simply to get it running. 
I'd be very happy about any help, cheers!
Bookmarks