VBA - Distinguishing when a cell is edited by a data validation list or free fromat text entry

[JLD1616]

Hi All,

Does anyone know of VBA code which can distinguish whether a cell has been edited by:

selecting an option from a data validation list (DVL)
or
manually editing the text in a cell?

The "Show error alert after invalid data is entered" option is unticked to allow both selection from the DVL as well as free format text entries.

The problem arises when using the code below which makes an edited cell within a range display the new value as well as the prior value(s). This works well when using the DVL but not when editing the cell.

Is there any code which can distinguish between the 2?

Hope this makes sense, please let me know if anything requires further clarification.

Many thanks in advance
JLD



Public Prior As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("E3:E100")) Is Nothing Then
If Target <> "" Then
If Prior <> "" Then Target = Target.Text & Chr(10) & Prior
Prior = Target
Else
Prior = ""
End If
End If
Application.EnableEvents = True
End Sub

 

[Gary McMaster]

I think you may have better luck if you cook your own validation dropdown using an ActiveX combo box. You could use the Selection change event to move it around with the cursor. I believe you would have better control over the behavior.

If that's not good enough, maybe you could add another Public string variable that would store the address of the last selected cell (also set inside the selection change event). If my logic is correct, when you go thru the change event the "Target.Address" would be different from the address in "sPublicStringVar" if the user clicked the validation drop down arrow. If the user clicked in the cell (so as to enter data) the addresses would be the same. This may be difficult to manage and impractical.

Sorry I can't offer anything better. At least you get a bump out of my stupid idea.

Gary

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ComboBox1.Top = Target.Top
ComboBox1.Left = Target.Left
ComboBox1.Height = Target.Height
ComboBox1.Width = Target.Width + 15

sPublicStringVar = Target.Address

End Sub

 

[Gary McMaster]

I believe you also have access to the "ListRange" property for validation in each cell. You could parse the ListRange to see if what was just entered/changed in the cell is in the list. If not, then it must have been keyed in.

Gary