?
Solved

Need another slicer

Posted on 2016-10-31
24
Medium Priority
?
79 Views
Last Modified: 2016-11-06
I had this question after viewing How can I use Excel VBA to trigger an event using Slicers?.

I just need to add an additional slicer to the Project Schedule Page. I need to add a 2nd Slicer to my Project Schedule Sheet which is called "Fiscal Year". The selections are all the years from 2007-2024.  Can we amend the macro to include that slicer? In other words, when the workbook is open, the user can either select Country or Fiscal Year or both and the result would open the "Summary" worksheet.
2nd-Dummy-FileV2--2-.xlsm
0
Comment
Question by:angelfromabove
[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
  • 11
  • 11
24 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41867966
Hi,

pls try in the Summary Sheet module
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Set SlicC = Nothing
Set SlicC = Target.Slicers("Country")
Set SlicFY = Nothing
Set SlicFY = Target.Slicers("Fiscal Year")
If SlicC.SlicerCache.VisibleSlicerItems.Count = 1 And SlicFY.SlicerCache.VisibleSlicerItems.Count = 1 Then
    Sheets("Summary").Activate
End If
End Sub

Open in new window

and in the Project Schedule module
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each oSlicerCache In ThisWorkbook.SlicerCaches
    For Each slicItem In oSlicerCache.SlicerItems
        slicItem.Selected = True
    Next
Next
ActiveSheet.Shapes.Range("Fiscal Year").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
0
 

Author Comment

by:angelfromabove
ID: 41868303
Now it's no longer working when I select just the country.  It only works when I first select the Fiscal Year, then the Country. I need to be able to select either Country or Fiscal Year or Both and the Summary Sheet is activated.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41868305
So you need another button (Go!) if not it won't know what you are looking for

Is what you would like?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41868320
0
 

Author Comment

by:angelfromabove
ID: 41868323
I'm not sure what you mean, but I would like the Users to be able to select the Country with or without selecting a Fiscal Year and similarly, I would like the Users to be able to select a Fiscal Year, with or without a country. For example, if they select just "US", without selecting a Fiscal Year, all Fiscal years will appear, or if they just select "2016" without selecting a Country, both"CAN" and "US" values would appear.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41868327
I mean that it cannot be automatic since the macro cannot know if the person wants to change Country and FY or only one of them
0
 

Author Comment

by:angelfromabove
ID: 41868333
Please don't delete this one, this is the one that's being answered properly.  We are close to a solution.
0
 

Author Comment

by:angelfromabove
ID: 41868337
Rgonzo 1971- If they click on Country, it should change Country.  If they click on Fiscal Year, it should change Fiscal Year. Either should trigger an event to move to the Summary Page If they click both, it should still move to the Summary Page.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41868340
What I mean if you click Country it goes to the summary before you can even try to change the year is that what you are looking for?
0
 

Author Comment

by:angelfromabove
ID: 41868346
Yes! Also, if you click Fiscal Year, it goes to the summary before you can even try to change the Country.  Lastly, if you click both, (Country followed by Fiscal Year or Fiscal Year followed by Country) it should still go to Summary, which is the way you have it now.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41868347
0
 

Author Comment

by:angelfromabove
ID: 41868372
That works!  Thanks, let me check with my colleagues to see which version would work best.  I see what you mean now, it's a challenge to do both Country and Fiscal Year at the same time.  Can you post the code from the dummy file you sent, so I can see it outside of the Workbook just to make sure I didn't mix anything up?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41868376
V4
in Project Schedule module
Private Sub Worksheet_Activate()
ActiveSheet.Shapes.Range("Country").Select
End Sub

Open in new window

in Summary code module
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
     Sheets("Summary").Activate
End Sub

Open in new window

0
 

Author Comment

by:angelfromabove
ID: 41868880
Sorry for the delay.  I spoke with my colleagues and Executives.  Both versions are great, but ultimately we would need a hybrid of the two versions, which I probably wasn't able to articulate well.  Let me give you a few examples to contrast and then #3 will explain what I really need:
1) User wants to Select US and Fiscal Year 2017, Summary Page Activates - we're good, a prior version you gave me covers that.  
2)  User wants to Select US only with selecting a Fiscal Year, or maybe Fiscal Year 2017 only, without selecting a Country, - we're good there as well, the most recent version you provided covers that.
3) User wants to Select both US and Canada and Fiscal Years, 2016, 2017 and 2018 all at once.  - This is what I really need and where it's probably more challenging. Do we need to add different button controls not related to the slicer?  I don't know the solution, perhaps a button that says, select Country or All, then select Fiscal Year or Multiple Fiscal Years?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41868895
0
 

Author Comment

by:angelfromabove
ID: 41868984
Are you able to assist with that? I have no idea how to do that with my data.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41869642
You mean you don't know how to insert a button and attach a macro to it?
0
 

Author Comment

by:angelfromabove
ID: 41872968
Sorry, I do not. If you could please provide assistance based on what I am trying to accomplish, I would greatly appreciate it.
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41876119
then try with


https://support.office.com/en-us/article/Add-a-button-and-assign-a-macro-to-it-in-a-worksheet-adec9584-3925-47c9-998f-def71ff6a4a3?omkt=en-US&ui=en-US&rs=en-US&ad=US

and use this code
Sub Button1_Click()
Sheets("Summary").Activate
End Sub

Open in new window

0
 

Author Comment

by:angelfromabove
ID: 41876184
Thank you, I have a few skills, but not an expert by any means, so I can insert a button, but can you please provide detailed instructions as to how this could work with my slicers and what I want to accomplish based on this thread? By now, you get what I'm trying to accomplish, butI'm not sure how to tie these ideas together.  Thanks!
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41876316
the slicers change the pivot then macro just go to the summary sheet like in version 3
0
 

Author Closing Comment

by:angelfromabove
ID: 41876475
Perfect, I got it and it works! I will be an Expert soon! Thank you again for all of your patience!
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month10 days, 17 hours left to enroll

770 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