Use one macro for multiple buttons to hide individual columns

[288enzo]

I have 12 buttons across E3:P3. Each button would run the same macro "Hide", which simply hides the column. I know I could create 12 separate macros for each button. How can I have one macro "Hide" and for it to know which button the user clicked on thus knowing which column to hide? Each button is named button 1, 2, 3, and so on. So, if my macro "Hide" knew that Button2 was pressed then it would hide columns("F"). I just can't figure out how to pass the name of the button to the "Hide" macro. I thought if I could pass it on I then could use Case Select?

What I came up with to create the buttons is:

    Dim i As Long, rng As Range
    Set rng = Range("E3")
    For i = 1 To 12
        ActiveSheet.Buttons.Add(rng.Offset(0, i - 1).Left, rng.Offset(0, i - 1).Top, rng.Offset(0, i - 1).Width, rng.Offset(0, i - 1).Height).Select
        Selection.OnAction = "Hide"
        Selection.Characters.Text = "Hide"
        Selection.Name = "Button" & i
    Next i

 

[Marc L]

How can I have one macro "Hide"

Different ways possible, one of them is to not pass anything to the VBA procedure but to just use the AlternativeText property of each button​

combined with the Application.Caller function for the button name calling this procedure so nothing is hardcoded …​

 

[288enzo]

I'd be tempted to use a Worksheet.SelectionChange event to trigger the macro. Something like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Me.Range("E3:P3"), Target) Is Nothing Then
    Selection.Columns.Hidden = True
End If
End Sub

That would hide the column if the user selected any cell in the range E3:P3, and a simple Sub to unhide all the hidden columns:

Sub unhideColumns()
    ActiveSheet.Cells.Columns.Hidden = False
End Sub

With a shortcut key such as Ctrl+u to run it.

I like the idea. I must not be executing it correctly because nothing is happening when I select E3, or any cell between E3:P3.

 

[288enzo]

See if the following works for you (you can assign macros to shapes as well):

Sub AddButtons()
    Dim i As Long, rng As Range
    Set rng = Range("E3")
    For i = 1 To 12
        With ActiveSheet.Buttons.Add(rng.Offset(0, i - 1).Left + 1, rng.Offset(0, i - 1).Top + 1, rng.Offset(0, i - 1).Width - 2, rng.Offset(0, i - 1).Height - 2)
            .OnAction = "Hide"
            .Characters.Text = "Hide"
            .Name = "Button" & i
        End With
    Next i
End Sub
Sub Hide()
    ActiveSheet.Buttons(Application.Caller).TopLeftCell.EntireColumn.Hidden = True
End Sub

Worked perfectly, thank you so much. Can you help explain what is happening? It looks very similar to what I already had.

 

[288enzo]

Different ways possible, one of them is to not pass anything to the VBA procedure but to just use the AlternativeText property of each button​

combined with the Application.Caller function for the button name calling this procedure so nothing is hardcoded …​

I've been doing a lot of searching about "Caller". I can't find any information on what all you can put after it. Such as, "TopLeftCell.EntireColumn". I would appreciate it if someone could point me in the right direction on where to find this info. Even in Excel, all it says is Caller is a member of Application. Thanks

 

[Marc L]

​

On my side VBA help tells so much more ‼ Anyway a Caller & AlternativeText sample here !​

 

[288enzo]

​

On my side VBA help tells so much more ‼ Anyway a Caller & AlternativeText sample here !​

I opened your link, don't really understand what I'm looking at. I did hit F1 for help and found some additional info. Shape.TopLeftCell property (Excel)

Thanks for pointing me to help. I was going about it wrong via the Object Browser. I was looking to find a list of what I could input after "Application.Caller".

 

[Marc L]

​

This sample demonstrates how to hide / unhide some columns without hardcoding any, just using Caller & AlternativeText …​

 

[288enzo]

​

This sample demonstrates how to hide / unhide some columns without hardcoding any …​

Must have been my fat fingers, nothing happens when I click on any of the buttons.

 

[Marc L]

​

Close the workbook, reopen it and say Yes to activate the Macros …​

 

[288enzo]

​

Close the workbook, reopen it and say Yes to activate the Macros …​

Odd, the first time I opened it I don't recall being asked to enable macros. Anyhow, up and running. Opened your script, for the life of me I haven't a clue how you did it. I playing around with the text in the button, that didn't make a difference. How in the world do the buttons know which column to hide? You truly are a master.