Add Date/Time Validation to Excel's Cell -
i have work schedule have created , place code in there verifies entries proper time format , not text.
i have basic part of code done i'm having difficulty searching through various cells. unfortunately cells 1 big list or easy enough code work. started create multiple ranges , going create statements cycle through figure there must simpler way. i'm new site can't attach image of schedule. can see below though in code various cells in range.
any appreciated.
private sub worksheet_change(byval target range) dim cel range, targ range dim v variant dim daterng range dim emp1a range, emp1b range dim emp2a range, emp2b range dim emp3a range, emp3b range dim emp4a range, emp4b range dim emp5a range, emp5b range dim emp6a range, emp6b range dim emp7a range, emp7b range dim emp8a range, emp8b range dim emp9a range, emp9b range dim emp10a range, emp10b range dim emp11a range, emp11b range dim emp12a range, emp12b range dim emp13a range, emp13b range dim emp14a range, emp14b range dim emp15a range, emp15b range dim emp16a range, emp16b range dim emp17a range, emp17b range dim emp18a range, emp18b range dim emp19a range, emp19b range dim emp20a range, emp20b range dim emp21a range, emp21b range dim emp22a range, emp22b range dim emp23a range, emp23b range dim emp24a range, emp24b range dim emp25a range, emp25b range if target.rows.count >= rows.count exit sub set emp1a = range("d5,e5,h5,i5,l5,m5,p5,q5,t5,u5,x5,y5,ab5,ac5,d6,e6,h6,i6,l6,m6,p6,q6,t6,u6,x6,y6,ab6,ac6,d7,e7,h7,i7,l7,m7,p7,q7,t7,u7,x7,y7,ab7,ac7") set emp1b = range("d9,e9,h9,i9,l9,m9,p9,q9,t9,u9,x9,y9,ab9,ac9,d10,e10,h10,i10,l10,m10,p10,q10,t10,u10,x10,y10,ab10,ac10,d11,e11,h11,i11,l11,m11,p11,q11,t11,u11,x11,y11,ab11,ac11") set emp2a = range("d13,e13,h13,i13,l13,m13,p13,q13,t13,u13,x13,y13,ab13,ac13,d14,e14,h14,i14,l14,m14,p14,q14,t14,u14,x14,y14,ab14,ac14,d15,e15,h15,i15,l15,m15,p15,q15,t15,u15,x15,y15,ab15,ac15") set emp2b = range("d17,e17,h17,i17,l17,m17,p17,q17,t17,u17,x17,y17,ab17,ac17,d18,e18,h18,i18,l18,m18,p18,q18,t18,u18,x18,y18,ab18,ac18,d19,e19,h19,i19,l19,m19,p19,q19,t19,u19,x19,y19,ab19,ac19") set emp3a = range("d25,e25,h25,i25,l25,m25,p25,q25,t25,u25,x25,y25,ab25,ac25,d26,e26,h26,i26,l26,m26,p26,q26,t26,u26,x26,y26,ab26,ac26,d27,e27,h27,i27,l27,m27,p27,q27,t27,u27,x27,y27,ab27,ac27") set emp3b = range("d33,e33,h33,i33,l33,m33,p33,q33,t33,u33,x33,y33,ab33,ac33,d34,e34,h34,i34,l34,m34,p34,q34,t34,u34,x34,y34,ab34,ac34,d35,e35,h35,i35,l35,m35,p35,q35,t35,u35,x35,y35,ab35,ac35") 'watch these cells time entries" set emp1a = intersect(emp1a, target) 'watch these cells time entries if not emp1a nothing application.enableevents = false each cel in emp1a.cells if isnumeric(cel.value) if cel.value > 0 if len(cel.value) < 7 on error resume next v = 0 v = timevalue(format(cel.value, "00:0#")) on error goto 0 if v = 0 cel.select msgbox format(cel.value, "00:0#") & " not permissible time value!" cel.clearcontents end if end if else if cel.value < 0 cel.select msgbox cel.value & " not permissible time value" cel.clearcontents end if end if else cel.select msgbox cel.value & " not permissible time value" cel.clearcontents end if next application.enableevents = true end if end sub
you can add validation object single cell or entire range (e.g. "a1:a10") programmatically using vba shown in following code snippet:
sub addtimevalidation() range("a1:a10").validation .add type:=xlvalidatetime, _ alertstyle:=xlvalidalertstop, _ operator:=xlbetween, formula1:="0:00:00", formula2:="23:59:59" .inputtitle = "time" .errortitle = "time" .inputmessage = "enter valid time" .errormessage = "you must enter enter valid time" end end sub also, can add validation manually (detailed explanation available here: http://office.microsoft.com/en-001/excel-help/apply-data-validation-to-cells-hp010072600.aspx).
hope help. kind regards,
Comments
Post a Comment