Defined List For Combobox Not Being Carried Over

[Ark68]

I have this code that populates different userform comboboxes based on the previous combobox's selection by the user. For confidentiality, I have reduced the full code to just (what I think is) pertinent. If its necessary to share more code, I can redact the confidential stuff. Please ask for clarification if it helps.

'permit.cbx_league' is a combo box in userform 'permit'. A change in that combox's value triggers this code:

Sub Chg_League()
    Dim nr_calibre As Range
    Dim nr_dvsion As Range
    Dim a1 As Range, a2 As Range
    Dim t As Double
    Dim inc As Double
    
    league = permit.cbx_league.Value
    permit.cbx_league.BackColor = vbWhite

'Stop
    mbevents = False
    
    Select Case league
          Case Is = "WMSA"
                 Stop
                 Set nr_calibre = ws_lists.Range("G28:G29")
                 t = 4
                 bigandbad t
     End Select
     mbevents = True
        
     ActiveWorkbook.Names.Add Name:="nr_division", RefersTo:=nr_dvsion
     ActiveWorkbook.Names.Add Name:="nr_calibre", RefersTo:=nr_calibre      'error line 1

End Sub

The user selects "WMSA" from the cbx_league dropdown.
The range nr_calibre is defined (two cells - G28 & G29 - from worksheet ws_list)
Routine bigand bad is called carrying over a value for t of 4

Here is the bigandbad code

Sub bigandbad(ByRef t As Double)
     Else 't=4
            'variable division based on calibre selection (minor groups with HL & REP)
            'prepare calibre
            If Range("nr_calibre").Count = 1 Then
                permit.cbx_calibre.List = Array(Range("nr_calibre").Value)
            Else
                permit.cbx_calibre.List = Range("nr_calibre").Value
            End If
            permit.cbx_calibre.Enabled = True
            permit.cbx_calibre.BackColor = clr_blue
            permit.cbx_division.BackColor = vbWhite
            permit.cbx_division.Enabled = False
            
            chk_main

        End If
End Sub

chk_main is called, but unlikely contributing to the problem. All it does is do a count of fields in the userform with values in them.

Sub chk_main()

    fc = 0 'counter of filled fields
    With permit
        If .tb_pn.Value <> "" Then fc = fc + 1
        If .cbx_rcode.Value <> "" Then fc = fc + 1
        If .tb_aname.Value <> "" Then fc = fc + 1
        If .cbx_func.Value <> "" Then fc = fc + 1
        If .cbx_league.Value <> "" Then fc = fc + 1
        If .cbx_calibre.Value <> "" Then fc = fc + 1
        If .cbx_division.Value <> "" Then fc = fc + 1
        
        If .frm_cname.Value <> "" Then fc = fc + 1
        If .frm_ctele1.Value <> "" Then fc = fc + 1
        If .frm_cname.Value = "" Or .frm_cname.Value = "" Then
            .frm_cust.Caption = "Customer"
            .frm_cust.Enabled = False
        End If
        
        
        If .cbx_rcode.Value Like "D*" Then
            If .cbx_f1_bd.Value <> "" Then fc = fc + 1
            If .cbx_f1_pd.Value <> "" Then fc = fc + 1
            If .cbx_f1_bb.Value <> "" Then fc = fc + 1
            If .cbx_f1_cb.Value <> "" Then fc = fc + 1
            If .cbx_f1_cl.Value <> "" Then fc = fc + 1
            If .cbx_f1_pc.Value <> "" Then fc = fc + 1
            If .cbx_f1_rl.Value <> "" Then fc = fc + 1
            If .cbx_f1_sl.Value <> "" Then fc = fc + 1
            If .cbx_f1_sm.Value <> "" Then fc = fc + 1
            If .cbx_f1_sb.Value <> "" Then fc = fc + 1
        
        ElseIf .cbx_rcode.Value Like "F*" Then
            If .cbx_f2_fc.Value <> "" Then fc = fc + 1
            If .cbx_f2_gl.Value <> "" Then fc = fc + 1
        
        ElseIf .cbx_rcode.Value Like "C*" Then
            If .cbx_f3_cl.Value <> "" Then fc = fc + 1
            If .cbx_f3_vn.Value <> "" Then fc = fc + 1
            
        ElseIf rcode = "GM" Then

        ElseIf rcode = "GS" Then

        ElseIf rcode = "SE" Then

        Else 'If rcode = "TR" Then
        
        End If
        
        .tb_miof.Caption = miof
        .tb_mino.Caption = fc
        
        If fc = miof Then
            .btn_submit.Enabled = True
        Else
            .btn_submit.Enabled = False
        End If
    End With
        
End Sub

The intended behaviour after the user selects 'WMSA' is that the 'cbx_calibre' combobox is populated with a list defined by range 'nr_calibre'. In this case, the list is two values - REC and COMP. Combobox 'cbx_dvsion' remains disabled until the user selects a value from 'cbx_calibre'. But something is not working right.

1) the dropdown list for 'cbx_calibre' does not contain the values in G28 and G29. Instead, it has the values of G2:G9. I do not know how 'nr_calibre' is being changed from it's setting in 'Sub Chg_League'
2) I am getting an "Application-defined or object-defined error" with the line marked "error line 1" in 'Sub Chg_League'. This line applies the name nr_division to range nr_dvsion. I assume this is because no range has been set for 'nr_dvsion'. This won't happen until the user selects it from cbx_dvsion which only becomes available after a value is selected from cbx_calibre. How can I avoid this error? It's premature at this point to define nr_division in this case, but for others nr_dvsion have been defined already.

 

[Ark68]

To add to this after some debugging efforts ....

nr_calibre and nr_dvsion have been both publicly declared as ranges
When I step through the debugging loop (in blue) to determine the contents of nr_calibre, it is not reflecting the values assigned in the row above (ws_lists.range("B28:B29"). Instead, it reveals the contents from ws_lists.range("G2:G9").

It appears that range object variable nr_range isn't taking it's assigned values.

Public nr_calibre as range
Public nr_dvsion as range

Sub Chg_League()

    Dim a1 As Range, a2 As Range
    Dim t As Double
    Dim inc As Double
    Dim mycell as range
    
    league = permit.cbx_league.Value
    permit.cbx_league.BackColor = vbWhite

'Stop
    mbevents = False
    
    Select Case league
          Case Is = "WMSA"
                 Stop
                 Set nr_calibre = ws_lists.Range("G28:G29")
                 For each mycell in nr_calibre
                      debug.print mycell.value
                 Next mycell
                 t = 4
                 bigandbad t
     End Select
     mbevents = True
        
     ActiveWorkbook.Names.Add Name:="nr_division", RefersTo:=nr_dvsion
     ActiveWorkbook.Names.Add Name:="nr_calibre", RefersTo:=nr_calibre      'error line 1

End Sub

 

[Ark68]

Some more debugging results.

Just for curiosity, I thought I'd 'clear' the range 'nr_calibre' hoping it would get rid of anything that might be in it before trying to re-set it to ws_lists.range("G28:G29"). I also cleared the combobox 'cbx_calibre" to get rid of any list that might still be held for it.

Sub Chg_League()
Stop

    'Dim nr_calibre As Range
    'Dim nr_dvsion As Range
    Dim a1 As Range, a2 As Range
    Dim t As Double
    Dim inc As Double
    Dim mycell As Range
    
    league = permit.cbx_league.Value
    Range("nr_calibre").Clear
    permit.cbx_league.BackColor = vbWhite
    permit.cbx_calibre.Clear
    For Each mycell In Range("nr_calibre")
        Debug.Print mycell.Value
    Next mycell
    
'Stop
    mbevents = False

    Select Case league
          Case Is = "WMSA"
                 Stop
                 Set nr_calibre = ws_lists.Range("G28:G29")
                 For each mycell in nr_calibre
                      debug.print mycell.value
                 Next mycell
                 t = 4
                 bigandbad t
     End Select
     mbevents = True
        
     ActiveWorkbook.Names.Add Name:="nr_division", RefersTo:=nr_dvsion
     ActiveWorkbook.Names.Add Name:="nr_calibre", RefersTo:=nr_calibre      'error line 1

End Sub

With this in place, when I stepped through the debug loop, all I got was empty values. The immediate window scrolled empty lines for each mycell in the loop, about 6 or 7. That did not help, so I removed the clear statement for the range. I saved and tried again. I was expecting that the old values of nr_calibre ("G2:G9") be returned, but they were not! I got the blank line returns. I've searched all my code to find where nr_calibre is being set, and I cannot find any instance where it is set before the 'chg_league' routine. I would have thought that whatever nr_calibre was previously would be replaced by a new range when re-set, but it's holding onto a value that isn't ever set to be in a position to be replaced.

 

[Ark68]

In fact, this behaviour is exhibited in all my instances where I set nr_calibre in the chg_league sub (all the selects I didn't include)

 

[Ark68]

I was able to diagnose the issue. I hadn't noticed that I named a range the same as the range variable. I had been setting the range variable nr_calibre, and naming that range 'nr_calibre' in the workbook.