Hello,
There two (ActiveX) listboxes on sheet (Monthlist & Yearlist). Each is populated with unique values from columns H(months) and I(years) (see code below). The user will select month(s) available in Monthlist. I would like the available items in ‘Yearlist’ to reflect the selection in ‘Monthlist’. For example, if there is data on sheet for May, 2015 and June, 2016, and the user selects ‘May’ from Monthlist, ‘2015’ should only be available on ‘Yearlist’ for selection. (‘2016’ should be grayed out or not present).
Please suggest how to accomplish this? Thank you very much.
There two (ActiveX) listboxes on sheet (Monthlist & Yearlist). Each is populated with unique values from columns H(months) and I(years) (see code below). The user will select month(s) available in Monthlist. I would like the available items in ‘Yearlist’ to reflect the selection in ‘Monthlist’. For example, if there is data on sheet for May, 2015 and June, 2016, and the user selects ‘May’ from Monthlist, ‘2015’ should only be available on ‘Yearlist’ for selection. (‘2016’ should be grayed out or not present).
Please suggest how to accomplish this? Thank you very much.
Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#00008B]Sub[/COLOR][COLOR=black] UniqueMonthsAndYears[/COLOR][COLOR=black]([/COLOR][COLOR=#00008B]ByVal[/COLOR][COLOR=black] ws [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Object[/COLOR][COLOR=black])[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] LastRow[/COLOR][COLOR=black],[/COLOR][COLOR=black] i[/COLOR][COLOR=black],[/COLOR][COLOR=black] j[/COLOR][COLOR=black],[/COLOR][COLOR=black] k [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Long[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] c [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] Range
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] MyArUniqVal[/COLOR][COLOR=black]()[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Variant[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] i2[/COLOR][COLOR=black],[/COLOR][COLOR=black] j2[/COLOR][COLOR=black],[/COLOR][COLOR=black] k2 [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Long[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] c2 [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] Range
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] MyArUniqVal2[/COLOR][COLOR=black]()[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Variant[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] MonthList [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] MSForms[/COLOR][COLOR=black].[/COLOR][COLOR=black]ListBox
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] YearList [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] MSForms[/COLOR][COLOR=black].[/COLOR][COLOR=black]ListBox
[/COLOR][COLOR=#00008B]ReDim[/COLOR][COLOR=black] MyArUniqVal[/COLOR][COLOR=black]([/COLOR][COLOR=maroon]0[/COLOR][COLOR=black])[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]ReDim[/COLOR][COLOR=black] MyArUniqVal2[/COLOR][COLOR=black]([/COLOR][COLOR=maroon]0[/COLOR][COLOR=black])[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] rng [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] Range
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=black] cl [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=black] Range
LastRow [/COLOR][COLOR=black]=[/COLOR][COLOR=black] ActiveSheet[/COLOR][COLOR=black].[/COLOR][COLOR=black]Range[/COLOR][COLOR=black]([/COLOR][COLOR=maroon]"H"[/COLOR][COLOR=black] [/COLOR][COLOR=black]&[/COLOR][COLOR=black] Rows[/COLOR][COLOR=black].[/COLOR][COLOR=black]Count[/COLOR][COLOR=black]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=black]([/COLOR][COLOR=black]xlUp[/COLOR][COLOR=black]).[/COLOR][COLOR=black]Row
[/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=black] rng [/COLOR][COLOR=black]=[/COLOR][COLOR=black] Range[/COLOR][COLOR=black]([/COLOR][COLOR=maroon]"H9:H"[/COLOR][COLOR=black] [/COLOR][COLOR=black]&[/COLOR][COLOR=black] LastRow[/COLOR][COLOR=black])[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=black] ThisWorkbook[/COLOR][COLOR=black].[/COLOR][COLOR=black]ActiveSheet
[/COLOR][COLOR=#00008B]For[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Each[/COLOR][COLOR=black] cl [/COLOR][COLOR=#00008B]In[/COLOR][COLOR=black] rng
[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=black] cl[/COLOR][COLOR=black].[/COLOR][COLOR=black]EntireRow[/COLOR][COLOR=black].[/COLOR][COLOR=black]Hidden [/COLOR][COLOR=black]=[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]False[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=black] [/COLOR][COLOR=gray]'//Use Hidden property to check if filtered or not[/COLOR][COLOR=black]
Debug[/COLOR][COLOR=black].[/COLOR][COLOR=black]Print cl
[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=black] cl[/COLOR][COLOR=black].[/COLOR][COLOR=black]Value [/COLOR][COLOR=black]<>[/COLOR][COLOR=black] cl[/COLOR][COLOR=black].[/COLOR][COLOR=black]Offset[/COLOR][COLOR=black]([/COLOR][COLOR=maroon]1[/COLOR][COLOR=black],[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]0[/COLOR][COLOR=black]).[/COLOR][COLOR=black]Value [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=black] [/COLOR][COLOR=gray]'~~~~~MonthArray[/COLOR][COLOR=black]
MyArUniqVal[/COLOR][COLOR=black]([/COLOR][COLOR=black]UBound[/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal[/COLOR][COLOR=black]))[/COLOR][COLOR=black] [/COLOR][COLOR=black]=[/COLOR][COLOR=black] cl[/COLOR][COLOR=black].[/COLOR][COLOR=black]Value
[/COLOR][COLOR=#00008B]ReDim[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Preserve[/COLOR][COLOR=black] MyArUniqVal[/COLOR][COLOR=black]([/COLOR][COLOR=black]UBound[/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal[/COLOR][COLOR=black])[/COLOR][COLOR=black] [/COLOR][COLOR=black]+[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]1[/COLOR][COLOR=black])[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=black] cl[/COLOR][COLOR=black].[/COLOR][COLOR=black]Offset[/COLOR][COLOR=black]([/COLOR][COLOR=maroon]0[/COLOR][COLOR=black],[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]1[/COLOR][COLOR=black]).[/COLOR][COLOR=black]Value [/COLOR][COLOR=black]<>[/COLOR][COLOR=black] cl[/COLOR][COLOR=black].[/COLOR][COLOR=black]Offset[/COLOR][COLOR=black]([/COLOR][COLOR=maroon]1[/COLOR][COLOR=black],[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]1[/COLOR][COLOR=black]).[/COLOR][COLOR=black]Value [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=black] [/COLOR][COLOR=gray]'Year Array[/COLOR][COLOR=black]
MyArUniqVal2[/COLOR][COLOR=black]([/COLOR][COLOR=black]UBound[/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal2[/COLOR][COLOR=black]))[/COLOR][COLOR=black] [/COLOR][COLOR=black]=[/COLOR][COLOR=black] cl[/COLOR][COLOR=black].[/COLOR][COLOR=black]Offset[/COLOR][COLOR=black]([/COLOR][COLOR=maroon]0[/COLOR][COLOR=black],[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]1[/COLOR][COLOR=black]).[/COLOR][COLOR=black]Value
[/COLOR][COLOR=#00008B]ReDim[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Preserve[/COLOR][COLOR=black] MyArUniqVal2[/COLOR][COLOR=black]([/COLOR][COLOR=black]UBound[/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal2[/COLOR][COLOR=black])[/COLOR][COLOR=black] [/COLOR][COLOR=black]+[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]1[/COLOR][COLOR=black])[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=black] cl
[/COLOR][COLOR=#00008B]ReDim[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Preserve[/COLOR][COLOR=black] MyArUniqVal[/COLOR][COLOR=black]([/COLOR][COLOR=black]UBound[/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal[/COLOR][COLOR=black])[/COLOR][COLOR=black] [/COLOR][COLOR=black]-[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]1[/COLOR][COLOR=black])[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]ReDim[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Preserve[/COLOR][COLOR=black] MyArUniqVal2[/COLOR][COLOR=black]([/COLOR][COLOR=black]UBound[/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal2[/COLOR][COLOR=black])[/COLOR][COLOR=black] [/COLOR][COLOR=black]-[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]1[/COLOR][COLOR=black])[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]With[/COLOR][COLOR=black]
[/COLOR][COLOR=gray]'Fill the listbox[/COLOR][COLOR=black]
ws[/COLOR][COLOR=black].[/COLOR][COLOR=black]YearList[/COLOR][COLOR=black].[/COLOR][COLOR=black]Clear
[/COLOR][COLOR=#00008B]For[/COLOR][COLOR=black] k2 [/COLOR][COLOR=black]=[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]0[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]To[/COLOR][COLOR=black] UBound[/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal2[/COLOR][COLOR=black])[/COLOR][COLOR=black]
ws[/COLOR][COLOR=black].[/COLOR][COLOR=black]YearList[/COLOR][COLOR=black].[/COLOR][COLOR=black]AddItem [/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal2[/COLOR][COLOR=black]([/COLOR][COLOR=black]k2[/COLOR][COLOR=black]))[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=black] k2
ws[/COLOR][COLOR=black].[/COLOR][COLOR=black]MonthList[/COLOR][COLOR=black].[/COLOR][COLOR=black]Clear
[/COLOR][COLOR=#00008B]For[/COLOR][COLOR=black] k [/COLOR][COLOR=black]=[/COLOR][COLOR=black] [/COLOR][COLOR=maroon]0[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]To[/COLOR][COLOR=black] UBound[/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal[/COLOR][COLOR=black])[/COLOR][COLOR=black]
ws[/COLOR][COLOR=black].[/COLOR][COLOR=black]MonthList[/COLOR][COLOR=black].[/COLOR][COLOR=black]AddItem [/COLOR][COLOR=black]([/COLOR][COLOR=black]MyArUniqVal[/COLOR][COLOR=black]([/COLOR][COLOR=black]k[/COLOR][COLOR=black]))[/COLOR][COLOR=black]
[/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=black] k
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=black] [/COLOR][COLOR=#00008B]Sub[/COLOR]</code>