Dynamic Macro Name?

[smitpau]

Hi,

I'm not sure this is possible but thought it would be worth asking.

I have the following code below, which hides a tab based on a cell.

Ideally I'd like a macro button to say "Hide Tab" when it's unhidden and "Unhide Tab" when it's hidden.

Thanks for reading, any suggestions welcome.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If [G2] = "Unhide" Then
Sheets("Sheet 1").Visible = True
Else
Sheets("Sheet 1").Visible = xlSheetVeryHidden
End If
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Insert a forms control button, or shape & assign this macro to it.

Sub smitpau()
   With Sheets("Sheet1")
      ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text = IIf(.Visible = -1, "Unhide Tab", "Hide Tab")
      .Visible = IIf(.Visible = -1, 2, -1)
   End With
End Sub

 

[smitpau]

Okay that looks promising to be honest I simplified the code a little bit above it really hides about 4 tabs for the same cell reference, also probably doesn't need saying but the reference cell is in another tab to the ones hidden.

How would that change your code, would I just add the other tabs after Sheet 1?

 

[Fluff]

That is a totally different matter. You should NEVER simplify your problem, unless you want something that won't work.
Please explain exactly what you want to happen when the button is clicked.

 

[smitpau]

When the button is clicked it will hide 4 tabs when it is clicked again it will unhide 4 tabs, the name of the button will change to match the situation e.g. "Unhide tabs" will be the name when the tabs are hidden and vice versa.

Hope that explains it clearly.

Thanks.

 

[Fluff]

That's fine, what are the names of the tabs?

 

[smitpau]

The tabs names are:

"Outputs Variance>>"
"P&L Var."
"BS Var."
"CF Var."

 

[Fluff]

Ok, how about

Sub smitpau()
   Dim Ary As Variant
   Dim Vis As Long, i As Long
   
   Ary = Array("Outputs Variance>>", "P&L Var.", "BS Var.", "CF Var.")
   With ActiveSheet.Shapes(Application.Caller).TextFrame.Characters
      If .Text = "Hide Tab" Then
         .Text = "Unhide Tab"
         Vis = 2
      Else
         .Text = "Hide Tab"
         Vis = -1
      End If
      For i = 0 To UBound(Ary)
         Sheets(Ary(i)).Visible = Vis
      Next i
    .Parent.Parent.Activate
   End With
End Sub

 

[smitpau]

Thanks that works a treat just out of interest would that still work with an ActiveX button?

 

[Fluff]

It would have to be rewritten, but I see no reason why it couldn't be done.