score:2

Accepted answer

with ref to the query, workaround for the dv and change event is managed.

public strrange as string
public bcheck as boolean

private sub worksheet_change(byval target as range)
if bcheck then exit sub

msgbox "correct entry!"

strrange = target.address
bcheck = true
end sub

private sub worksheet_selectionchange(byval target as range)
if target.address <> strrange then bcheck = false
end sub

http://forum.chandoo.org/threads/multiple-worksheet-change-event-with-data-validation.32750

score:3

you have a very open-ended question...........not having the time to do a full whitepaper, here is a simple experiment. i use the event code:

private sub worksheet_change(byval target as range)
    dim a1 as range, rint as range

    set a1 = range("a1")
    set rint = intersect(a1, target)
    if rint is nothing then exit sub
    msgbox a1.value
end sub

and in a1, i setup dv as follows:

enter image description here

if i use the drop-down, i get the value entered and i also get the msgbox. however, if i click on the cell and type some junk what happens is:

  1. the dv alert occurs and i touch the cancel button
  2. i get 2 msgbox occurrences, each with the original contents rather than the attempted junk !!

i have absolutely no idea why the event is raised since the cell is not actually changed, let alone why the event is raised twice !! it is almost as if the event is raised on junk entry, but the dv alarm has precedence, the dv reverse the entry and another event is raised, and finally both events get processed.

hopefully a person smarter than me will chime in.


Related Query

More Query from same tag