Change the Caption on a button..............

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926
Hi,
I have a macro assigned to a button that unhides the columns F:K.
The Caption on the Button is "Display Details". Is there a way to change the caption to "Hide Details" if the Columns F:K are shown and assign a macro that hides these columns to the same button.
ie, if the Columns F:K are hidden, then the caption must be "Display Deatils" and if the Columns F:K are shown, then the caption must be "Hide Details"
Hope it is clear
TIA
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I created a button from the Forms toolbar, selected New when the Assign Macro dialog appeared and then used this code.
Code:
Sub Button1_Click()
Dim o
Dim bHide As Boolean

    Set o = ActiveSheet.Shapes(Application.Caller)
    
    bHide = o.OLEFormat.Object.Caption = "Hide Details"

    ActiveSheet.Columns("F:K").Hidden = bHide
    
    If bHide Then
        o.OLEFormat.Object.Caption = "View Details"
    Else
        o.OLEFormat.Object.Caption = "Hide Details"
    End If
    
End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

try this
Code:
Sub test()
'Erik Van Geit
'090620
Dim rng As Range
Dim txt As String

Set rng = Columns("F:K")

rng.Hidden = Not rng.Hidden
txt = IIf(rng.Hidden, "Display", "Hide") & " Details"
ActiveSheet.Shapes("Button 1").OLEFormat.Object.Characters.Text = txt

End Sub
change buttonname to what you have or use application.caller
you used a formstoolbar-button, I think
for a controlstoolbar-button the code can be almost the same
just one line to change
Code:
CommandButton1.Caption = txt
kind regards,
Erik
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
How's this all in one Command Button:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#007F00">'   Toggle Columns</SPAN>
    Columns("F:K").EntireColumn.Hidden = <SPAN style="color:#00007F">Not</SPAN> Columns("F:K").EntireColumn.Hidden
    <SPAN style="color:#00007F">If</SPAN> Columns("F:K").EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        CommandButton1.Caption = "Show Columns"
    Else: CommandButton1.Caption = "Hide Columns"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty
 

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926

ADVERTISEMENT

Hi Norie,
I also want to run a second macro that will hide these columns with the same button but with the second caption. Is it possible??
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
shajueasow

I'm not quite sure what you mean.:eek:

Why do you need a 2nd macro?

All the offered solutions use only 1 sub.
 

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926
Well, Norie....I admit.....the heavy downpour of codes had really got into my nerves.
Thanks A Lot for all the helping hands.....
 

Forum statistics

Threads
1,136,369
Messages
5,675,360
Members
419,565
Latest member
Phil57

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