Excel Worksheet Cell Events

[grnzbra]

Do the cells on Excell worksheets have events ie. OnEnter, OnChange, OnExit etc? I have to create a worksheet in which users enter names of things along with various information about the named items. The Names column is the key field. When the user enters a name in that field, I need to check the entire column to be sure the name doesn't already exist and prevent the user from exiting the cell (or row) if a match is found.

When I go into the macro help and look for On Exit, I do get a write up on the following

Private Sub object_Exit( ByVal Cancel As MSForms.ReturnBoolean)

I am trying to use the named range for that column as the object.
However, when I try to go past AS within the parenthasis, all the available choices that start with MS are followed by o. There are no MSForms available in the drop down selection. I have not been able to figure out how to make this work. I'm am using Excel 2007

 

[SkipVought]

Hi,

The are a whole passel of events associated with the Worksheet Object. One is the Change event. Another is the SelectionChange event. Both are with respect to a Target range, which can be one or more cells.

You can code the SelectionChange event that when a selection is made that includes your column of interest, the value is stored. Then when a change occurs in your column that is found elsewhere in the column, the Change event can be coded to replace the original value that was previously stored.

You can get help for this in forum707, where VBA is specifically addressed.

 

[grnzbra]

Thank you very much.

I found a nonVBA way of doing it.

Create hidden columne and set its value to =COUNTIF(<Named Range>,G4)
In my case, my named range started at G4.
Copy that cell down to the row that is the last row of the named range.

Then select the named range and select Data Validation from Data Tools
On the Settings tab set Allow to Custom
Set Formula to =IF(A4 < 2,True,False)
In my case, the hidden column is the A column.

On the Error Alert tab set Style to Stop (this will prevent the violating value to remain.)
Set Error Message to your own attention getting nastygram

 

[grnzbra]

Just came across problem with my solution above. It works fine if one tabs out of the cell, but if one mouses out it doesn't

 

[zelgar]

Instead of having a hidden column with the COUNTIF formula, why don't you just have the conditional format use the COUNTIF as it's criteria (e.g., =COUNTIF(<Named Range>,G4)>1) and copy/paste the format for all of the cells. This would highlight both cells with the same name.