Excel - detecting if another cell is selected

September 6, 2009 at 16:23:03
Specs: Windows Vista
Hi - I know absolutely nothing about VB, but have a pretty good working knowledge of Excel. I'm am trying to find out if I can get one cell to tell me if another cell is selected. Not highlighted, selected with the mouse.

Why am I asking? I'm trying to add some functionality to a spreadsheet that would allow me to simply select a few dates with the mouse, and then have Excel calculate certain stats for criteria that happened within that data range. Obviously, this would require that one cell is able to tell when another cell is selected, something that can't be done with normal Excel formulas to my knowledge.

I hope this makes sense. To expose my VB ignorance even further, I wouldn't know what to do with the VB code even if i already had it, so if you could give me some guidance there too, it would help! Thanks folks!!


See More: Excel - detecting if another cell is selected

Report •


#1
September 6, 2009 at 17:33:15
Right click a sheet tab and choose View Code.

Paste this into the window that opens then start selecting cells in that sheet, while keeping an eye on A1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Range("A1") = "FYI: " & Target.Address & " was just selected."
End Sub


Report •

#2
September 6, 2009 at 17:57:32
Great! Thank you for your detailed response DerbyDad03.

Before I input your data, let me expound a little further to make sure we are on the same wavelength. Specifically what I'm trying to get is to have cell L5 show a 'Yes' or 'True' when A5 is selected, and a 'No' or 'False' when A5 is Not selected..

Furthermore, I need to be able to 'copy' this capability down to all cells below...so that cell L59 shows a 'Yes' when cell A59 is selected and so forth.... Are we still cool?

thanks


Report •

#3
September 6, 2009 at 19:13:39
Well, we're cool if by that you mean I don't mind that you left out the details of your requirements in your original post.

I guess I could have just responded "yes" to your statement I'm am trying to find out if I can get one cell to tell me if another cell is selected.

Now that you've told us what you really want, I'll go back and rewrite the code I offered earlier.

Please hold...no music will follow.
....
...
..
.

OK, I'm back.

Paste this into the window I suggested earlier and select any cell in rows 1 - 59. Modify as required.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Range("L1:L59") = "''No' or 'False'"
 If Target.Column = 1 Then Range("L" & Target.Row) = "''Yes' or 'True'"
End Sub


Report •

Related Solutions

#4
September 6, 2009 at 19:42:01
Hey - It works perfectly! The only problem is that I need to be able to select multiple cells in column A. Right now it only works for the first cell that I click on.

In other words, if I select cells A20 thru A31, I need cells L20 thru L 30 to say 'Yes'. Currently only cell L20 would say 'Yes'.

This programmins stuff is very detail-specific, so I'm sorry about the run-around. Thanks.


Report •

#5
September 6, 2009 at 21:15:40
This programmins stuff is very detail-specific

So why don't you give us all of the requirements instead of posting them piecemeal?

First you said you wanted to know if one cell could tell you if another cell was selected. Then you added requirements related to placing text in a specific cell in Column L when a specific cell in Column A was selected, something that was not even hinted at in your OP. Now you've added another requirement regarding the selection of multiple cells.

Please do us all a favor:

Tell us what the end product needs to do. We may come back and ask for clarification on some points, but right now we're just spinning our wheels writing code for what you ask for in one post only to have you come back and ask for something different.


Report •

#6
September 6, 2009 at 21:56:49
I am sorry I've upset you. I fully understand that you are giving your time freely here and I apologize that you feel I'm wasting it...that was not my intention. Regardless of whether or not you choose to reply further, I do appreciate your help.

When I said that I needed one cell to be able to tell me when another cell is selected, that is exactly what I meant, and is still what I mean. In other words, I need a cell in column L to tell me when a cell in column A is selected. What I didn't say is that I need that functionality on one or more rows of the spreadsheet to allow for the flexibility I need. Therefore, regardless of how many cells are selected in column A, I need the corresponding cells in column L to reflect that with a 'Yes'. Otherwise the cells in column L should say 'No.

I had no way of knowing that the code would only work on the first cell of the selected range in column A. Having no knowledge of VB, I could only assume it worked similarly to the way Excel formulas work.

But to answer your question, I need to be able to select one date OR multiple dates in column A, and have the corresponding cells in column L reflect that with a 'Yes', otherwise they should say 'No'. Again...your code does everything I need, except it doesn't allow for multiple date selections, or for more than one consecutive date selections.

I think this covers covers it. Thanks!



Report •

#7
September 6, 2009 at 22:00:58
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Range("L1:L59") = "No"
  If Target.Column = 1 Then
   For Each aCell In Range(Target.Address)
    Range("L" & aCell.Row) = "Yes"
   Next
  End If
End Sub


Report •

#8
September 6, 2009 at 22:23:32
Thank you, thank you, thank you. Seriously. Perfect.

Report •


Ask Question