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.

"san jose native" wrote in message
...
: 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

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

Sort accordingly.

Tim


"san jose native" wrote in
message ...
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.

Here's an old post.

I put rectangles over the headers (made the borders invisible) so that when you
clicked on the rectangle, it looked like you were clicking on the header.

Option Explicit
Sub setupOneTime()

Dim myRng As Range
Dim myCell As Range
Dim curWks As Worksheet
Dim myRect As Shape

Set curWks = ActiveSheet

With curWks
'10 columns
Set myRng = .Range("b1").Resize(1, 10)
For Each myCell In myRng.Cells
With myCell
Set myRect = .Parent.Shapes.AddShape _
(Type:=msoShapeRectangle, _
Top:=.Top, Height:=.Height, _
Width:=.Width, Left:=.Left)
End With
With myRect
.OnAction = ThisWorkbook.Name & "!SortTable"
.Fill.Visible = False
.Line.Visible = False
End With
Next myCell
End With
End Sub
Sub sortTable()

Dim myTable As Range
Dim myColToSort As Long
Dim curWks As Worksheet
Dim mySortOrder As Long
Dim LastRow As Long

Set curWks = ActiveSheet
With curWks
myColToSort = .Shapes(Application.Caller).TopLeftCell.Column
LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
Set myTable = .Range("b1:b" & LastRow).Resize(, 10)
If .Cells(myTable.Row + 1, myColToSort).Value _
< .Cells(LastRow, myColToSort).Value Then
mySortOrder = xlDescending
Else
mySortOrder = xlAscending
End If
myTable.Sort key1:=.Cells(myTable.Row, myColToSort), _
order1:=mySortOrder, _
header:=xlYes
End With

End Sub

san jose native wrote:

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.

--

Dave Peterson