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]

​

No, I'm just a beginner in guessing …​

​

As stated in post #11 the column is stored within the button AlternativeText property :​

right click on a button, select the Control Format (somethin' like that as my local versions are not English)​

then you must see the Alternative Text tab at the top right …​

 

[288enzo]

​

No, I'm just a beginner in guessing …​

​

As stated in post #11 the column is stored within the button AlternativeText property :​

right click on a button, select the Control Format (somethin' like that as my local versions are not English)​

then you must see the Alternative Text tab at the top right …​

That's really cool, I love learning new things.

 

[Marc L]

​

It's just an easy tip / trick …​

​

Sometimes instead of a button a cell comment can be used, helpful on 'moving headers' …​

 

[288enzo]

​

It's just an easy tip / trick …​

​

Sometimes instead of a button a cell comment can be used, helpful on 'moving headers' …​

I don't suppose you know how to assign the "Alternate text"? I tried -

    With ActiveSheet.Buttons.Add(rng.Offset(0, m - 1).Left, rng.Offset(0, m - 1).Top, rng.Offset(0, m - 1).Width, rng.Offset(0, m - 1).Height)
        .OnAction = "Hide"
        .Characters.Text = "Hide"
        .Name = "Button" & m
        .Alternate.Text = "T"
    End With

I was thinking I could use an array to assign the alternate text E through P.

 

[Marc L]

​

Just comparing with my procedure you must see there is no dot within AlternativeText !​

 

[kevin9999]

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.

Worksheet change event code doesn't go in a standard module, you put it in the individual sheet code area (of the sheet of interest). There's plenty of tutorials on the internet that show you how to do this, here's one: Events

 

[288enzo]

Worksheet change event code doesn't go in a standard module, you put it in the individual sheet code area (of the sheet of interest). There's plenty of tutorials on the internet that show you how to do this, here's one: Events

My final output is a macro file that the user has in their XLSTART folder. They download the latest set of data, then apply the macro I've created on the set of data. If what you're saying is true, the worksheet change event would need to be in the data file the user downloaded. Is that correct?

 

[kevin9999]

My final output is a macro file that the user has in their XLSTART folder. They download the latest set of data, then apply the macro I've created on the set of data. If what you're saying is true, the worksheet change event would need to be in the data file the user downloaded. Is that correct?

The Worksheet change event code goes in the code area of the sheet that has the columns you want to hide.

 

[288enzo]

The Worksheet change event code goes in the code area of the sheet that has the columns you want to hide.

That's what I was afraid of. Unless there is a way to use a macro to put the code into another file, that won't work for my situation. I do appreciate the help.

 

[288enzo]

​

Just comparing with my procedure you must see there is no dot within AlternativeText !​

I see what you did there

Thanks again! My mind is about to explode with all I'm learning.