Hiding and Unhiding Columns with Shapes

JoRyBar

New Member
Joined
Aug 19, 2018
Messages
17
Hi Gurus,

I am having an issue trying to hide/unhide columns using shapes (not command buttons). I would like to do the following:

1) have the shapes show a "ON" color and "OFF" color (which I have tried to do by overlapping shapes on top of each other
2) when a certain shape is "ON", the columns will hide all other columns except for the ones I have indicated

I don't know if using command buttons will make this easier... The VBA code I am having issues is:

Sub TabPro()
With Sheet1
.Shapes("ProON").Visible = msoCTrue
.Shapes("ProOFF").Visible = msoCFalse

.Shapes("ProInfON").Visible = msoCFalse
.Shapes("ProInfOFF").Visible = msoCTrue

.Shapes("QON").Visible = msoCFalse
.Shapes("QOFF").Visible = msoCTrue

.Shapes("ListON").Visible = msoCFalse
.Shapes("ListOFF").Visible = msoCTrue

.Shapes("VON").Visible = msoCFalse
.Shapes("VOFF").Visible = msoCTrue

.Range("b:G").EntireColumn.Hidden = False
.Range("H:AG").EntireColumn.Hidden = True


End With
End Sub

This is code for only the first set of buttons out of 4. Once correct, I should be able to repeat for the other 3 with minor corrections. I have gotten a few errors when I have tried to test run the above.

I would also ask if you would recommend not using shapes with macro and instead using command buttons. As long as it works, I am open to any suggestions.

Thank you for your assistance.

Best,

VBA Novice
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Can you explain exactly what you are trying to achieve?

You want to use various shapes to hide columns
- does each shape hide a different range of columns?

You are wanting to make certain shapes invisible
- are the shapes inside columns that may be hidden?
- what determines if a shape should be rendered visible?
- what determines if a shape should be rendered hidden?
 

JoRyBar

New Member
Joined
Aug 19, 2018
Messages
17
Hi Yongle,

Thank you for responding. Apologies for the confusing request.

I have 5 horizontal tabs using the shape rectangle (top corners rounded). These are the "ON" shapes and they are in yellow. On top of these rectangles are another set of identical rectangles (top corners rounded) and they are in light grey. These are the "OFF" shapes. Every time someone clicks on one of the tabs, it should turn yellow and the other 4 tabs should be grey, meaning they are "OFF". And the associated columns for the "ON" tab should be the only ones displaying.

So for example:
1) When the 1st shape/tab "ProON" is selected, all the other shapes (ProOFF, ProInfON, ProInfOFF...etc) should be in grey, and only the columns associated with "ProON" (columns B:G) should display. All other columns should be hidden (columns H:AG).
2) If another shape/tab is selected (for example "ProInfON"), then this should be the only thing in yellow and all other shapes should be grey. The only columns that should display is (I:L) and all others (B:G) and (M:AG) should be hidden.

I hope this makes sense....:eek:

Thanks so much!

Best,

VBA Novice
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,012
Office Version
  1. 365
Platform
  1. Windows
You can use just 5 shapes (I've named them as your "On" buttons). Assign each of them to this macro
Code:
Sub UseShapes()
   Dim Nme As String
   Dim Shp As Shape
   
   Nme = Application.Caller
   For Each Shp In ActiveSheet.Shapes
      Shp.Fill.ForeColor.RGB = IIf(Shp.Name = Nme, vbYellow, 12566463)
      Shp.TextFrame.Characters.Text = IIf(Shp.Name = Nme, "On", "Off")
   Next Shp
   Range("B:AG").EntireColumn.Hidden = True
   Select Case Nme
      Case "ProON"
         Range("B:G").EntireColumn.Hidden = False
      Case "ProInfON"
         Range("I:L").EntireColumn.Hidden = False
      Case "QON"
      
      Case "ListON"
      
      Case "VON"
      
   End Select
End Sub
Just add the columns to be visible
 

JoRyBar

New Member
Joined
Aug 19, 2018
Messages
17

ADVERTISEMENT

Hi Fluffy,

Thank you for taking the time to respond to my request. Unfortunately, I get type mismatch, error 13 when I try to run this code. This is what I currently have:

Sub UseShapes()
Dim Nme As String
Dim Shp As Shape

Nme = Application.Caller
For Each Shp In ActiveSheet.Shapes
Shp.Fill.ForeColor.RGB = IIf(Shp.Name = Nme, vbYellow, 12566463)
Shp.TextFrame.Characters.Text = IIf(Shp.Name = Nme, "ON", "OFF")
Next Shp
Range("a:Ah").EntireColumn.Hidden = True
Select Case Nme
Case "****"
Range("B:F").EntireColumn.Hidden = True
Case "PropION"
Range("G:J").EntireColumn.Hidden = False
Case "QON"
Range("K:M").EntireColumn.Hidden = False
Case "AON"
Range("K:R").EntireColumn.Hidden = False
Case "ReqON"
Range("S:V").EntireColumn.Hidden = False
Case "VON"
Range("W:AB").EntireColumn.Hidden = False
Case "ImON"
Range("AC:AH").EntireColumn.Hidden = False
End Select
End Sub

Based on your previous post, I deleted the OFF shapes and added the columns for the rest of the shapes. I am not sure where I messed up. Also, would you be able to provide further details on code in red. Does this mean that the text in the shape should say On or Off? Since we only have one button On, is this what is messing things up? Please advise.

Thanks!
Novice
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,012
Office Version
  1. 365
Platform
  1. Windows
When you click debug, what line of code is highlighted?
Also do you have any other shapes, graphs, buttons on the sheet?
The part in red does indeed change the text in the shape, if you don't want that to happen, simply delete that line.
 

JoRyBar

New Member
Joined
Aug 19, 2018
Messages
17

ADVERTISEMENT

Hi,

It was this portion that was highlighted Nme = Application.Caller with a 2023 error code. Not sure if that helps...

Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,012
Office Version
  1. 365
Platform
  1. Windows
I'm not sure what is causing the problem, as I cannot replicate the error.
Would you be able to share your workbook?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
@Fluff
I have tested your code - it works without any problem for me

@JoRyBar
I think there is something else going on in your worksheet -perhaps old code that should be deleted
- add a NEW worksheet
- create 5 shapes and name them ProON, ProInfON, QON, ListON, VON
- move Fluff's macro to a general module
- assign each shape to Fluff's macro

Does it work
- if it does what is different?
 
Last edited:

Forum statistics

Threads
1,148,294
Messages
5,745,942
Members
423,985
Latest member
sayed manzar

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