Excel VBA Userform - How to increase/decrease date by one month -
using following code, increase value of date displayed in textbox spinbuttons.
mainuserform_initialize() datetextbox.value = format(date, "dd-mm-yyyy") ... end sub 'add 1 day when spinning up. private sub spinbuttondate1_spinup() datetextbox .value = format(dateadd("d", 1, .value), "dd-mm-yyyy") end end sub 'remove 1 day when spinning down. private sub spinbuttondate1_spindown() datetextbox .value = format(dateadd("d", -1, .value), "dd-mm-yyyy") end end sub
the problem is, if spin down 11-07-2015, goes 12-07-2015 06-12-2015 instead of 11-07-2015. why that? (i have same problem when increasing value. dates given example.)
i have same issue if set regional format us. try fix should work regardless of regional settings:
'add 1 day when spinning up. private sub spinbuttondate1_spinup() datetextbox .value = format(getdatefromuk(.value) + 1, "dd-mm-yyyy") end end sub 'remove 1 day when spinning down. private sub spinbuttondate1_spindown() datetextbox .value = format(getdatefromuk(.value) - 1, "dd-mm-yyyy") end end sub function getdatefromuk(sdate string, optional sseparator string = "-") date dim vparts vparts = split(sdate, sseparator) getdatefromuk = dateserial(vparts(2), vparts(1), vparts(0)) end function
Comments
Post a Comment