View Full Version : [SOLVED:] Include 3 sheets only in filtering
djemy1975
11-13-2018, 01:36 AM
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:
2319223193
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
11-13-2018, 03:20 PM
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
11-13-2018, 03:51 PM
I will give it a try and bring you my feedback
djemy1975
11-13-2018, 04:31 PM
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
11-13-2018, 07:23 PM
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
11-14-2018, 12:58 AM
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
11-14-2018, 09:04 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.