Dynamic Macro Name?

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
167
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
 
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.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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".
 
Upvote 0
Untested, but change the with statement to
VBA Code:
   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.
 
Upvote 0
That's not far off what do you mean by change text to Caption sorry?
 
Upvote 0
Perfect I'll finally leave you in peace!

Thanks again, very helpful & useful.
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,934
Messages
6,175,491
Members
452,649
Latest member
mr_bhavesh

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
Back
Top