hot to assign an image to another tab's cell with 'double click'

[Terry89]

Dear All,

I have an image database in excel (2010). I want to assign every image with a specific cell of another tab of the same excel file. I want to do this with 'double left click'. Could you please help me?

I did it in the first place by placing a hyperlink to every image but because it only supports a 'single left click' you can accidentally go to another tab and it is frustrating.

Thanks!

 

[Worf]

Hello and welcome to the Board

- Double clicking an image will not fire an event, but here is a workaround:
- Paste the two macros where indicated
- Create a table similar to the one shown below; if you can’t use that particular range, the formulas will need adjusting
- Run the table macro, which will populate some fields
- Fill manually the destination field
- Click a cell near an image and you will be taken to the corresponding sheet. The picture’s top left cell is used as position reference.

Invoice (3)

*	A	B	C	D	E	F	G
2	Image #	Image Name	Image Col	Destination	Last used row	Target Col	Col chosen
3	1	hli2	13	Plan1!F9	5	10	13
4	2	Picture 3	6	Dest!k4	*	*	*
5	3	hli1	17	Instructions!P7	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:92px;"><col style="width:74px;"><col style="width:108px;"><col style="width:101px;"><col style="width:69px;"><col style="width:76px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E3 =MATCH(LOOKUP(2,1/(A:A<>""),A:A),A:A) G3 {=INDEX(C3:C5,MATCH(SMALL(ABS(F3-C3:C5),1),ABS(F3-C3:C5),0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

' standard module
Sub GenerateTable()
Dim r%, i%
Range("a3:c100").ClearContents
For i = 1 To ActiveSheet.Pictures.Count
    Cells(i + 2, 1) = i
    Cells(i + 2, 2) = ActiveSheet.Pictures(i).Name
    Cells(i + 2, 3) = ActiveSheet.Pictures(i).TopLeftCell.Column
Next
r = ActiveSheet.Range("e3")
ActiveSheet.Range("g3").FormulaArray = "=index(c3:c" & r & ",match(small(abs(f3-c3:c" _
& r & "),1),abs(f3-c3:c" & r & "),0))"
End Sub

' sheet module
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sr
Me.Range("f3") = Target.Column
sr = Split(Me.Range("d" & Evaluate("=match(g3,c:c,0)")), "!")
Sheets(sr(0)).Activate
Sheets(sr(0)).Range(sr(1)).Activate
End Sub