Naming a Grouped range of columns in a spreadsheet

Hi,

Was wonderinf if there was a way of assigning a name or label to a set of Grouped columns in excel?  For example, if i have a small range of columns that relate to fees, and I group these togather, can I assign a label Fees to this, so that when the gropup is minimised, then a label is there that I can click on to open the fees grouped data?

Hope this makes sense :-)

Hi Marcos,

Alt-F11 to open the VBA editor

Alt-IM to insert a new code module

In the new module, paste the following code

'==========>>

Option Explicit

'---------->>

Public Sub ToggleColumns()

    Dim SH As Worksheet

    Dim Rng As Range

    Dim obj As Variant

    Dim BTN As Button

    Dim iLen As Long

    Const myColumns As String = "H:K"               '<<===== Change

    Const sFees As String = "Fees"                       '<<===== Change

    Const sHidden As String = " Hidden"

    Const sVisible As String = " Visible"

    Set SH = ActiveSheet

    Set BTN = SH.Buttons(Application.Caller)

    Set Rng = SH.Columns(myColumns)

    With Rng.EntireColumn

        .Hidden = Not .Hidden

        If .Hidden Then

        iLen = Len(sHidden) + Len(sFees)

            BTN.Characters.Text = sFees & " Hidden"

            With BTN.Characters(Start:=1, Length:=iLen).Font

                .Name = "Arial"

                .FontStyle = "Bold"

                .Size = 10

                .ColorIndex = 3  '\ RED

            End With

        Else

        iLen = Len(sVisible) + Len(sFees)

        BTN.Characters.Text = sFees & " Visible"

            With BTN.Characters(Start:=1, Length:=iLen).Font

                .Name = "Arial"

                .FontStyle = "Bold"

                .Size = 10

                .ColorIndex = 4  '\ GREEN

            End With

        End If

    End With

End Sub

'<<==========

Alt-Q to close the VBA editor

Place  a button from the forms toolbox onto the worksheet and assign the macro to the button:

Now, clicking the button will alternately hide and unhide the fees columns and the button's caption will alternately show Fees Hidden in red orFees Visiblein green**.** Thus, the button caption will act in lieu of your label and you will have the desired functionality.

===

Regards,

Norman

Hi Norman,

I am trying to apply the same grouping principle to Rows rather than columns, can you assist with the alternate code to create the hide/unhide button please?

Many Thanks

Thanks for your advice Vijay, I'll put that suggestion in :-)

Hi Norman,

Your solution works! but only if I need to hide/unhide the columns ("fees") while working elsewhere in the spreadsheet.

I have groups of 6 columns, per project, that I need to hide and unhide - and they have nothing to do with each other.

Can I do this, using your solution - hence having the buttons up in the grey area above the column labels (A, B, C...)? 

So far the only way I've found to do this, was to put all the buttons below one another in an inserted column, A

Thanks :) 

Marie