Run Code on Cell Select

[Luke Bailey]

Hello,

I have a couple of questions on Excel Spreadsheet. I have a protected
worksheet that does not allow users to select locked or unlocked cells.
Ideally, I would like users to have a cell that when clicked on, runs a bit
of code. I thought I could accomplish by inserting a button, then setting
background to opaque and shawdows to false. This does work, but you can
still see outline off the button (which I do not want). Any other way to
accomplish this?

I am willing to compromise and let the protection of the form allow users to
select locked cells if necessary. But would also like to have the mouse
change to a different pointer when over that cell (which was working with the
button as well).

Thanks for any ideas!

 

[Chip Pearson]

You can use the SelectionChange event procedure of the workbook to detect
when a focus moves to a cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If StrComp(Target.Address, "$A$1", vbBinaryCompare) = 0 Then
' user selected A1
' your code here
End If
End Sub

However, if you are not allowing the user to select any cells, locked or
unlocked, this won't work since focus can never be set to a cell. In this
case, have only two options that I can think of. The first is to create a
menu item or commandbar that the user can click to run some code. The other
option is to drop a Forms or OLE button directly on to the worksheet and
attach some code to the button.

Finally, you cannot change the cursor when the user hovers over a cell. You
can change it with VBA code but such code won't be triggered if the cursor
just hovers over, without actually selecting, a cell.

My preference is to always allow the user to select any cells he wants, even
those that are locked. The reason is that the user may use the selection
indicator and row/column heading colors to highlight cells to make things
easier to read, especially in a large data table.

I guess the bottom line is that you can't really do any of the things you
want to.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Sinner]

Interesting addition : )

Thx.

You can use the SelectionChange event procedure of the workbook to detect
when a focus moves to a cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If StrComp(Target.Address, "$A$1", vbBinaryCompare) = 0 Then
        ' user selected A1
        ' your code here
    End If
End Sub

However, if you are not allowing the user to select any cells, locked or
unlocked, this won't work since focus can never be set to a cell. In this
case, have only two options that I can think of. The first is to create a
menu item or commandbar that the user can click to run some code. The other
option is to drop a Forms or OLE button directly on to the worksheet and
attach some code to the button.

Finally, you cannot change the cursor when the user hovers over a cell. You
can change it with VBA code but such code won't be triggered if the cursor
just hovers over, without actually selecting, a cell.

My preference is to always allow the user to select any cells he wants, even
those that are locked. The reason is that the user may use the selection
indicator  and row/column heading colors to highlight cells to make things
easier to read, especially in a large data table.

I guess the bottom line is that you can't really do any of the things you
want to.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)









- Show quoted text -

 

[Luke Bailey]

Thanks Chip!

I went with the Workseet_SelectionChange option and let users select the
unlocked cells - then commented on cell so they know clicking triggers an
event to let the code run and drill down to further level data.

After some thinking about it, users may want to copy and paste areas from
the worksheet into an e-mail, etc. anyways, so works out for the best.

Thanks again!

Luke