Dynamic Named Range as Row Source in Userform Combo Box

[jaykilleen]

Hi fellow Chandooers


I am about half way through Chandoos VBA training course but my new skills are creating demand fasting than I can learn lol!


I am working on a userform with a combo box that is generated by Dynamic Named Ranges that are stored in a worksheet. When the combo box for "Market Segment" is populated the "Line of Business" Combo Box Row Source is updated to reference the name of the Named Range. Essentially like any Dynamic Named Ranges with Validation Lists but done in a Userform setting.


For Example, Market Segment is "Builders" and I have a Named Range called ValBuilders. The Combox Box for Line of Business then references ValBuilders as the Row Source. This way the ComboBox options for Line of Business are dynamic based on the Market Segment in the previous Combo Box.


However, as some of my Market Segments have spaces ("Cash Sales") I have chosen to use the first word of the Market Segment for the named range ("ValCash"). In my userform code below I am finding it very difficult to split out the first word of the "Market Segment" to use in the Row Source property. I continue to get Type Mismatch Error 13. I tried changing the Dim As Variant but I still get the same error.

Private Sub cboxMarketSegment_Change()


    Dim ValMarketSegment As String


    ValMarketSegment = Split(cboxMarketSegment.Value, " ")


    cboxLineofBusiness.RowSource = "Val" & ValMarketSegment


End Sub

Does anyone have any ideas on how I can fix this? Or have an alternative way to do it. I could do the whole word and strip out spaces but some have '&' symbols etc.


Thank you for reading my problem.

 

[jaykilleen]

I have since solved this problem by changing my Dynamic Ranged Ranges to space and symbol delimited names (ie ValCashSales instead of ValCash for the Cash Sales Market Segment) and then added the following lines into my code to remove spaces and symbols (where required).

Private Sub cboxMarketSegment_Change()


    Dim ValMarketSegment As String


    ValMarketSegment = Replace(cboxMarketSegment, " ", "")

ValMarketSegment = Replace(ValMarketSegment, "&", "")

ValMarketSegment = Replace(ValMarketSegment, "(", "")

ValMarketSegment = Replace(ValMarketSegment, ")", "")


    cboxLineofBusiness.RowSource = "Val" & ValMarketSegment


End Sub