VBA Hiding and displaying slicers

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
137
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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

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

Watch MrExcel Video

Forum statistics

Threads
1,109,428
Messages
5,528,698
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top