Passing info to macro from icons

[Chrisso]

Hi All

To preface my question:

* I like to dumb things down for my users my adding icons (a gif image
thru Insert->Picture->From File) that perform tasks such as sorting.
This just reduces their role to clicking icons instead of controlling
the sort at a lower level through the menu system.

* I have an icon at the top of each column of data. The icon says
"Sort" and I intend for my users to click this button when they want
that column sorted.

* I have my macro subroutine (MySort) written generically taking as a
parameter the column to sort on so in my code I can just call
My_Sort("C") or MySort(ActiveCell.Column).

* At the moment I have to have wrapper subroutines for each icon on my
sheet - so if I have icons on columns A,B & C I need : My_Sort_A,
My_Sort_B and My_Sort_C which just call My_Sort with the correct
column. It is these wrapper subroutines which I then attach to the
correct icons with a right click and "Add/Attach Macro".

Obviously this wrapper solution is fine for a small amount of columns
but it does not scale AND it is not elegant

I could get around this if I could pass info about the location of the
clicked icon to my code but as far as I can see you can only tell Excel
to run a macro when an icon is clicked and not any information about
where the icon is located - the active cell before the icon is clicked
is still the active cell when the icon is clicked.

Is there anyway to get into my code the location (cell, column or row)
of the icon/drawing that was clicked by the user?

Thanks for any help in advance - I have used this newsgroup a *lot* to
get things done but this is my first post.

Chris

 

[Don Guillett]

why bother with all that overhead. Just copy paste this to the sheet module
(right click sheet tab>view code). Use a defined name for sortrange. Now
just inform them that the range will be sorted by whatever column they
double click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
[sortrange].Sort Key1:=Cells(1, activecell.column), _
Order1:=xlAscending, Orientation:=xlTopToBottom
End Sub

 

[Tom Ogilvy]

Public Sub Pic_Click()
Dim sname as String
Dim pic as Picture
Dim rng as Range
sname = Application.Caller
set pic = Activesheet.Pictures(sName)
set rng = pic.TopLeftCell
'msgbox "row: " & rng.row & " column: " & rng.column
rng.CurrentRegion.Sort Key1:=rng, _
Order1:=xlAscending, _
Orientation:=xlTopToBottom

End Sub

should get you started. Place this code in a general module (not the sheet
module). Assign this same single macro (or one adjusted to your needs) to
all the icons.

 

[Chrisso]

Huge thanks to both Don and Tom.

Both solutions do exactly what I wanted and are wonderfully concise.

I have tried them both out with great results. Thankyou very much for
your reply and code.

Cheers
Chris