Error Handler won't trigger if Runtime Error is 380 (Could not set RowSource property)

[Sabotage]

Hi Wizzards!
I have a problem not being able to trigger my error handler after the code is trying to set an "empty list" for combobox rowsource. In a nutshell my code is trying to set the rowsource for a combobox, depending on a value in another combobox's value. I have tried to put "On Error GoTo Err1" where Err1 is my Error Handler. Tried to put it before Select Case and inside Select Case but no luck what so ever. My Error Handler would just tell the user that the specified supplier does not have any store location set and exit the procedure. The user hence will know what is wrong...

Thank you in advance!

        Select Case cb_Supplier
            Case "Supplier ABC"
                On Error GoTo Err1
                cb_StoreName.RowSource = "SupLoc_ABC"   'error occurs
                On Error GoTo 0
            Case "Supplier XYZ"
                On Error GoTo Err1
                cb_StoreName.RowSource = "SupLoc_XYZ"   'error occurs
                On Error GoTo 0

        End Select

 

[Yongle]

try

       On Error Resume Next 
       Select Case cb_Supplier
            Case "Supplier ABC"
                cb_StoreName.RowSource = "SupLoc_ABC"   'error occurs
                On Error GoTo Err1
            Case "Supplier XYZ"
                cb_StoreName.RowSource = "SupLoc_XYZ"   'error occurs
                On Error GoTo Err1
        End Select

 

[Sabotage]

Thank you Yongle, but it doesn't work as well. I am going crazy right now...

 

[Yongle]

Are these comboboxes embedded on a
- worksheet ? OR UserForm ?

What is "SupLoc_ABC" ?
- is it a named range?

 

[Sabotage]

Are these comboboxes embedded on a
- worksheet ? OR UserForm ?

What is "SupLoc_ABC" ?
- is it a named range?

Yes Yongle, it is a dynamic named range. An the cboxes are both on a userform...

 

[RoryA]

What does your error handler look like? If it doesn't use Resume, your code will fail on the second error.

 

[Yongle]

In that case try this method to set the RowSource

cb_StoreName.RowSource = ThisWorkbook.Names("SupLoc_ABC").RefersTo

(have not checked if this works with dynamic named ranges - will test when back at PC later today)

But I am a little puzzled at where you are going with this code
- are you planning on listing all the possible suppliers inside Select Case?

You could use something like this if the named ranges are all SupLoc_ followed by the value found in cb_Supplier

Private Sub cb_Supplier_Change()
        Dim n As String
        n = "SupLoc_" & cb_Supplier.Value
        On Error Resume Next
            cb_StoreName.RowSource = ThisWorkbook.Names(n).RefersTo
                If Err.Number > 0 Then
                    MsgBox "named range " & n & "does not exist"
                    Exit Sub
                End If
End Sub

 

[Sabotage]

What does your error handler look like? If it doesn't use Resume, your code will fail on the second error.

Hi Rory,
Thank you for trying to help. The error handler looks as the below code. I thought that when the error occurs it should just jump to the handler line and tell the user what the problem thereafter exit the sub. Where does it needs to Resume? The named range is currently empty, so returns an invalid 0 length list which cannot be assigned to the RowSource property.

Err2:
    'if there is no store for the specified supplier!
        MsgBox "This specified Supplier doesn't have any Store Location set!" & vbNewLine & vbNewLine & _
            "Please set up all Store names before trying to register a new load!", vbInformation, "Error..."
        Application.ScreenUpdating = True
        Exit Sub

 

[Sabotage]

Yongle,
The SupLoc_**** is a dynamic list of locations under each supplier. On the UserForm when the the Supplier is being picked cb_Supplier then the next cbox is populating the list of corresponding locations. I have tried all your suggestions, no matter how I try to trigger my ErrorHandler or Resume Next. Just keep triggering the VBA error:

Run-time error '380':
Could not set the RowSource property. Invalid property value.

 

[RoryA]

In the VB Editor, under Tools - Options, on the General tab, which error handling option do you have checked?