98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » IF Statement when choosing from pivot table drop down menu
IF Statement when choosing from pivot table drop down menu
Resolved · High Priority · Version 2003
Wed 9 Nov 2011, 09:57Delegate Tony said...
Tony has attended:
Excel VBA Advanced course
IF Statement when choosing from pivot table drop down menu
I have the following data in my macro
Sheets("Midas Pivot").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Actual Company Name"). _
CurrentPage = MyClient
where MyClient is a variable client name
I want the pivot table to show no data if MyClient is not shown in the drop down menu.
Is there an IF statement I can use around the above code or is there an easier way to make the pivot table show no data.
Thanks in advance
Tony
Thu 10 Nov 2011, 11:45Trainer Anthony said...
RE: IF Statement when choosing from pivot table drop down menu
Hi Tony, thanks for your query. There's no out of the box function to do this for you, but try coding the creation of the pivot, then the addition of the Page field, and then bracket everything after that in an If expression to test what the user chooses. You may even want to use a second subroutine - the Worksheet_Change event - to test what has been picked from the Page field and build the rest of the pivot accordingly.
Hope this helps,
Anthony
Thu 10 Nov 2011, 12:06Delegate Tony said...
RE: IF Statement when choosing from pivot table drop down menu
Thanks for your reply Anthony
Unfortunately, I have to use the pivot table produced. I cannot recreate the pivot.
However there may be a work round. There is a "(blank)" option in the drop down menu at the bottom of the client names. (This is because the pivot range data is for all rows ie row1 to row65536)
Is there a piece of code I can use which allows me to choose "(blank)" from the drop down if the MyClient does not exist.
Thanks
Tony
Thu 10 Nov 2011, 12:32Trainer Anthony said...
RE: IF Statement when choosing from pivot table drop down menu
Yes, again I'd attach that to the Worksheet_Change event. Use an IF expression to test the value of MyClient and if MyClient doesn't exist to force it to replace it with (blank). It's difficult to give you bespoke code without seeing the actual Pivot, but that's the logic I'd try.
Anthony
Thu 10 Nov 2011, 14:45Delegate Tony said...
RE: IF Statement when choosing from pivot table drop down menu
Thanks again Anthony,
I am really struggling with this. I attach the code below. It changes the client if it is available in the drop down menu otherwise I get an error.
How can I apply the IF statement around the code so that it chooses "(blank)" if the client does not exist in the drop down menu. Do I still need the Worksheet_Change Event (I am still not proficient in this area).
I realise you are extremely busy but any help would be much appreciated.
Thanks
Tony
MyClient = Sheets("Macro").Range("C16")
Sheets("TST pivot").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Actual Company Name"). _
CurrentPage = MyClient
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Hide data in Excel WorksheetsLet's say you have some data in cell 'C5' you would like to hide from the casual viewer. |
