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
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