Solved

VB Help

Posted on 2011-03-13
1
177 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now