Dynamic Macro Name?

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
149
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,629
Office Version
  1. 365
Platform
  1. Windows
Insert a forms control button, or shape & assign this macro to it.
VBA Code:
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

Board Regular
Joined
Apr 2, 2020
Messages
149
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,629
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 2, 2020
Messages
149
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,629
Office Version
  1. 365
Platform
  1. Windows
That's fine, what are the names of the tabs?
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
149
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The tabs names are:

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,629
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

Board Regular
Joined
Apr 2, 2020
Messages
149
Office Version
  1. 365
Platform
  1. Windows
Thanks that works a treat just out of interest would that still work with an ActiveX button?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,629
Office Version
  1. 365
Platform
  1. Windows
It would have to be rewritten, but I see no reason why it couldn't be done.
 

Forum statistics

Threads
1,143,658
Messages
5,720,128
Members
422,267
Latest member
olund

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