Solved

VB Help

Posted on 2011-03-13
1
179 Views
Last Modified: 2012-05-11
Please can you help me convert my spreadsheet?

Currently the text box filters out anything that does not meet the criteria entered into textbox1

I want to include the dropdown box in B3 so that depending on what is selected the code will either work for columns B: K or just the column selected.

search-range-filter.xls
0
Comment
Question by:Gazza83
1 Comment
 
LVL 8

Accepted Solution

by:
wchh earned 500 total points
ID: 35125644
Try the code below...
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim Column As Range
Dim mFilter As Integer
With TextBox1.Parent
    .AutoFilterMode = False
    If Len(TextBox1.Value) > 0 Then
        .[B6:K20].AutoFilter
        For Each Column In .AutoFilter.Range.Columns
           .AutoFilter.Range.Cells(1, 1).AutoFilter Field:=Column.Column - .AutoFilter.Range.Column + 1, Visibledropdown:=False
        Next Column
        mFilter = 1
        Select Case ActiveSheet.Cells(3, 2).Value
            Case "B"
                mFilter = 1
            Case "C"
                mFilter = 2
            Case "D"
                mFilter = 3
            Case "E"
                mFilter = 4
            Case "F"
                mFilter = 5
            Case "G"
                mFilter = 6
            Case "H"
                mFilter = 7
            Case "I"
                mFilter = 8
            Case "J"
                mFilter = 9
            Case "K"
                mFilter = 10
        End Select
        .[B6:K20].AutoFilter Field:=mFilter, Criteria1:="=*" & TextBox1.Value & "*", Operator:=xlAnd
    End If
End With
End Sub

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question