?
Solved

Filter data from an userform

Posted on 2011-03-22
15
Medium Priority
?
297 Views
Last Modified: 2012-05-11
Dear EE experts,

I'm trying to figure a way to filter data from an userform. I know that a filter can be added on any column, but what about doing this from an userform ?

Thanks for any help you can provide,

currentdb
0
Comment
Question by:currentdb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35189610
Do you have a sample file?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35189693
Hi Sid,

I'm happy to see you around and yes I have a sample file.
If you go to ''Sheet 3'', I've already added a button ''Filter Acctopid''. This button opens the form ''UserForm 2''

Thanks

userform-sample-v7b.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35189757
Ok What col does Acctopid represent in sheet QUERY_FOR_GSL2?

Sid
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:currentdb
ID: 35189766
It's column O
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35189777
Ok. On to it.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35189791
If you need more explanations, let me know. Thanks.
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35189836
Ok Try this. I added a button to that userform. I entered 03 in the textbox and it worked.

Sid

Code Used

Private Sub CommandButton1_Click()
    On Error GoTo Whoa
    
    If Len(Trim(TextBox1.Text)) = 0 Then
        MsgBox "There is no value to filter"
        Exit Sub
    End If
    
    With Sheets("QUERY_FOR_GSL2").Range("$A$1:$BC$5")
        .Range("$A$1:$BC$5").AutoFilter Field:=15, Criteria1:=TextBox1.Text
        Unload Me
    End With
    Exit Sub
Whoa:
    msgbos Err.Description
End Sub

Open in new window

userform-sample-v7b.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35189849
Oops, a small typo.

Just remove ".Range("$A$1:$BC$5")" from line 9

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35189875
Another small typo is on line 15 (msgbos Err.Description). Changed it to ''MsgBox   :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35189891
Thanks :)

Also one more thing.

In case you are unsure about the number of rows in Sheet "QUERY_FOR_GSL2" then use this code.

Private Sub CommandButton1_Click()
    Dim lastRow As Long
    
    On Error GoTo Whoa
    
    lastRow = Sheets("QUERY_FOR_GSL2").Range("A" & Rows.Count).End(xlUp).Row
    
    If Len(Trim(TextBox1.Text)) = 0 Then
        MsgBox "There is no value to filter"
        Exit Sub
    End If
    
    With Sheets("QUERY_FOR_GSL2")
        .Range("$A$1:$BC$" & lastRow).AutoFilter Field:=15, Criteria1:=TextBox1.Text
        Unload Me
    End With
    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Open in new window


Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35189931
Corrected it :)

If the number of rows change, I'll use the code you provided. For now, it's ok and I hope it will not go further more.

How I can add the other small part of code to go directly to sheet QUERY_FOR_GSL2 ? If I enter the Acctopid id number on Sheet 3, then click on the button, can it show the sheet QUERY_FOR_GSL2 without having to click on it ?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35189946
Before this line

Exit Sub

Type this

Sheets("QUERY_FOR_GSL2").Activate

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190070
It works perfectly! Thanks!

I'm going to open a new question on the same sample file I sumbitted earlier, but a little more complex.

Hold on as I open the question and post the link here, then close the actual question and award you full points.
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190123
0
 
LVL 1

Author Closing Comment

by:currentdb
ID: 35190367
Thanks again so much for your fast help :)
0

Featured Post

Independent Software Vendors: 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

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.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

765 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