using combo box selection to change another's rowsource

Hey,

I've encountered an issue with code for getting a userform to react dynamically to user input in combo boxes.

I have three combo boxes (Featured Post Area Category, Featured Post Category 1, Featured Post Category 2) whose contents I would like determined by the user selection on the drop down menu of another combo box on the userform, 'Select Business'.

I've attached the workbook...

I've tried to create the right code with, but I don't have the requisite skills yet to make it work yet.

Thanks in advance for any support, much appreciated

John

John,

This is your current code. I'm not entirely sure what you want to do here:

Private Sub cmdselectblog_AfterUpdate()
With Me.featuredpostareacategory
    Select Case cmdselectblog.ListIndex
        Case 0: .List = Sheets("Info").Range("I4:I14").Value
        Case 1: .List = Sheets("Info").Range("L4:L8").Value
        Case 2: .List = Sheets("Info").Range("O4:O14").Value
        Case 3: .List = Sheets("Info").Range("R4:R5").Value
        Case 4: .List = Sheets("Info").Range("U4:U5").Value
    End With
    Select Case cmdselectblog.ListIndex
        Case 0: .List = Sheets("Info").Range("J4:J5").Value
        Case 1: .List = Sheets("Info").Range("M4:M9").Value
        Case 2: .List = Sheets("Info").Range("P4:P5").Value
        Case 3: .List = Sheets("Info").Range("S4:S5").Value
        Case 4: .List = Sheets("Info").Range("V4:V5").Value
    End With
    Select Case cmdselectblog.ListIndex
        Case 0: .List = Sheets("Info").Range("K4:K5").Value
        Case 1: .List = Sheets("Info").Range("N4:N9").Value
        Case 2: .List = Sheets("Info").Range("Q4:Q5").Value
        Case 3: .List = Sheets("Info").Range("T4:T5").Value
        Case 4: .List = Sheets("Info").Range("W4:W5").Value
    End With
    End Select
End Sub

So I took the first select case statement and adjusted it so that it will update the combobox "featuredpostareacategory" rowsource:

Private Sub cmdselectblog_AfterUpdate()
    
    With Me.featuredpostareacategory
        Select Case cmdselectblog.ListIndex
            Case 0: .RowSource = "Info!I4:I14"
            Case 1: .RowSource = "Info!L4:L8"
            Case 2: .RowSource = "Info!O4:O14"
            Case 3: .RowSource = "Info!R4:R5"
            Case 4: .RowSource = "Info!U4:U5"
        End Select
    End With
    
End Sub

Is something like that what you're looking for?

~tigeravatar

Hey,

thanks for the code, that seems to have worked for the most part... Although I have encountered a different problem now.

I have adapted your code so that it works for all three combo boxes now; they all now change their content depending on the option chosen in a previous combo box.

Now when I use the userform, when I hit 'Add', I receive the following error:-

Compile error: method or data member not found

I have attached a sample workbook. Any ideas how to fix this?

John

Hi Roy,

Thanks again for your assistance, and I take your point on reading up about VBA which I will... I have learnt a fair amount on the fly aswell, but i'll be sure to cover the basics - thanks for the link

I see now your reference to the highlighting of the text when the ''Compile error'' pops up, I had renamed the combo box without consideration of the code, school boy error...

So I have gone through and corrected the mis-spellings and a new Compile error has popped up, one that I can't fix with my recent understanding of the highlighting of the error...

Its: ''Compile error: only comments may appear after End Sub, End Function, Or End Property''

I've attached another copy of the worksheet

Any help is again greatly appreciated.

Cheers,

John