VBA Hiding and displaying slicers

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136
I am trying provide the ability to hide and show slicers using a check box. And for some reason it not working? Not very good, no I am horrible in VBA, so the question may be a dumb one. Here is what I have:

Code:
Sub HideShowObjects()  If ActiveSheet.Range("AF1").Value Then
       ActiveSheet.Shapes("Region").Visible = True
       ActiveSheet.Shapes("2014 Carryover").Visible = True
       ActiveSheet.Shapes("Capitalized 2015 USD").Visible = True
       ActiveSheet.Shapes("Sub Function 1").Visible = True
  Else
       ActiveSheet.Shapes("Region").Visible = False
       ActiveSheet.Shapes("2014 Carryover").Visible = False
       ActiveSheet.Shapes("Capitalized 2015 USD").Visible = False
       ActiveSheet.Shapes("Sub Function 1").Visible = False
       
  End If
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Reggie74

Board Regular
Joined
Sep 26, 2014
Messages
51
The BIG question i have is .... What is in Range("AF1") ?? :confused:


I am trying provide the ability to hide and show slicers using a check box. And for some reason it not working? Not very good, no I am horrible in VBA, so the question may be a dumb one. Here is what I have:

Code:
Sub HideShowObjects()  If ActiveSheet.Range("AF1").Value Then
       ActiveSheet.Shapes("Region").Visible = True
       ActiveSheet.Shapes("2014 Carryover").Visible = True
       ActiveSheet.Shapes("Capitalized 2015 USD").Visible = True
       ActiveSheet.Shapes("Sub Function 1").Visible = True
  Else
       ActiveSheet.Shapes("Region").Visible = False
       ActiveSheet.Shapes("2014 Carryover").Visible = False
       ActiveSheet.Shapes("Capitalized 2015 USD").Visible = False
       ActiveSheet.Shapes("Sub Function 1").Visible = False
       
  End If
End Sub
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136
Sorry Reggie,

In AF1 I have it showing True/False based on a "Check Box" that returns True and False. So checking it (True) on it should hide shapes, and Un-checking return shapes.
 

Reggie74

Board Regular
Joined
Sep 26, 2014
Messages
51
Try this ... ??

Code:
Sub HideShowObjects()
  If ActiveSheet.Range("AF1").Value = "False" Then

Or perhaps this ... ??

Code:
Sub HideShowObjects()
  If ActiveSheet.Range("AF1").Value = False Then


Sorry Reggie,

In AF1 I have it showing True/False based on a "Check Box" that returns True and False. So checking it (True) on it should hide shapes, and Un-checking return shapes.
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136

ADVERTISEMENT

This is weird. Those two choices didn't work either. It seems the code is not reacting to the checking of the box. I don't understand why, as I have used this same code in the past with a checkbox. I've review everything I can think of and just don't see any reason for it not to work.
 

Reggie74

Board Regular
Joined
Sep 26, 2014
Messages
51
What about ? ....

Code:
Sub HideShowObjects()
  If ActiveSheet.Range("AF1").Value = 0 Then

This is weird. Those two choices didn't work either. It seems the code is not reacting to the checking of the box. I don't understand why, as I have used this same code in the past with a checkbox. I've review everything I can think of and just don't see any reason for it not to work.
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136
I have the file I used to use and it still works that's what is puzzling me. It is as if the code is not seeing or reacting to the check box and the true/false it enters in the cell. I've checked the cell many times to make sure the actual cell and the one in the code are the same. I can hit F5 in the code and it works. The problem is I want the end user to simply hit the check box and the designated boxes disappear and come back when checked. He does not use excel much so I need to keep it simple. I cannot for the life of me figure this out. I have a feeling it is something really minor, but who knows.
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136
I figured it out. After reviewing my work, I realized I never right clicked on the check box and assigned it to the macro. Feelin' a bit dumb.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,629
Messages
5,838,454
Members
430,549
Latest member
jayjay2022

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