Polulating Combobox's

[koenigma]

Hopefully someone can help,

I have a Usersform with 5 comboboxes, named as follows:

1. cmbMatSelect
2. cmbMatSpec1
3. cmbMatSpec2
4. cmbMatGrade1
5. cmbMatGrade1

based upon the value of the "cmbMatSelect" the Comboboxes cmbMatSpec1 &
cmbMatSpec2 are populated, with the Code shown below:

Private Sub cmbMaterialSelect_Change()
Dim wks As Worksheet
Dim mycell As Range

Select Case cmbMaterialSelect.Text
'****************************Case 1
Case "SA Material"
' If cmbMaterialSelect.Text = "SA Material" Then
Set wks = Worksheets("BaseMetals")
With wks
Set rng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
End With
For Each mycell In rng.Cells
If mycell.Value = mycell.Offset(-1, 0).Value Then
'do nothing
Else
Me.cmbMatSpec1.AddItem CStr(mycell.Value)
Me.cmbMatSpec1.ListIndex = 0
Me.cmbMatSpec2.AddItem CStr(mycell.Value)
Me.cmbMatSpec2.ListIndex = 0

End If
Next mycell
'***************************** Case 2
Case "SB Material"
' If cmbMaterialSelect.Text = "Material" Then
Set wks = Worksheets("BaseMetals")
With wks
Set rng = .Range("N2", .Cells(.Rows.Count, "N").End(xlUp))
End With
For Each mycell In rng.Cells
If mycell.Value = mycell.Offset(-1, 0).Value Then
'do nothing
Else
Me.cmbMatSpec1.AddItem CStr(mycell.Value)
Me.cmbMatSpec1.ListIndex = 0
Me.cmbMatSpec2.AddItem CStr(mycell.Value)
Me.cmbMatSpec2.ListIndex = 0
End If
Next mycell
'**************************** Case 3
Case "SA to an SB Material"
' If cmbMaterialSelect.Text = "SA to an SB Material" Then
Set wks = Worksheets("WeldingBaseMetals")
With wks
Set rng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
End With
For Each mycell In rng.Cells
If mycell.Value = mycell.Offset(-1, 0).Value Then
'do nothing
Else
Me.cmbMatSpec1.AddItem CStr(mycell.Value)
Me.cmbMatSpec1.ListIndex = 0
End If
Next mycell

Set wks = Worksheets("WeldingBaseMetals")
With wks
Set rng = .Range("N2", .Cells(.Rows.Count, "N").End(xlUp))
End With
For Each mycell In rng.Cells
If mycell.Value = mycell.Offset(-1, 0).Value Then
'do nothing
Else
Me.cmbMatSpec2.AddItem CStr(mycell.Value)
Me.cmbMatSpec2.ListIndex = 0
End If
Next mycell
End Select
End Sub

which in turn populates the Comboboxes cmbMatGrade1 & cmbMatGrade2.
that have the following code:

Private Sub cmbMatSpec1_Change()
Dim mycell As Range
Me.cmbMatGrade1.Clear
With Me.cmbMatSpec1
For Each mycell In rng.Cells
If mycell.Value = Me.cmbMatSpec1.Value Then
Me.cmbMatGrade1.AddItem CStr(mycell.Offset(0, 1).Value)
Me.cmbMatGrade1.ListIndex = 0
End If
Next mycell
End With
End sub

Private Sub cmbMatSpec2_Change()
Dim mycell As Range
Me.cmbMatGrade1.Clear
With Me.cmbMatSpec1
For Each mycell In rng.Cells
If mycell.Value = Me.cmbMatSpec1.Value Then
Me.cmbMatGrade1.AddItem CStr(mycell.Offset(0, 1).Value)
Me.cmbMatGrade1.ListIndex = 0
End If
Next mycell
End With

When selecting Cases 1 & 2, there is no problem, all the Comboboxes
function correctly, when Case 3 is is selected the Combobox
cmbMatSpec1, populates correctly but the Combobox cmbMatGrade1 does not
re-populate.

I am at a loss what the problem is so any advice would be appricated.

Kind Regards
Martin

 

[Tom Ogilvy]

Both of these events repopulate cmbMatGrade1

Private Sub cmbMatSpec1_Change()
Private Sub cmbMatSpec2_Change()

In the third case, you do this in separate loops. When you do that, you
reassign the global variable rng.

So I suspect when you populate cmbMatSpec2, its change event again
repopulates cmbMatGrade1, however rng is refering to

Worksheets("WeldingBaseMetals")
.Range("N2", .Cells(.Rows.Count, "N").End(xlUp))


Where I suspect there is no match to cmbMatSpec1.Value

 

[koenigma]

Tom ,

So the problem might be caused by perfomring the the third case in
separate loops. Would there be a way to perform this without separate
loops.

Thanks for your help.
Regards
Martin

 

[Tom Ogilvy]

the change event of

cmbMatSpec1_Change
cmbMatSpec2_Change

both update the list for

cmbMatGrade1

I would suggest you don't have a
cmbMatSpec2_Change
event.

But I assume there is some reason you wrote it, so only you can say if that
is appropriate.