?
Solved

Creating user text input to select filter using slicers?

Posted on 2011-10-14
19
Medium Priority
?
1,177 Views
Last Modified: 2012-05-12
Hello EE!

I have a sheet that contain a few pivot tables, and several other sheets with slicers to change all these pivots. The pivot tables are all controlled by the same set of slicers.

However, one of the slicers filter on names of people in a database. And since there are near 10.000 names in the database, this becomes very unpractical to look through if you want to filter a subset of names.

Is there anyway to construct a userform, a textbox, above the slicer that you can enter the name in, and it will automaticly set the slicer to that name, or would I need to look for other solutions?

Thank you in advance!
0
Comment
Question by:ThomasFoege
  • 10
  • 4
  • 2
  • +2
18 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 36968060
Why use a form and a textbox ? you can simply add the textbox onto the sheet that you want above the slicer. and in the LoastFocus method of the textbox you invoke the slicer giving it the value of the textbox something like this

Private Sub TextBox1_LostFocus()
Slicer.Text = TextBox1.Text
End Sub

don't know exactly how you call your slicer but we could refine it with your comment.
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36968066
An other question when you say: Slicer you mean Combobox ?
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36968076
Sorry I should throw all my remarks in one thread, Do you have a possibility to post your workbook or just a sample ?
gowflow
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:ThomasFoege
ID: 36968092
I inserted that code into the sheet, named my textbox "TextBox1" and my slicer for "Slicer"
Did I follow your suggestion correctly, I'm not sure about the lost focus?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36968166
ok when you insert the textbox1 as a control on hte sheet doubleclick on textbox1 you will be displayed the code for Textbox1 in the Change Event something like

Sub TextBox1_Change()

End Sub

In the right combo on top of this window click on it and go thru the list you will find the LostFocus event click on it and you will have this

Private Sub TextBox1_LostFocus()
End Sub


insert
Slicer.Text = TextBox1.Text
like I proposed after the sub. Save and start again your workbook and enable macro is the is the only line of code you need to set macro security to medium and when prompt enable macros.

gowflow
0
 

Author Comment

by:ThomasFoege
ID: 36968202
How do I insert the textbox as a control? I inserted it by using the "insert" part on the top ribbon menu
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36968249
oops !!! can you post your workbook ? Seems your using Excel 2007 ?
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36968271
You still did not answer:
An other question when you say: Slicer you mean Combobox ?
gowflow
0
 

Author Comment

by:ThomasFoege
ID: 36968296
Oh I missed that. I think I mean slicer? The pivot slicers you can insert, I'm not sure what a combobox is
I'm using excel 2010

I've pasted an example of what I'm trying to do

Thanks again for the help!
SlicerLookupExample.xlsx
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36968341
Sorry I do not have Excel 2010 and don't know if its availl in Excel 2007 ? Can you saveas this workbook in Excel 2007 version so I choeck it out ? I was able to open it but the square slicer gave me a description .... You created the name by using insert but its diffrent we should use VBA but I should know what the slicer is order provide accurate solution.
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36968442
I just googled up info from Excel 2010 and slicers are ways to filter data in a pivot table I guess your problem is when you create the slicer for that specific sheet for the field name it return 10000 names and you need to be able to type in the name and the slicer to look for it instead of looking over 10000 records to find your name and clicking on it. correct ?
If this is correct then to draft the code for you I will need to know the slicer name on this sheet. click on the clicer and it should display its name beside the formula bar something like Slicer1 ... pls give it to me.
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36968476
Sorry Thomas I have to run for now I will re-connect later on. Don't loose hope we will get it (even though I don't hv Excel 2010 and its a total new feature, but we will get it !!!)
gowflow
0
 

Author Comment

by:ThomasFoege
ID: 36968624
I dont have the option to save the file in a 2007 format, only 07-2003?

Do you think it would be possible to filter directly into the pivot instead of the slicer?

No worries, thanks for the help so far!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36969647
well you haven't answer my question
If this is correct then to draft the code for you I will need to know the slicer name on this sheet. click on the clicer and it should display its name beside the formula bar something like Slicer1 ... pls give it to me.

gowflow
0
 
LVL 11

Accepted Solution

by:
ScriptAddict earned 2000 total points
ID: 36969712
I don't know if this is going to give you the polished look you want, but I just use the pivot table drop down.  

You can type a fraction of the name in the search that drops with that, and then it would restrict the pivot table to show only those names that match your search.

I'm sure there is a way to programatically call that same functionality
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36969731
BTW: Same functionality appears on your chart if you click on the name drop down.  It looks a little better there.  Just click on that and type oe in the search box, and then watch joe pop up.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36978670
Not for points:

Saving as 2007 won't help since the Slicer will be gone. ;)

I agree with ScriptAddict - you'd be far better off using the search in the dropdown; a slicer serves no purpose, to my mind, in a field with 10000 items.

Regards,
Rory
0
 
LVL 50
ID: 37419360
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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

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.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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.

807 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