Include 3 sheets only in filtering

[djemy1975]

Dear friends,

First of all,thank you for your habitual interest in my posts .In fact this is not a code made by me but I found it near to my approach so I used it.The first problem is the ability to make my combobox6 (combobox to filter sheets)to display only three sheets names ("BASIC";"SPECIFIC" and "IMPROVE") so that whenever I add a sheet ,it will not be included in the combo.

The second problem is that I am unable to filter with year ("Année") and amounts ("Appro." "Appropr.16...etc) How can I get this fulfilled.

Herewith screen shots and the sample file I am working on:

Sans titre.jpgSans titre2.jpg

I am really in need for this work to be done by the end of this week and so your help would be very much appreciated.

Best regards,

[Paul_Hossler]

In the code module for frmSearchDatabase

…..


'    ReDim SheetsFound(0)
'    For Each Wks In ActiveWorkbook.Sheets
'        If Not Wks.Name = "Lists" Then
'            SheetsFound(UBound(SheetsFound)) = Wks.Name
'            ReDim Preserve SheetsFound(UBound(SheetsFound) + 1)
'        End If
'    Next Wks
'    ReDim Preserve SheetsFound(UBound(SheetsFound) - 1)
    
    SheetsFound = Array("BASIC", "SPECIFIC", "IMPROVE")
    Me.ComboBox6.List = SheetsFound            '   WorksheetFunction.Transpose(SheetsFound)

[djemy1975]

I will give it a try and bring you my feedback

[djemy1975]

In the code module for frmSearchDatabase

…..


'    ReDim SheetsFound(0)
'    For Each Wks In ActiveWorkbook.Sheets
'        If Not Wks.Name = "Lists" Then
'            SheetsFound(UBound(SheetsFound)) = Wks.Name
'            ReDim Preserve SheetsFound(UBound(SheetsFound) + 1)
'        End If
'    Next Wks
'    ReDim Preserve SheetsFound(UBound(SheetsFound) - 1)
    
    SheetsFound = Array("BASIC", "SPECIFIC", "IMPROVE")
    Me.ComboBox6.List = SheetsFound            '   WorksheetFunction.Transpose(SheetsFound)

The code is working now though I have to add the sheet array line to both the module and the form code.Sorry for being so late as I had some problems in my PC.Thank you for your help.What about the second problem of filtering numbers not working.

[Paul_Hossler]

Try this

2010 is a number on the worksheet and

"=*" & Me.TextBox1.Value & "*" makes it to a string

, which filter doesn't find

you could make 2010 into a string by putting a ' in front of it on the worksheet

            If IsNumeric(Me.TextBox1.Value) Then
                .Range(.Cells(1, 1), .Cells(LR, LC)).AutoFilter Field:=Col, Criteria1:=Me.TextBox1.Value
            Else
                .Range(.Cells(1, 1), .Cells(LR, LC)).AutoFilter Field:=Col, Criteria1:="=*" & Me.TextBox1.Value & "*"
            End If

[djemy1975]

You are wonderful Mr Paul .Your replies are like medecines to injuries.God bless you.It is working charmfully.I will make some development to my file and be back soon if I find any problem.

Have a nice day

[Paul_Hossler]

Glad it worked

Excel filtering has always been a little finicky

You mark the thread [Solved] by using [Thread Tools] above your first post

Open another one if you any issues