Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 29

Thread: Data Validation in VBA Code

  1. #11
    Board Regular
    Join Date
    Sep 2018
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation in VBA Code

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, [T2]) Is Nothing And UCase([T2]) = "YES" Then _
        Sheets("CSA1").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T3]) Is Nothing And UCase([T3]) = "YES" Then _
        Sheets("CSA2").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T4]) Is Nothing And UCase([T4]) = "YES" Then
        Sheets("CSA3").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T5]) Is Nothing And UCase([T5]) = "YES" Then _
        Sheets("CSA4").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T6]) Is Nothing And UCase([T6]) = "YES" Then _
        Sheets("CSA5").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T7]) Is Nothing And UCase([T7]) = "YES" Then _
        Sheets("CSA6").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T8]) Is Nothing And UCase([T8]) = "YES" Then _
        Sheets("CSA7").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T9]) Is Nothing And UCase([T9]) = "YES" Then _
        Sheets("CSA8").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T10]) Is Nothing And UCase([T10]) = "YES" Then _
        Sheets("CSA9").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T11]) Is Nothing And UCase([T11]) = "YES" Then _
        Sheets("CSA10").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T12]) Is Nothing And UCase([T12]) = "YES" Then _
        Sheets("CSA11").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T13]) Is Nothing And UCase([T13]) = "YES" Then _
        Sheets("CSA12").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T14]) Is Nothing And UCase([T14]) = "YES" Then _
        Sheets("CSA13").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T15]) Is Nothing And UCase([T15]) = "YES" Then _
        Sheets("CSA14").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T16]) Is Nothing And UCase([T16]) = "YES" Then _
        Sheets("CSA15").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T17]) Is Nothing And UCase([T17]) = "YES" Then _
        Sheets("CSA16").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T18]) Is Nothing And UCase([T18]) = "YES" Then _
        Sheets("CSA17").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T19]) Is Nothing And UCase([T19]) = "YES" Then _
        Sheets("CSA18").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T20]) Is Nothing And UCase([T20]) = "YES" Then _
        Sheets("CSA19").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T21]) Is Nothing And UCase([T21]) = "YES" Then _
        Sheets("CSA20").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T22]) Is Nothing And UCase([T22]) = "YES" Then _
        Sheets("CSA21").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T23]) Is Nothing And UCase([T23]) = "YES" Then _
        Sheets("CSA22").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T24]) Is Nothing And UCase([T24]) = "YES" Then _
        Sheets("CSA23").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T25]) Is Nothing And UCase([T25]) = "YES" Then _
        Sheets("CSA24").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T26]) Is Nothing And UCase([T26]) = "YES" Then _
        Sheets("CSA25").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T27]) Is Nothing And UCase([T27]) = "YES" Then _
        Sheets("CSA26").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T28]) Is Nothing And UCase([T28]) = "YES" Then _
        Sheets("CSA27").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T29]) Is Nothing And UCase([T29]) = "YES" Then _
        Sheets("CSA28").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T30]) Is Nothing And UCase([T30]) = "YES" Then _
        Sheets("CSA29").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T31]) Is Nothing And UCase([T31]) = "YES" Then _
        Sheets("CSA30").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T32]) Is Nothing And UCase([T32]) = "YES" Then _
        Sheets("CSA31").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T33]) Is Nothing And UCase([T33]) = "YES" Then _
        Sheets("CSA32").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T34]) Is Nothing And UCase([T34]) = "YES" Then _
        Sheets("CSA33").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T35]) Is Nothing And UCase([T35]) = "YES" Then _
        Sheets("CSA34").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T36]) Is Nothing And UCase([T36]) = "YES" Then _
        Sheets("CSA35").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T37]) Is Nothing And UCase([T37]) = "YES" Then _
        Sheets("CSA36").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T38]) Is Nothing And UCase([T38]) = "YES" Then _
        Sheets("CSA37").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T39]) Is Nothing And UCase([T39]) = "YES" Then _
        Sheets("CSA38").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T40]) Is Nothing And UCase([T40]) = "YES" Then _
        Sheets("CSA39").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T41]) Is Nothing And UCase([T41]) = "YES" Then _
        Sheets("CSA40").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T42]) Is Nothing And UCase([T42]) = "YES" Then _
        Sheets("CSA41").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T43]) Is Nothing And UCase([T43]) = "YES" Then _
        Sheets("CSA42").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T44]) Is Nothing And UCase([T44]) = "YES" Then _
        Sheets("CSA43").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T45]) Is Nothing And UCase([T45]) = "YES" Then _
        Sheets("CSA44").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T46]) Is Nothing And UCase([T46]) = "YES" Then _
        Sheets("CSA45").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T47]) Is Nothing And UCase([T47]) = "YES" Then _
        Sheets("CSA46").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T48]) Is Nothing And UCase([T48]) = "YES" Then _
        Sheets("CSA47").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T49]) Is Nothing And UCase([T49]) = "YES" Then _
        Sheets("CSA48").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T50]) Is Nothing And UCase([T50]) = "YES" Then _
        Sheets("CSA49").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T51]) Is Nothing And UCase([T51]) = "YES" Then _
        Sheets("CSA50").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T52]) Is Nothing And UCase([T52]) = "YES" Then _
        Sheets("CSA51").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T53]) Is Nothing And UCase([T53]) = "YES" Then _
        Sheets("CSA52").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T54]) Is Nothing And UCase([T54]) = "YES" Then _
        Sheets("CSA53").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T55]) Is Nothing And UCase([T55]) = "YES" Then _
        Sheets("CSA54").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T56]) Is Nothing And UCase([T56]) = "YES" Then _
        Sheets("CSA55").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T57]) Is Nothing And UCase([T57]) = "YES" Then _
        Sheets("CSA56").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T58]) Is Nothing And UCase([T58]) = "YES" Then _
        Sheets("CSA57").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T59]) Is Nothing And UCase([T59]) = "YES" Then _
        Sheets("CSA58").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T60]) Is Nothing And UCase([T60]) = "YES" Then _
        Sheets("CSA59").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T61]) Is Nothing And UCase([T61]) = "YES" Then _
        Sheets("CSA60").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T62]) Is Nothing And UCase([T62]) = "YES" Then _
        Sheets("CSA61").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T63]) Is Nothing And UCase([T63]) = "YES" Then _
        Sheets("CSA62").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T64]) Is Nothing And UCase([T64]) = "YES" Then _
        Sheets("CSA63").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T65]) Is Nothing And UCase([T65]) = "YES" Then _
        Sheets("CSA64").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T66]) Is Nothing And UCase([T66]) = "YES" Then _
        Sheets("CSA65").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T67]) Is Nothing And UCase([T67]) = "YES" Then _
        Sheets("CSA66").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T68]) Is Nothing And UCase([T68]) = "YES" Then _
        Sheets("CSA67").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T69]) Is Nothing And UCase([T69]) = "YES" Then _
        Sheets("CSA68").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T70]) Is Nothing And UCase([T70]) = "YES" Then _
        Sheets("CSA69").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T71]) Is Nothing And UCase([T71]) = "YES" Then _
        Sheets("CSA70").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T72]) Is Nothing And UCase([T72]) = "YES" Then _
        Sheets("CSA71").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T73]) Is Nothing And UCase([T73]) = "YES" Then _
        Sheets("CSA72").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T74]) Is Nothing And UCase([T74]) = "YES" Then _
        Sheets("CSA73").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T75]) Is Nothing And UCase([T75]) = "YES" Then _
        Sheets("CSA74").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T76]) Is Nothing And UCase([T76]) = "YES" Then _
        Sheets("CSA75").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T77]) Is Nothing And UCase([T77]) = "YES" Then _
        Sheets("CSA76").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T78]) Is Nothing And UCase([T78]) = "YES" Then _
        Sheets("CSA77").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T79]) Is Nothing And UCase([T79]) = "YES" Then _
        Sheets("CSA78").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T80]) Is Nothing And UCase([T80]) = "YES" Then _
        Sheets("CSA79").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T81]) Is Nothing And UCase([T81]) = "YES" Then _
        Sheets("CSA80").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T82]) Is Nothing And UCase([T82]) = "YES" Then _
        Sheets("CSA81").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T83]) Is Nothing And UCase([T83]) = "YES" Then _
        Sheets("CSA82").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T84]) Is Nothing And UCase([T84]) = "YES" Then _
        Sheets("CSA83").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T85]) Is Nothing And UCase([T85]) = "YES" Then _
        Sheets("CSA84").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T86]) Is Nothing And UCase([T86]) = "YES" Then _
        Sheets("CSA85").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T87]) Is Nothing And UCase([T87]) = "YES" Then _
        Sheets("CSA86").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T88]) Is Nothing And UCase([T88]) = "YES" Then _
        Sheets("CSA87").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T89]) Is Nothing And UCase([T89]) = "YES" Then _
        Sheets("CSA88").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T90]) Is Nothing And UCase([T90]) = "YES" Then _
        Sheets("CSA89").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T91]) Is Nothing And UCase([T91]) = "YES" Then _
        Sheets("CSA90").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T92]) Is Nothing And UCase([T92]) = "YES" Then _
        Sheets("CSA91").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T93]) Is Nothing And UCase([T93]) = "YES" Then _
        Sheets("CSA92").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T94]) Is Nothing And UCase([T94]) = "YES" Then _
        Sheets("CSA93").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T95]) Is Nothing And UCase([T95]) = "YES" Then _
        Sheets("CSA94").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T96]) Is Nothing And UCase([T96]) = "YES" Then _
        Sheets("CSA95").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T97]) Is Nothing And UCase([T97]) = "YES" Then _
        Sheets("CSA96").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T98]) Is Nothing And UCase([T98]) = "YES" Then _
        Sheets("CSA97").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T99]) Is Nothing And UCase([T99]) = "YES" Then _
        Sheets("CSA98").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T100]) Is Nothing And UCase([T100]) = "YES" Then _
        Sheets("CSA99").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T101]) Is Nothing And UCase([T101]) = "YES" Then _
        Sheets("CSA100").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T102]) Is Nothing And UCase([T102]) = "YES" Then _
        Sheets("CSA101").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T103]) Is Nothing And UCase([T103]) = "YES" Then _
        Sheets("CSA102").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T104]) Is Nothing And UCase([T104]) = "YES" Then _
        Sheets("CSA103").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T105]) Is Nothing And UCase([T105]) = "YES" Then _
        Sheets("CSA104").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T106]) Is Nothing And UCase([T106]) = "YES" Then _
        Sheets("CSA105").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T107]) Is Nothing And UCase([T107]) = "YES" Then _
        Sheets("CSA106").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T108]) Is Nothing And UCase([T108]) = "YES" Then _
        Sheets("CSA107").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T109]) Is Nothing And UCase([T109]) = "YES" Then _
        Sheets("CSA108").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T110]) Is Nothing And UCase([T110]) = "YES" Then _
        Sheets("CSA109").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T111]) Is Nothing And UCase([T111]) = "YES" Then _
        Sheets("CSA110").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T112]) Is Nothing And UCase([T112]) = "YES" Then _
        Sheets("CSA111").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T113]) Is Nothing And UCase([T113]) = "YES" Then _
        Sheets("CSA112").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T114]) Is Nothing And UCase([T114]) = "YES" Then _
        Sheets("CSA113").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T115]) Is Nothing And UCase([T115]) = "YES" Then _
        Sheets("CSA114").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T116]) Is Nothing And UCase([T116]) = "YES" Then _
        Sheets("CSA115").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T117]) Is Nothing And UCase([T117]) = "YES" Then _
        Sheets("CSA116").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T118]) Is Nothing And UCase([T118]) = "YES" Then _
        Sheets("CSA117").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T119]) Is Nothing And UCase([T119]) = "YES" Then _
        Sheets("CSA118").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T120]) Is Nothing And UCase([T120]) = "YES" Then _
        Sheets("CSA119").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T121]) Is Nothing And UCase([T121]) = "YES" Then _
        Sheets("CSA120").Range("A3:A122,a124:a153").ClearContents
    End If

  2. #12
    Board Regular
    Join Date
    Sep 2018
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation in VBA Code

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, [T2]) Is Nothing And UCase([T2]) = "YES" Then _
        Sheets("CSA1").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T3]) Is Nothing And UCase([T3]) = "YES" Then _
        Sheets("CSA2").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T4]) Is Nothing And UCase([T4]) = "YES" Then
        Sheets("CSA3").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T5]) Is Nothing And UCase([T5]) = "YES" Then _
        Sheets("CSA4").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T6]) Is Nothing And UCase([T6]) = "YES" Then _
        Sheets("CSA5").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T7]) Is Nothing And UCase([T7]) = "YES" Then _
        Sheets("CSA6").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T8]) Is Nothing And UCase([T8]) = "YES" Then _
        Sheets("CSA7").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T9]) Is Nothing And UCase([T9]) = "YES" Then _
        Sheets("CSA8").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T10]) Is Nothing And UCase([T10]) = "YES" Then _
        Sheets("CSA9").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T11]) Is Nothing And UCase([T11]) = "YES" Then _
        Sheets("CSA10").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T12]) Is Nothing And UCase([T12]) = "YES" Then _
        Sheets("CSA11").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T13]) Is Nothing And UCase([T13]) = "YES" Then _
        Sheets("CSA12").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T14]) Is Nothing And UCase([T14]) = "YES" Then _
        Sheets("CSA13").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T15]) Is Nothing And UCase([T15]) = "YES" Then _
        Sheets("CSA14").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T16]) Is Nothing And UCase([T16]) = "YES" Then _
        Sheets("CSA15").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T17]) Is Nothing And UCase([T17]) = "YES" Then _
        Sheets("CSA16").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T18]) Is Nothing And UCase([T18]) = "YES" Then _
        Sheets("CSA17").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T19]) Is Nothing And UCase([T19]) = "YES" Then _
        Sheets("CSA18").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T20]) Is Nothing And UCase([T20]) = "YES" Then _
        Sheets("CSA19").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T21]) Is Nothing And UCase([T21]) = "YES" Then _
        Sheets("CSA20").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T22]) Is Nothing And UCase([T22]) = "YES" Then _
        Sheets("CSA21").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T23]) Is Nothing And UCase([T23]) = "YES" Then _
        Sheets("CSA22").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T24]) Is Nothing And UCase([T24]) = "YES" Then _
        Sheets("CSA23").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T25]) Is Nothing And UCase([T25]) = "YES" Then _
        Sheets("CSA24").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T26]) Is Nothing And UCase([T26]) = "YES" Then _
        Sheets("CSA25").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T27]) Is Nothing And UCase([T27]) = "YES" Then _
        Sheets("CSA26").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T28]) Is Nothing And UCase([T28]) = "YES" Then _
        Sheets("CSA27").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T29]) Is Nothing And UCase([T29]) = "YES" Then _
        Sheets("CSA28").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T30]) Is Nothing And UCase([T30]) = "YES" Then _
        Sheets("CSA29").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T31]) Is Nothing And UCase([T31]) = "YES" Then _
        Sheets("CSA30").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T32]) Is Nothing And UCase([T32]) = "YES" Then _
        Sheets("CSA31").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T33]) Is Nothing And UCase([T33]) = "YES" Then _
        Sheets("CSA32").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T34]) Is Nothing And UCase([T34]) = "YES" Then _
        Sheets("CSA33").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T35]) Is Nothing And UCase([T35]) = "YES" Then _
        Sheets("CSA34").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T36]) Is Nothing And UCase([T36]) = "YES" Then _
        Sheets("CSA35").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T37]) Is Nothing And UCase([T37]) = "YES" Then _
        Sheets("CSA36").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T38]) Is Nothing And UCase([T38]) = "YES" Then _
        Sheets("CSA37").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T39]) Is Nothing And UCase([T39]) = "YES" Then _
        Sheets("CSA38").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T40]) Is Nothing And UCase([T40]) = "YES" Then _
        Sheets("CSA39").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T41]) Is Nothing And UCase([T41]) = "YES" Then _
        Sheets("CSA40").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T42]) Is Nothing And UCase([T42]) = "YES" Then _
        Sheets("CSA41").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T43]) Is Nothing And UCase([T43]) = "YES" Then _
        Sheets("CSA42").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T44]) Is Nothing And UCase([T44]) = "YES" Then _
        Sheets("CSA43").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T45]) Is Nothing And UCase([T45]) = "YES" Then _
        Sheets("CSA44").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T46]) Is Nothing And UCase([T46]) = "YES" Then _
        Sheets("CSA45").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T47]) Is Nothing And UCase([T47]) = "YES" Then _
        Sheets("CSA46").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T48]) Is Nothing And UCase([T48]) = "YES" Then _
        Sheets("CSA47").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T49]) Is Nothing And UCase([T49]) = "YES" Then _
        Sheets("CSA48").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T50]) Is Nothing And UCase([T50]) = "YES" Then _
        Sheets("CSA49").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T51]) Is Nothing And UCase([T51]) = "YES" Then _
        Sheets("CSA50").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T52]) Is Nothing And UCase([T52]) = "YES" Then _
        Sheets("CSA51").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T53]) Is Nothing And UCase([T53]) = "YES" Then _
        Sheets("CSA52").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T54]) Is Nothing And UCase([T54]) = "YES" Then _
        Sheets("CSA53").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T55]) Is Nothing And UCase([T55]) = "YES" Then _
        Sheets("CSA54").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T56]) Is Nothing And UCase([T56]) = "YES" Then _
        Sheets("CSA55").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T57]) Is Nothing And UCase([T57]) = "YES" Then _
        Sheets("CSA56").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T58]) Is Nothing And UCase([T58]) = "YES" Then _
        Sheets("CSA57").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T59]) Is Nothing And UCase([T59]) = "YES" Then _
        Sheets("CSA58").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T60]) Is Nothing And UCase([T60]) = "YES" Then _
        Sheets("CSA59").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T61]) Is Nothing And UCase([T61]) = "YES" Then _
        Sheets("CSA60").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T62]) Is Nothing And UCase([T62]) = "YES" Then _
        Sheets("CSA61").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T63]) Is Nothing And UCase([T63]) = "YES" Then _
        Sheets("CSA62").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T64]) Is Nothing And UCase([T64]) = "YES" Then _
        Sheets("CSA63").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T65]) Is Nothing And UCase([T65]) = "YES" Then _
        Sheets("CSA64").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T66]) Is Nothing And UCase([T66]) = "YES" Then _
        Sheets("CSA65").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T67]) Is Nothing And UCase([T67]) = "YES" Then _
        Sheets("CSA66").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T68]) Is Nothing And UCase([T68]) = "YES" Then _
        Sheets("CSA67").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T69]) Is Nothing And UCase([T69]) = "YES" Then _
        Sheets("CSA68").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T70]) Is Nothing And UCase([T70]) = "YES" Then _
        Sheets("CSA69").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T71]) Is Nothing And UCase([T71]) = "YES" Then _
        Sheets("CSA70").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T72]) Is Nothing And UCase([T72]) = "YES" Then _
        Sheets("CSA71").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T73]) Is Nothing And UCase([T73]) = "YES" Then _
        Sheets("CSA72").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T74]) Is Nothing And UCase([T74]) = "YES" Then _
        Sheets("CSA73").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T75]) Is Nothing And UCase([T75]) = "YES" Then _
        Sheets("CSA74").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T76]) Is Nothing And UCase([T76]) = "YES" Then _
        Sheets("CSA75").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T77]) Is Nothing And UCase([T77]) = "YES" Then _
        Sheets("CSA76").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T78]) Is Nothing And UCase([T78]) = "YES" Then _
        Sheets("CSA77").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T79]) Is Nothing And UCase([T79]) = "YES" Then _
        Sheets("CSA78").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T80]) Is Nothing And UCase([T80]) = "YES" Then _
        Sheets("CSA79").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T81]) Is Nothing And UCase([T81]) = "YES" Then _
        Sheets("CSA80").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T82]) Is Nothing And UCase([T82]) = "YES" Then _
        Sheets("CSA81").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T83]) Is Nothing And UCase([T83]) = "YES" Then _
        Sheets("CSA82").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T84]) Is Nothing And UCase([T84]) = "YES" Then _
        Sheets("CSA83").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T85]) Is Nothing And UCase([T85]) = "YES" Then _
        Sheets("CSA84").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T86]) Is Nothing And UCase([T86]) = "YES" Then _
        Sheets("CSA85").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T87]) Is Nothing And UCase([T87]) = "YES" Then _
        Sheets("CSA86").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T88]) Is Nothing And UCase([T88]) = "YES" Then _
        Sheets("CSA87").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T89]) Is Nothing And UCase([T89]) = "YES" Then _
        Sheets("CSA88").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T90]) Is Nothing And UCase([T90]) = "YES" Then _
        Sheets("CSA89").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T91]) Is Nothing And UCase([T91]) = "YES" Then _
        Sheets("CSA90").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T92]) Is Nothing And UCase([T92]) = "YES" Then _
        Sheets("CSA91").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T93]) Is Nothing And UCase([T93]) = "YES" Then _
        Sheets("CSA92").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T94]) Is Nothing And UCase([T94]) = "YES" Then _
        Sheets("CSA93").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T95]) Is Nothing And UCase([T95]) = "YES" Then _
        Sheets("CSA94").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T96]) Is Nothing And UCase([T96]) = "YES" Then _
        Sheets("CSA95").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T97]) Is Nothing And UCase([T97]) = "YES" Then _
        Sheets("CSA96").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T98]) Is Nothing And UCase([T98]) = "YES" Then _
        Sheets("CSA97").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T99]) Is Nothing And UCase([T99]) = "YES" Then _
        Sheets("CSA98").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T100]) Is Nothing And UCase([T100]) = "YES" Then _
        Sheets("CSA99").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T101]) Is Nothing And UCase([T101]) = "YES" Then _
        Sheets("CSA100").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T102]) Is Nothing And UCase([T102]) = "YES" Then _
        Sheets("CSA101").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T103]) Is Nothing And UCase([T103]) = "YES" Then _
        Sheets("CSA102").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T104]) Is Nothing And UCase([T104]) = "YES" Then _
        Sheets("CSA103").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T105]) Is Nothing And UCase([T105]) = "YES" Then _
        Sheets("CSA104").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T106]) Is Nothing And UCase([T106]) = "YES" Then _
        Sheets("CSA105").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T107]) Is Nothing And UCase([T107]) = "YES" Then _
        Sheets("CSA106").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T108]) Is Nothing And UCase([T108]) = "YES" Then _
        Sheets("CSA107").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T109]) Is Nothing And UCase([T109]) = "YES" Then _
        Sheets("CSA108").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T110]) Is Nothing And UCase([T110]) = "YES" Then _
        Sheets("CSA109").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T111]) Is Nothing And UCase([T111]) = "YES" Then _
        Sheets("CSA110").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T112]) Is Nothing And UCase([T112]) = "YES" Then _
        Sheets("CSA111").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T113]) Is Nothing And UCase([T113]) = "YES" Then _
        Sheets("CSA112").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T114]) Is Nothing And UCase([T114]) = "YES" Then _
        Sheets("CSA113").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T115]) Is Nothing And UCase([T115]) = "YES" Then _
        Sheets("CSA114").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T116]) Is Nothing And UCase([T116]) = "YES" Then _
        Sheets("CSA115").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T117]) Is Nothing And UCase([T117]) = "YES" Then _
        Sheets("CSA116").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T118]) Is Nothing And UCase([T118]) = "YES" Then _
        Sheets("CSA117").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T119]) Is Nothing And UCase([T119]) = "YES" Then _
        Sheets("CSA118").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T120]) Is Nothing And UCase([T120]) = "YES" Then _
        Sheets("CSA119").Range("A3:A122,a124:a153").ClearContents
    If Not Intersect(Target, [T121]) Is Nothing And UCase([T121]) = "YES" Then _
        Sheets("CSA120").Range("A3:A122,a124:a153").ClearContents
    End If

  3. #13
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Data Validation in VBA Code

    That needs streamlining!

  4. #14
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Data Validation in VBA Code

    Firstly disable both subs named Worksheet_Change by renaming them differently
    eg
    Private Sub Xorksheet_Change(ByVal Target As Range)
    Private Sub Zorksheet_Change(ByVal Target As Range)

    Secondly test replacement for the last one you posted

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("T1:T121")) Is Nothing Then
            Const Rng = "A3:A122,a124:a153"
            Dim Sh As Worksheet
            Set Sh = Sheets("CSA" & (Target.Row - 1))
            If UCase(Target) = "YES" Then Sh.Range(Rng).ClearContents
        End If
    End Sub

  5. #15
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Data Validation in VBA Code

    Thirdly

    My earlier sub modified to make it consistent with the one posted in post#14
    - I think target range should be rows 2 to 121 (like the other one)
    - I assume that your DV forces user to put in integers between 1 and 150
    - VBA slimmed it down to avoid repeating code

    You may want to test it - remember to disable the other one with same name
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("D2:D121")) Is Nothing Then
            Dim msg As String
            Application.EnableEvents = False
            With Target
                If UCase(.Offset(, -3)) = "YES" And .Value > 120 Then msg = "Please select a Value 120 or lower"
                If UCase(.Offset(, -3)) = "NO" And .Value < 121 Then msg = "Please select a value 121 or higher"
                    If msg <> "" Then
                        .ClearContents
                        MsgBox "The value entered dose not meet the requirement " & msg, vbCritical, "Error"
                        .Select
                    End If
            End With
            Application.EnableEvents = True
        End If
    End Sub

  6. #16
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Data Validation in VBA Code

    Finally

    Combine like this and delete all the other versions!

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("T1:T121")) Is Nothing Then
            Const Rng = "A3:A122,a124:a153"
            Dim Sh As Worksheet
            Set Sh = Sheets("CSA" & (Target.Row - 1))
            If UCase(Target) = "YES" Then Sh.Range(Rng).ClearContents
        End If
    
        If Not Intersect(Target, Range("D2:D121")) Is Nothing Then
            Dim msg As String
            Application.EnableEvents = False
            With Target
                If UCase(.Offset(, -3)) = "YES" And .Value > 120 Then msg = "Please select a Value 120 or lower"
                If UCase(.Offset(, -3)) = "NO" And .Value < 121 Then msg = "Please select a value 121 or higher"
                    If msg <> "" Then
                        .ClearContents
                        MsgBox "The value entered dose not meet the requirement " & msg, vbCritical, "Error"
                        .Select
                    End If
            End With
            Application.EnableEvents = True
        End If
    End Sub

  7. #17
    Board Regular
    Join Date
    Sep 2018
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation in VBA Code

    The code to Clear CSA sheets works great and much effective.

    For the Second IF statement How does it check Column A for "YES" or "NO" because its still allowing any values and not .ClearContents or populating the Error Msg.


    Man I really Really Appreciate you taking the time help me.

    Code:
        If Not Intersect(Target, Range("D2:D121")) Is Nothing Then
            Dim msg As String
            Application.EnableEvents = False
            With Target
                If UCase(.Offset(, -3)) = "YES" And .Value > 120 Then msg = "Please select a Value 120 or lower"
                If UCase(.Offset(, -3)) = "NO" And .Value < 121 Then msg = "Please select a value 121 or higher"
                    If msg <> "" Then
                        .ClearContents
                        MsgBox "The value entered dose not meet the requirement " & msg, vbCritical, "Error"
                        .Select
                    End If
            End With
            Application.EnableEvents = True
        End If
    End Sub
    [/QUOTE]

  8. #18
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Data Validation in VBA Code

    It works for me
    - something is different in your sheet or you are expecting something different to what I am expecting to happen
    - you confirmed that the code was working correctly originally and it is doing EXACTLY what it was doing then (just looks a little different)
    - originally when value in D1 was changed it looked to cell A1 to see if yes or no before testing for over and under 120

    EXPLANATION

    Macro is triggered when any cell in range D2:D121 is changed. So if the cell being amended is D4 then TARGET = Range("D4") ...

    Code:
    With Target
    If UCase(.Offset(, -3)) = "YES" And .Value > 120 Then msg = "Please select a Value 120 or lower"
    is same as ...
    Code:
    If UCase(TARGET.Offset(, -3)) = "YES" And TARGET.Value > 120 Then ...
    and Target is D4, so is same as ...
    Code:
    If UCase(Range("D4").Offset(, -3)) = "YES" And Range("D4").Value > 120 Then ...
    and D4 offset 3 columns to the left is A4 ...
    Code:
    If UCase(Range("A4")) = "YES" And Range("D4").Value > 120 Then ...
    Q1. Is there only ONE macro named Worksheet_Change and is it in the correct sheet module?

    Q2. I have already asked this question previously WHAT IS YOUR DATA VALIDATION RULE IN COLUMN D? Is user forced to select a value?

    Q3. Is there anything incorrect in my logic?
    Last edited by Yongle; Mar 13th, 2019 at 02:17 PM.

  9. #19
    Board Regular
    Join Date
    Sep 2018
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation in VBA Code

    Q1. Is there only ONE macro named Worksheet_Change and is it in the correct sheet module? YES
    Q2. I have already asked this question previously WHAT IS YOUR DATA VALIDATION RULE IN COLUMN D? Is user forced to select a value?
    CODE IN DV IS:
    Code:
    =COUNTIF($E$2:E2,D5)=0
    Than dragged it to D121 so that numbers in a cell are not duplicate form any previous cell in column E


    Q3. Is there anything incorrect in my logic?
    I started a Brand New Workbook and inputted the code in the Sheet1 (Sheet1) under Microsoft Excel Object and Still not working. I Certain that the code you gave is correct just do not know whats going on.

  10. #20
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Data Validation in VBA Code

    =COUNTIF($E$2:E2,D5)=0

    What is in column E?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •