Use two filters at the same time

[ceci_teran_m]

hello , i use two listbox that hide and show columns in a same range, the thing is that when i run one the rules from the other not take it in count. I want basically to run the first listbox rules and then from the columns that are visible run the second one:my idea es basically that i can run the listperiodvaluationbox and from that run the listratiovaluationbox
this is my complete code and will mark with asterisc where i left sorry for the typos and the english

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Dim ListPeriod As MSForms.ListBox
Dim PeriodObject As OLEObject

Dim ListRatio As MSForms.ListBox
Dim RatioObject As OLEObject

Dim i As Long

Dim isEmptyRatio As Boolean
Dim ArrayRatios() As String
Dim showRatio As Range

Set PeriodObject = Me.OLEObjects("ListPeriodValuationBox")
Set ListPeriod = PeriodObject.Object

Set RatioObject = Me.OLEObjects("ListRatioValuationBox")
Set ListRatio = RatioObject.Object

If Target.Count > 1 Then
Exit Sub
Else
'-----------------------------------------------------------------------
' When you click the cell next to PERIOD cell appears the List Period
'-----------------------------------------------------------------------
If Not Intersect(Target, [D2]) Is Nothing Then
Set fillRng1 = Target
With PeriodObject
.Left = fillRng1.Left
.Top = fillRng1.Top + 25
.Width = fillRng1.Width
.Visible = True
End With
Else
PeriodObject.Visible = False
If Not fillRng1 Is Nothing Then
fillRng1.ClearContents
With ListPeriod
If .ListCount <> 0 Then
For i = 0 To .ListCount - 1
If fillRng1.value = "" Then
If .Selected(i) Then fillRng1.value = .List(i)
Else
If .Selected(i) Then fillRng1.value = _
fillRng1.value & "," & .List(i)
End If
Next
End If
End With
Set fillRng1 = Nothing
End If
End If

'-----------------------------------------------------------------------
' When you click the cell next to RATIO cell appears the List Ratio
'-----------------------------------------------------------------------
If Not Intersect(Target, [D5]) Is Nothing Then
Set fillRng2 = Target
With RatioObject
.Left = fillRng2.Left
.Top = fillRng2.Top + 25
.Width = fillRng2.Width
.Visible = True
End With
Else
RatioObject.Visible = False
If Not fillRng2 Is Nothing Then

fillRng2.ClearContents
With ListRatio
If .ListCount <> 0 Then
For i = 0 To .ListCount - 1
If fillRng2.value = "" Then
If .Selected(i) Then fillRng2.value = .List(i)
Else
If .Selected(i) Then fillRng2.value = _
fillRng2.value & "," & .List(i)
End If
Next
End If
End With
Set fillRng2 = Nothing
End If
End If
End If
End Sub

Private Sub ListPeriodValuationBox_click()

'-----------------------------------------------------------------------
' Filter selection
'-----------------------------------------------------------------------
Dim PY2_v As Range
Dim PY1_v As Range
Dim FY0_v As Range
Dim FY1_v As Range
Dim FY2_v As Range
Dim rcrit As Range
Dim n As Integer
Set rcrit = Sheets("valuation").Range("G:AO")
Set PY2_v = Sheets("valuation").Range("G:G,L:L,Q:Q,V:V,AA:AA,AF:AF,AK:AK")
Set PY1_v = Sheets("valuation").Range("H:H,M:M,R:R,W:W,AB:AB,AG:AG,AL:AL")
Set FY0_v = Sheets("valuation").Range("I:I,N:N,S:S,X:X,AC:AC,AH:AH,AM:AM")
Set FY1_v = Sheets("valuation").Range("J:J,O:O,T:T,Y:Y,AD:AD,AI:AI,AN:AN")
Set FY2_v = Sheets("valuation").Range("K:K,P:P,U:U,Z:Z,AE:AE,AJ:AJ,AO:AO")
Application.ScreenUpdating = False
rcrit.EntireColumn.Hidden = True
With ListPeriodValuationBox
For n = 0 To .ListCount - 1
If .Selected(n) Then
Select Case .List(n)
Case "FY-2"
PY2_v.EntireColumn.Hidden = False
Case "FY-1"
PY1_v.EntireColumn.Hidden = False
Case "FY-2"
PY2_v.EntireColumn.Hidden = False
Case "FY0"
FY0_v.EntireColumn.Hidden = False
Case "FY1"
FY1_v.EntireColumn.Hidden = False
Case "FY2"
FY2_v.EntireColumn.Hidden = False
End Select
End If
Next n
End With
End Sub
Private Sub ListRatioValuationBox_click()
Dim EV_EBITDA As Range
Dim P_FCF As Range
Dim P_B As Range
Dim EBITDA As Range
Dim P_E As Range
Dim P_S As Range
Dim EPS As Range
Dim rcrit As Range
Dim M As Integer
Set rcrit = Sheets("valuation").Range("G:AO")
Set EV_EBITDA = Sheets("valuation").Range("G:K")
Set P_FCF = Sheets("valuation").Range("L:P")
Set P_B = Sheets("valuation").Range("Q:U")
Set EBITDA = Sheets("valuation").Range("V:Z")
Set P_E = Sheets("valuation").Range("AA:AE")
Set P_S = Sheets("valuation").Range("AF:AJ")
Set EPS = Sheets("valuation").Range("AK:AO")
Call ListPeriodValuationBox_click
Application.ScreenUpdating = False
rcrit.EntireColumn.Hidden = True
With ListRatioValuationBox
For M = 0 To .ListCount - 1
If .Selected(M) Then
Select Case .List(M)
Case "EV_EBITDA"
EV_EBITDA.EntireColumn.Hidden = False
Case "P_FCF"
P_FCF.EntireColumn.Hidden = False
Case "P_B"
P_B.EntireColumn.Hidden = False
Case "EBITDA"
EBITDA.EntireColumn.Hidden = False
Case "P_E"
P_E.EntireColumn.Hidden = False
Case "P_S"
P_S.EntireColumn.Hidden = False
Case "EPS"
EPS.EntireColumn.Hidden = False
Case ""
rcrit.EntireColumn.Hidden = True
End Select

End If
Next M
End With
End Sub

**Sub Filter_Click()
Dim n As Integer
Dim M As Integer
Dim rcrit As Range
Dim list_n As Range
Set rcrit = Sheets("valuation").Range("G:AO")
Call ListPeriodValuationBox_click
'Set list_n = Sheets("valuation").Range("G:AO").Columns = Visible'From list_n
'Call ListRatioValuationBox_click


End Sub

[Richard Buttrey]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html


In addition you'll probably get more response if you upload the workbook.