Hide/unhide textbox based on Slicer selection

JezJamesBrown

New Member
Joined
Oct 25, 2016
Messages
5
Hi everyone,

I'm trying to hide/unhide multiple text boxes depending on what is selected on a pivot table slicer;

IF UK is selected then show TextBox 21 but hide TextBox 22 and TextBox 23
IF DE is selected then show TextBox 22 but hide TextBox 21 and TextBox 23
IF FR is selected then show TextBox 23 but hide TextBox 21 and TextBox 22

I tried recording a Macro to get the naming conventions of the objects, and then wrote the below VBA code, except it doesn't work. Can anybody tell me where I'm going wrong (I'm using Excel 2019);

Sub Changetextbox()
If ActiveWorkbook.SlicerItems("UK").Selected = True Then
ActiveSheet.Shapes.Range(Array("TextBox 21")).Visible = msoTrue
ActiveSheet.Shapes.Range(Array("TextBox 22")).Visible = msoFalse
ActiveSheet.Shapes.Range(Array("TextBox 23")).Visible = msoFalse
If ActiveWorkbook.SlicerItems("DE").Selected = True Then
ActiveSheet.Shapes.Range(Array("TextBox 21")).Visible = msoFalse
ActiveSheet.Shapes.Range(Array("TextBox 22")).Visible = msoTrue
ActiveSheet.Shapes.Range(Array("TextBox 23")).Visible = msoFalse
If ActiveWorkbook.SlicerItems("FR").Selected = True Then
ActiveSheet.Shapes.Range(Array("TextBox 21")).Visible = msoFalse
ActiveSheet.Shapes.Range(Array("TextBox 22")).Visible = msoFalse
ActiveSheet.Shapes.Range(Array("TextBox 23")).Visible = msoTrue
End If
End Sub


Any help would be much appreciated.
Thanks
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,127,776
Messages
5,626,816
Members
416,202
Latest member
donya ba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top