Excel - Clickable Cells

Asked By JSno on 25-Sep-08 11:32 AM
Is there any way to assign a macro to a cell instead of a button w/in a cell?
Ideally, I'd like certain cells to be "clickable" and if I can do this w/out
a button - great; if not - so be it.

Thanks in advance.




James_Thomlinso replied on 25-Sep-08 11:41 AM
You can do that. A better way however IMO is to use double click. Otherwise
any time you move to that cell by enter or arrow keys the macro will run.

Right click on the sheet tab and select view code. Paste the following in
the code window...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$A$1" Then
Cancel = True
MsgBox "Tada"
End If
End Sub

When you double click cell A1 a message box appears.
--
HTH...

Jim Thomlinson
Rick Rothstein replied on 25-Sep-08 11:50 AM
It sounds like a SelectionChange event might do what you want. Try this and
see. Right click the tab for the worksheet containing the cell you want to
be "clickable" and select View Code from the popup menu that appears. You
will be placed in the code window for that worksheet. At the top of the code
window are two dropdown boxes... click the left one and select the Worksheet
entry. This should open up to the SelectionChange event automatically. If it
doesn't, then click the right dropdown box and select SelectionChange from
that list. After you have done this, you will see the following...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Any code you place between these two lines of code will execute whenever you
click into a cell different from the currently active cell (what you click
into will, of course, then become the active cell). Since you only are
interested in having one cell "clickable", you need to filter the
SelectionChange event for that cell. You can do that with this If..Then
block....

If Target.Address = "$B$2" Then

End If

where, for example purposes, I am assuming the cell is B2. Note that the
absolute references are the default for the Address property of a range
(Target is the range for the selection you just made... VB automatically
assigns it for you). Now, anything you put between the If..Then statement
and the End If statement will execute whenever you select B2 (as long as you
did so from a cell other than B2; in other words, a SelectionChange event is
not executed if you click the current active cell since no change in
selection would have taken place).

--
Rick (MVP - Excel)