IF Statement when choosing from pivot table drop down menu

Resolved · High Priority · Version 2003

Wed 9 Nov 2011, 09:57 Delegate 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:45 Trainer 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:06 Delegate 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:32 Trainer 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:45 Delegate 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Hide data in Excel Worksheets

Let's say you have some data in cell 'C5' you would like to hide from the casual viewer.

Click cell 'C5' to select it.

Click the 'Format' menu, select 'Cells'. When the 'Format Cells' dialogue box opens, click the 'Numbers' tab (if necessary), then select 'Custom' from the 'Category' list.

Double-click the 'Type' entry box and type three semi-colons: ";;;"

Click 'OK' to close the dialogue box and accept the new formatting.

The data in cell 'C5' disappears. It's still there and will work in calculations, but it isn't visible.

If you need to check the data, just click the blank cell and the contents appear in the 'Formula bar'.

View all Excel hints and tips