Solved

Need another slicer

Posted on 2016-10-31
24
67 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
  • 11
  • 11
24 Comments
 
LVL 50

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 50

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

Accepted Solution

by:
Rgonzo1971 earned 500 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 50

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Being an active EE Expert means to get a lot of (E)EMail, as you certainly know. If you are using Outlook, I'll show you how to minimize your inbox contents without losing anything – even improve the experience by changing the Subject line to facili…
This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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