double-click moves me to another cell on spreadsheet

[mjb]

I have set up an inventory of the chemicals we keep on an excel
spreadsheet... What I want to be able to do is to double-click on a chemical
(ie double-click in a cell) and be referred to new cell that lists the
synonym of that chemical.

In other words, when I double- click on 'salt' the spreadsheet moves me to
cell that says 'NaCl'... and thus, all of the pertinent information for NaCl.

 

[BobT]

Why double-click? You could set a hyperlink on the cell with "Salt" that
takes you to the range that explains the details on NaCL.

1. Name the range of details (only one cell needs to be named). This will
be where you are taken.
2. In the cell where you have "Salt", choose INSERT then HYPERLINK; click
on "Place in This Document" on the left; pick your named range and click ok.

Now whenever someone clicks on the Salt cell you are taken to your details
on it.

If you want something more dynamic, you'd have to write a macro that reads
the value of the cell you're double-clicking (or when you click on custom
button in a toolbar) and it does a look up to see where it needs to take you.

 

[JLatham]

I agree with BobT that hyperlinks could be a good simple solution to your
problem. However, it suffers from not being very 'robust' - a hyperlink to a
cell will always go to that cell, and the target may move due to
inserted/deleted row unless your worksheet is finalized before you start
adding the hyperlinks.

Here's some sample code for a simple process that responds to double-clicks
in columns A, D and G on a sheet and moves you across the row based on which
column you clicked in. That is, if you double-click in column A, you end up
on the same row, but in column D; and if double-click is in column D, you
jump to same row in column G and finally, double-click in G and you go back
to A on same row.

To put it into your workbook, open it and choose the sheet you need to have
this feature on. Right-click on the sheet's name tab and choose [View Code]
from the list. Copy the code below and paste it into the module that was
presented to you. Close the VB Editor and it will all work for you on that
one sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
'simple example of moving to another cell
'on the same row that the double-click took place in
'
'if a d-c took place in column A, move to
'column D on same row;
'if it took place in column D, move to G
'if in G, move back to A on the same row.
'ignore all others.
Select Case Target.Column
Case Is = 1 ' in column A; B=2, C=3 etc.
Cancel = True
'this moves to column D and scrolls it into
'upper left-most position
Application.Goto Range("D" & Target.Row), True
Case Is = 4 ' in column D
Cancel = True
'this moves to column G and scrolls it into
'upper left-most position
Application.Goto Range("G" & Target.Row), True
Case Is = 7 ' in column G
'returns us back to column A
Cancel = True
'this moves to column A and scrolls it into
'upper left-most position
Application.Goto Range("A" & Target.Row), True
Case Else
'ignore d-c in other columns, act normally
'does nothing!
End Select

End Sub

 

[JLatham]

I assume from your exasperated tone that you're dealing with Excel 2007?

Go to the Page Layout tab and then in the [Sheet Options] group, click on
the drop down arrow in the lower right corner of the group. You'll find a
familiar looking worksheet setup dialog hiding in there.