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

 

[smitpau]

Only reason I ask is because I'd like the button to be a certain colour which matches the ActiveX buttons currently setup, I'm currently looking at assigning the macro to a shape but need to make the shape look like a macro button, so that may be an easier option.

 

[Fluff]

I never user ActiveX controls unless absolutely necessary, as they are notoriously flaky. I also use shapes (normally rounded rectangle) because I find buttons look ugly & "clunky".

 

[Fluff]

Untested, but change the with statement to

   With Me.CommandButton1

or whatever you button is called & then change text to Caption.
If that doesn't work, it should get you a bit closer.

 

[smitpau]

That's not far off what do you mean by change text to Caption sorry?

 

[smitpau]

Sorry get it now!

 

[smitpau]

Perfect I'll finally leave you in peace!

Thanks again, very helpful & useful.

 

[Fluff]

Glad to help & thanks for the feedback.