From: Rod Speed on
I already have a couple of macros that sort a particular sheet by one of two columns.

I have them attached to buttons which replace the normal column heading in those two columns.

That works fine, but doesnt look very elegant.

How do I attach a macro to a normal column heading cell, so it doesnt look any different
to the other column headings that dont have macros attached, except with say an underline
under the text to indicate that you can sort on those columns ?

Not that easy to search for that using google etc.


From: Gord Dibben on
Don't use buttons.

Use worksheet double-click event code to run your macros.

You can then have anything you want as column titles.

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
'Substitute your cells/macro names.
Select Case Target.Address(False, False)
Case "A1"
Cancel = True
MyA1Macro
Case "B1"
Cancel = True
MyB1Macro
Case "C1"
Cancel = True
MyC1Macro
End Select
End Sub


Gord Dibben MS Excel MVP

On Fri, 23 Apr 2010 08:52:25 +1000, "Rod Speed" <rod.speed.aaa(a)gmail.com>
wrote:

>I already have a couple of macros that sort a particular sheet by one of two columns.
>
>I have them attached to buttons which replace the normal column heading in those two columns.
>
>That works fine, but doesnt look very elegant.
>
>How do I attach a macro to a normal column heading cell, so it doesnt look any different
>to the other column headings that dont have macros attached, except with say an underline
>under the text to indicate that you can sort on those columns ?
>
>Not that easy to search for that using google etc.
>

From: Rod Speed on
Thanks for that, knew it had to be something obvious like that.

Gord Dibben wrote:
> Don't use buttons.
>
> Use worksheet double-click event code to run your macros.
>
> You can then have anything you want as column titles.
>
> Private Sub Worksheet_BeforeDoubleClick( _
> ByVal Target As Excel.Range, Cancel As Boolean)
> 'Substitute your cells/macro names.
> Select Case Target.Address(False, False)
> Case "A1"
> Cancel = True
> MyA1Macro
> Case "B1"
> Cancel = True
> MyB1Macro
> Case "C1"
> Cancel = True
> MyC1Macro
> End Select
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Fri, 23 Apr 2010 08:52:25 +1000, "Rod Speed"
> <rod.speed.aaa(a)gmail.com> wrote:
>
>> I already have a couple of macros that sort a particular sheet by
>> one of two columns.
>>
>> I have them attached to buttons which replace the normal column
>> heading in those two columns.
>>
>> That works fine, but doesnt look very elegant.
>>
>> How do I attach a macro to a normal column heading cell, so it
>> doesnt look any different
>> to the other column headings that dont have macros attached, except
>> with say an underline under the text to indicate that you can sort
>> on those columns ?
>>
>> Not that easy to search for that using google etc.