[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB Help

Posted on 2011-03-13
1
Medium Priority
?
189 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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