Complex Problem!!!!

[ryanesx7]

i have a sheet with many different artists on and venues.
on a user form i have 2 comboboxes, when you choose an artist in the first combo box, i would like it to only display the appropiate venues for that artist.

this is the formula i have so far

Sheets("Concerts (2)").Select
If ConcertComboBox = "Arctic Monkeys" Then
VenueComboBox.RowSource = ARC_VENUE
ElseIf ConcertComboBox = "Babyshambles" Then
VenueComboBox = "WATEVA"
ElseIf ConcertComboBox = "Bloc Party" Then
VenueComboBox.RowSource = "Liverpool Academy" & "Manchester Apollo"
ElseIf ConcertComboBox = "Blondie" Then
VenueComboBox = "St David's Hall"
ElseIf ConcertComboBox = "Clor" Then
VenueComboBox.RowSource = "Coloseum" & "Waterfront"
ElseIf ConcertComboBox = "Dead 60s" Then
VenueComboBox.RowSource = "Carling Academy 2 Birmingham" & "Waterfront"
ElseIf ConcertComboBox = "Duke Spirit" Then
VenueComboBox.RowSource = "Carling Academy 2 Birmingham"
ElseIf ConcertComboBox = "Editors" Then
VenueComboBox.RowSource = "Carling Academy Brixton"
ElseIf ConcertComboBx = "Futureheads" Then
VenueComboBox.RowSource = "Carling Academy Brixton" & "Norwich UEA" & "Leeds Uni Union"
ElseIf ConcertComboBox = "Goldfrapp" Then
VenueComboBox.RowSource = "Carling Apollo" & "Leeds Uni Union"
ElseIf ConcertComboBox = "Kid Carptet" Then
VenueComboBox.RowSource = "Roadmender" & "Bar Academy Birmingham"
ElseIf ConcertComboBox = "Paddingtons" Then
VenueComboBox.RowSource = "Roadmender" & "Coloseum"
ElseIf ConcertComboBox = "Test Icicles" Then
VenueComboBox.RowSource = "Birmingham Barfly" & "Rivermead" & "Blank Canvas"
ElseIf ConcertComboBox = "The Go! Team" Then
VenueComboBox.RowSource = "Carling Academy Liverpool" & "The Plug"
ElseIf ConcertComboBox = "Thee Unstrung" Then
VenueComboBox.RowSource = "Coloseum"
End If

[ryanesx7]

if you have any questions that would help you answer the question then please ask!

[Hack]

Excel VBA question moved to Office Development.

[DKenny]

Ryan
One way to do this is to create a crossreference list of Bands to Venues. This list should contain 2 columns only and have an entry for each venue that each band can play at.
Then when the "Band" combo is change you can run an event macro that populates the "Venue" combo with the matching entries from the crossreference table.
Here is some sample code. You will need to change the names of the combos to your names.
Also, if you decide to store the xref list on a seperate worksheet, you will need to use that sheet name in the setting of the Xrefrange variable.

Option Explicit
 
Private Sub ComboBox1_Change()
Dim TheBand As String
Dim XrefRange As Range
Dim RowNum As Integer
    
    'Get the Selected Band
    TheBand = Me.ComboBox1.Value
    
    'Reset the 2nd Combobox
    Me.ComboBox2.Clear
    
    'The range containing the band to venue crossreference
    Set XrefRange = Range("BANDVENUE")
    
    With XrefRange
        'Loop through the Xref range
        For RowNum = 1 To .Rows.Count
            'Find Entries for the selected band
            If .Cells(RowNum, 1) = TheBand Then
                'Add the venue to the 2nd Combobox
                Me.ComboBox2.AddItem .Cells(RowNum, 2)
            End If
        Next RowNum
    End With
    
    'Select the 1st possible venue
    Me.ComboBox2.ListIndex = 0
End Sub

[DKenny]

Ryan
Per your PM, here is a sample of how this code works.

[ryanesx7]

thank you very much!
Ryan

[DKenny]

Hi sorry to bother you, still on that project, diong the write up bit now of how i have done it, was wondering about that file in the post:

how do i name a combo box??
also, in the VB code what does the me.combobox1.value mean???

thanks in anticipation

Ryan

To name a combobox:
Right-click on the Combo, select properties and type in a new name (it will be the first property in the Alphabetic list.

what does the me.combobox1.value mean?
As this procedure is in the code page for a worksheet object...
The me refers to the worksheet
The combobox1 refer to a combobox called "combobox1" on the worksheet.

[ryanesx7]

here is the spreadsheet Dkenny if you need it, thanks again

Ry