Excel Hide/unhide textbox based on Slicer selection

Question

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

"doesn't work" is not a very useful description of what happens when you run your code. The one thing that stands out is your `If Then` is not structured correctly: you have 3 `If` and only one `End If` https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-ifthenelse-statements — Tim Williams, Jan 13 '21 at 18:03

Tim Williams · Answer 1 · 2021-01-19T06:14:18.363

0

Try something like this:

Sub Changetextbox()
    
    Dim si As SlicerItems, ws As Worksheet
    
    Set ws = ActiveSheet
    Set si = ActiveWorkbook.SlicerCaches("Slicer_ColA").SlicerItems ' edit to your slicer name
    
    ws.Shapes("TextBox 21").Visible = si("UK").Selected
    ws.Shapes("TextBox 22").Visible = si("FR").Selected
    ws.Shapes("TextBox 23").Visible = si("DE").Selected
    
End Sub

Edit: you might need to check the names of your slicer caches:

For Each sc In ActiveWorkbook.SlicerCaches
    Debug.Print sc.Name
Next sc

edited Jan 19 '21 at 06:14

answered Jan 13 '21 at 18:18

Tim Williams

154,628
8
97
125

I tried using this as follows: but i got the following error: "object doesn't support this property or method" relating to this row: Set si = ActiveSheet.SlicerCaches("apslicer").SlicerItems ' edit to your slicer name – JezVanderBrown Jan 15 '21 at 13:06
`ActiveWorkbook` not `ActiveSheet` – Tim Williams Jan 15 '21 at 16:04
Thanks for helping with this. I now have `Run-Time Error '5': invalid procedure call or argument` with the same row `Set si = ActiveWorkbook.SlicerCaches("apslicer").SlicerItems` – JezVanderBrown Jan 18 '21 at 10:35
I think your slicer cache name is wrong - see edit above – Tim Williams Jan 19 '21 at 06:15

Excel Hide/unhide textbox based on Slicer selection

1 Answers1