Hiding and Unhiding Columns with Shapes

[JoRyBar]

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

 

[Yongle]

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]

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

Thanks so much!

Best,

VBA Novice

 

[Fluff]

You can use just 5 shapes (I've named them as your "On" buttons). Assign each of them to this macro

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]

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]

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]

Hi,

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

Thanks!

 

[Fluff]

I'm not sure what is causing the problem, as I cannot replicate the error.
Would you be able to share your workbook?

 

[Yongle]

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