How can I identify what cell was clicked on when I click on a butt

[Guest]

Is it possible to identify what cell was clicked on when I click on a button
to run a macro. I want to put about 10 buttons as column headers in a large
table. I would like to associate the same macro with each button and sort
based on which button (column) I click on. For example, with buttons,
"name", "city", "state", "zipcode", "product" all associated with the
"sorter" macro:
+------------+------------+------------+------------+------------+
| name | city | state | zipcode | product |
+------------+------------+------------+------------+------------+
j smith cupertino ca 95221 basket
a jones anaheim ca 93323 torch
p will las vegas nv 21352 basket
t adams reno nv 21992 bucket

I would like to click on the button and have the macro sort the respective
column. Does anyone have any idea how this can be done or even if it can be
done in Excel? Thank you.

 

[PaulD]

: Is it possible to identify what cell was clicked on when I click on a
button
: to run a macro. I want to put about 10 buttons as column headers in a
large
: table. I would like to associate the same macro with each button and sort
: based on which button (column) I click on. For example, with buttons,
: "name", "city", "state", "zipcode", "product" all associated with the
: "sorter" macro:
: +------------+------------+------------+------------+------------+
: | name | city | state | zipcode | product |
: +------------+------------+------------+------------+------------+
: j smith cupertino ca 95221 basket
: a jones anaheim ca 93323 torch
: p will las vegas nv 21352 basket
: t adams reno nv 21992 bucket
:
: I would like to click on the button and have the macro sort the respective
: column. Does anyone have any idea how this can be done or even if it can
be
: done in Excel? Thank you.

While it's not easy to tell what cell was clicked on (since you are clicking
a button in the cell), wouldn't you rather know what button was clicked on?
If you create 10 buttons, then each button would have it's own onclick event
that could then feed a range to a sort routine. If you would consider
double clicking a cell, then the doubleclick event could be used. Paste the
following in the sheet code module

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As
Boolean)
If target.Row = 1 Then
target.CurrentRegion.Sort Key1:=Range(target.Address),
Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, Orientation:=xlTopToBottom
Cancel = True
End If
End Sub

Note this routine assumes you have a header row in row 1 of the worksheet.
If you double click any cell in row 1, it should sort the table based on
that row. If you want decending instead of Ascending, change order1. You
could also add key2, order2 and key3, order3 if you want a second or third
sort field.
Paul D

 

[Tim Williams]

Use Application.Caller to identify which button called the macro.

Sort accordingly.

Tim