Solved

Need another slicer

Posted on 2016-10-31
24
45 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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Expert Comment

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

Is what you would like?
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
0
 

Author Comment

by:angelfromabove
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:angelfromabove
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
0
 

Author Comment

by:angelfromabove
Comment Utility
Are you able to assist with that? I have no idea how to do that with my data.
0
 
LVL 48

Expert Comment

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

Author Comment

by:angelfromabove
Comment Utility
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 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
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
Comment Utility
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 48

Expert Comment

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

Author Closing Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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