Using one combobox to limit values in another on same form

[lennie]

Anyone know how I can use the value selected by a user for one combobox to determine the options for a second combobox on the same form?

Basically I want people to select a year and a month. Which year they select determines which months are available as follows:

2003: August, September, October, November December
2004: All months
2005: January, February and more as today's date progresses
2006: Not available yet but will become available.

I can easily create lists of the months and years. What I can't do is make the year entered determine the months that are available for selection.

I could use a second userform but that just wouldn't be very neat :wink: !

Would greatly appreciate suggestions.

Thanks.

 

[SteveO59L]

Re: Using one combobox to limit values in another on same fo

Try Data, Validation, Limit to List with indirect.


HTH

 

[Andrew Poulsom]

Re: Using one combobox to limit values in another on same fo

Try this:

Private Sub ComboBox1_Change()
    Dim x As Integer
    ComboBox2.Clear
    Select Case ComboBox1.Value
        Case 2003
            For x = 8 To 12
                ComboBox2.AddItem Format(DateSerial(Year(Date), x, 1), "mmmm")
            Next x
        Case 2004
            For x = 1 To 12
                ComboBox2.AddItem Format(DateSerial(Year(Date), x, 1), "mmmm")
            Next x
        Case 2005
            For x = 1 To Month(Date)
                ComboBox2.AddItem Format(DateSerial(Year(Date), x, 1), "mmmm")
            Next x
        Case 2006
    
    End Select
End Sub

 

[lennie]

Re: Using one combobox to limit values in another on same fo

Thank you Andrew, that's great!

(and thanks Steve too, but I used Andy's because he'd actually written the syntax out for me!)

Now though, is there a way of referencing the 'Case' statements to today's date so that all through this year Case 2005 will be

For x = 1 To Month(Date)

but when the date changes to 2006 Case 2006 will be

For x = 1 To Month(Date)

and Case 2005 will automatically change to

For x = 1 To 12

and so on for future years.

Sorry if I'm going about this all the wrong way - I did a two day VBA course and now I suspect I may have got myself out of my depth somewhat!!

Thanks

 

[lennie]

Re: Using one combobox to limit values in another on same fo

OK, I think I've worked out a way of doing it....but it's not very neat so I'd still be happy to hear suggestions!

Thanks again.