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]

I tested post#7 code and it works with a dynamic range

But you could also use

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

 

[Yongle]

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.

That is exactly the same as what I am doing, and I get the message box if the named range does not exist
- so something (minor) must be different - I wonder what that is?

What is the NAME and FORMULA for ONE of your dynamic ranges?
- I will try to exactly mirror what you have done

 

[Sabotage]

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

Rory,

Option: Break on Unhandled Errors

 

[RoryA]

Then you should not see any errors if you have On Error Resume Next. Do you have an Exit Sub line above your error handler section?

 

[Sabotage]

That is exactly the same as what I am doing, and I get the message box if the named range does not exist
- so something (minor) must be different - I wonder what that is?

What is the NAME and FORMULA for ONE of your dynamic ranges?
- I will try to exactly mirror what you have done

One of the named Range is called SupLoc_VVLS. The formula evaluating the dynamic range is:

=OFFSET(Sup_Loc!$R$6,,,COUNTA(Sup_Loc!$R$6:$R$1048576),1)

I have already found a part solution, but I feel a bit lame cuz of it. If I put a text in all empty SupLoc ranges like "Store not set!". It works that way, but I am bugged by this now...

 

[Sabotage]

Then you should not see any errors if you have On Error Resume Next. Do you have an Exit Sub line above your error handler section?

Yes Rory. I do, so if setting the RowSource was successful, it will exit and all fine.

 

[Yongle]

So it sounds as though you are now all sorted

 

[Fluff]

In your op you have this line of code

On Error GoTo Err[COLOR=#ff0000]1[/COLOR]

Yet the error handler you showed was

Err[COLOR=#ff0000]2[/COLOR]:

Could you show us the complete code for that procedure

 

[Sabotage]

In your op you have this line of code

On Error GoTo Err[COLOR=#ff0000]1[/COLOR]

Yet the error handler you showed was

Err[COLOR=#ff0000]2[/COLOR]:

Could you show us the complete code for that procedure

Hi Fluff,
Apologies, that was just a typo. The Err2 is wrong. in Both places the Error handler is referred as Err1, so it should work. I will edit that post. Still no luck with triggering the error handler...

 

[Sabotage]

So it sounds as though you are now all sorted

Well,
Yes I have found a part solution as I said, but it still bothers me very much, why cant I trigger the error handler. Thank you for your help!