Toggle Column Visiblity & Button Caption

[the blue fox] · Nov 15th 2006

On creating a button to perform a macro, we are hiding columns.

Can the macro pick up that

If the columns E:J are hidden, then the text on the button to read 'Unhide'
If the columns E:J and unhidden, then text on the button to read 'Hide'

I am aware I can change what is written on the button via a macro, but can it see that the columns are hidden????

Make sense I hope

Thanks

GeorgS · Nov 15th 2006

Re: Dual Vba Macro Code

This is in the code "behind" Sheet1 with column E hidden.

Code

Private Function ishidden()
For x = 1 To 10
    Debug.Print Columns(x).Hidden
Next
End Function

Check the immediate window to see the output.

G.

Edit: What's with the Title? I'd duck if I were you.

[the blue fox] · Nov 15th 2006

Re: Dual Vba Macro Code

hrm .. thanks for the heads up on the title, but not sure how the code you supplied had anything to do with what I am trying to do.

Dave Hawley · Nov 15th 2006

Re: Dual Vba Macro Code

Use a Button from the Forms toolbar and assign it to this macro

Code

Sub ToggleHideCols()
Dim bHide As Boolean
Dim strCptn As String
Dim SrtBut As String


SrtBut = Application.Caller
    bHide = Columns("E:J").Hidden
    Columns("E:J").Hidden = Not bHide
    
    If bHide = True Then
        strCptn = "Hide"
    Else
       strCptn = "Show"
    End If
    
    ActiveSheet.Shapes(SrtBut).TextFrame.Characters.Text = strCptn
End Sub

Display More

GeorgS · Nov 15th 2006

Re: Toggle Column Visiblity & Button Caption

Dave,

I noticed that if in:

Code

Columns("E:J").Hidden

If column J is "unhidden", the statement above still returns

Code

True

This is not the case with column E.

Interesting.

G.

Dave Hawley · Nov 15th 2006

Re: Toggle Column Visiblity & Button Caption

Interesting to say the least. I guess there must be some logic to that, but perhaps it's M$ logic

[the blue fox] · Nov 15th 2006

Re: Toggle Column Visiblity & Button Caption

Sorry guys ..

Is there any way to get around having to assign the button number?
I have the same button on 12 sheets .. and even though they are a copy of each other from Button 2 .. its throwing up an error.

Can it be a generic code?

Dave Hawley · Nov 15th 2006

Re: Toggle Column Visiblity & Button Caption

Try it now.

[the blue fox] · Nov 15th 2006

Re: Toggle Column Visiblity & Button Caption

thanks heaps guys ..

all done .. works a treat

GeorgS · Nov 15th 2006

Re: Toggle Column Visiblity & Button Caption

Here's a Command Bar Option.

Needs a bit of work on the Workbook open event.

G

Toggle Column Visiblity & Button Caption

Participate now!

Share