Error Getting Count In Named Range

[Ark68]

The user makes a selection from a userform's combobox control "cbx_league". In testing, the user selects "KWHLB".

Private Sub cbx_league_Change()
    Dim league As String

    If Not mbevents Then Exit Sub
    mbevents = True
    Chg_League '{frm_chg_3League}*
    'chk_main '{frm_permit}*
    bu_cbx_league = permit.cbx_league.Value
End Sub

The change executes the routine "Chg_League", which defines the ranges "nr_calibre" & "nr_dvsion" which hold contents for userform combobox lists (cbx_calibre and cbx_dvsion respectively)

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

    mbevents = False
    
    Select Case league
        'several Case Is ...
        Case Is = "KWHLB"
            'Stop
            Set nr_calibre = ws_lists.Range("G4:G4")
            Set nr_dvsion = ws_lists.Range("H42:H48") 'HOUSE
            t = 2
            bigandbad t '{frm_chg_3League}
        'several Case Is ...       
        Case Else
            MsgBox "No Calibre range available for LEAGUE [" & league & "]", vbCritical, "Error: FUNCTION"
            mbevents = False
            permit.cbx_league.Value = ""
            mbevents = True
            Exit Sub
        End Select
        mbevents = True
        
        If t <> "4" Then ActiveWorkbook.Names.Add Name:="nr_division", RefersTo:=nr_dvsion
        ActiveWorkbook.Names.Add Name:="nr_calibre2", RefersTo:=nr_calibre
End Sub

Sub bigandbad(ByRef t As Double)
                
        Dim mycell As Range
        If t = 1 Then
            'user selects from both calibre and division lists
            'prepare calibre
            If nr_calibre.Count = 1 Then
                permit.cbx_calibre.List = Array(nr_calibre.Value)
            Else
                permit.cbx_calibre.List = nr_calibre.Value
            End If
            permit.cbx_calibre.Enabled = True
            permit.cbx_calibre.BackColor = clr_blue
            'prepare division
            If nr_calibre.Count = 1 Then
                permit.cbx_division.List = Array(nr_division.Value)
            Else
                permit.cbx_division.List = Range("nr_division").Value
            End If
            permit.cbx_division.Enabled = False
            
            chk_main '{frm_permit}*
            customer '{frm_activity_reference}*
                    
        ElseIf t = 3 Then
            'prepare calibre
            If nr_calibre.Count = 1 Then
                permit.cbx_calibre.List = Array(nr_calibre.Value)
            Else
                permit.cbx_calibre.List = nr_calibre.Value
            End If
            permit.cbx_calibre.Enabled = False
            'prepare division
            If nr_calibre.Count = 1 Then
                permit.cbx_division.List = Array(Range("nr_division").Value)
            Else
                permit.cbx_division.List = Range("nr_division").Value
            End If
            permit.cbx_division.Enabled = False
            
            chk_main '{frm_permit}*
            customer '{frm_activity_reference}*
            
        ElseIf t = 2 Then
            'calibre defined, division selectable

            '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 = False
            'prepare division
            If Range("nr_calibre").Count = 1 Then
                permit.cbx_division.List = Array(Range("nr_division").Value)
            Else
                permit.cbx_division.List = Range("nr_division").Value
            End If
            permit.cbx_division.Enabled = True
            permit.cbx_division.BackColor = clr_blue
            
            chk_main '{frm_permit}*
            customer '{frm_activity_reference}*
        
        Else 't=4
            'variable division based on calibre selection (minor groups with HL & REP)
            If nr_calibre.Count = 1 Then
                permit.cbx_calibre.List = Array(nr_calibre.Value)
            Else
                permit.cbx_calibre.List = 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 '{frm_permit}*
            customer '{frm_activity_reference}*

        End If
End Sub

The line above in red stops my procedure with a "Method 'range' of object '_Global' failed.
I'd be very grateful for a solution to this problem.

 

[StephenCrump]

As nr_calibre is a Range variable, you need to test:

If nr_calibre.Count = 1 Then ...

As written, your code is looking for a non-existent Excel range called "nr_calibre".

 

[Ark68]

Thank you Stephen. That worked. I'm having a similar problem, but unsure if it's the same problem in this separate post.