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

[shajueasow]

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

 

[Norie]

I created a button from the Forms toolbar, selected New when the Assign Macro dialog appeared and then used this 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]

Hi,

try this

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

CommandButton1.Caption = txt

kind regards,
Erik

 

[Smitty]

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]

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]

shajueasow

I'm not quite sure what you mean.

Why do you need a 2nd macro?

All the offered solutions use only 1 sub.

 

[shajueasow]

Well, Norie....I admit.....the heavy downpour of codes had really got into my nerves.
Thanks A Lot for all the helping hands.....