drop-down menu not displaying info in correct cells - Microsoft Community

in column a, i've # header. in column b, i've last name header. in column c, i've first name header.

1 when click on last name in column b. last name suppose displayed in column b & first name suppose displayed in column c.

error: instead, first name ends in column a erasing number in column. last name ends in right place.

2 when click on first name in column c. first name suppose displayed in column b & last name suppose displayed in column c.

error: instead, first name displayed in column c. again, last name in right place.

please inspect code below & correct me, thx.

private sub worksheet_change(byval target range)
    dim arrsplit variant

    on error goto reenableevents
    application.enableevents = false

    if target.cells.count = 1 then  'if 1 cell changed
        if target.column = 1 then   'if change in column a
            arrsplit = split(target.value)
            target.value = arrsplit(0)
            target.offset(0, 1) = arrsplit(1)
        end if

        if target.column = 2 then   'if change in column b
            arrsplit = split(target.value)
            target.value = arrsplit(0)
            target.offset(0, -1) = arrsplit(1)
        end if
    end if

reenableevents:
    if err.number <> 0 then
        msgbox "an error occurred in module " & me.name & " private sub worksheet_change"
    end if

    application.enableevents = true

end sub

                            

there's annoying window keeps popping whenever delete names cells.

i have thought if want delete 1 of cells have deleted both cells , if both cells selected delete error not occur because code not run multiple cells changed.

however, replace code following , error not occur if selecting 1 cell clear.

private sub worksheet_change(byval target range)
    dim arrsplit variant
   
    on error goto reenableevents
    application.enableevents = false
   
    if target.cells.count = 1 then  'if 1 cell changed
        if target.column = 2 then   'if change in column b
            if target.value = "" goto reenableevents  'additional line
            arrsplit = split(target.value)
            target.value = arrsplit(0)
            target.offset(0, 1) = arrsplit(1)
        end if
       
        if target.column = 3 then   'if change in column c
            if target.value = "" goto reenableevents  'additional line
            arrsplit = split(target.value)
            target.value = arrsplit(0)
            target.offset(0, -1) = arrsplit(1)
        end if
    end if
   
reenableevents:
    if err.number <> 0 then
        msgbox "an error occurred in module " & me.name & " private sub worksheet_change"
    end if
   
    application.enableevents = true
   
end sub

Office / Excel / Microsoft Office Programming / Office 365 Home