Hello Gurus,
I have referred to following for incorporating date picker in my userform:
I have made a userform, which is meant to gather data of some chemicals, where user need to put manufacturing date using date picker and enter shelf life details. Expiry is calculated based on the input in shelf life and manufacturing date. So, whenever I enter date 1st of Mar'2023 i.e. 01-03-2023 and shelf life 1 months, I get output expiry date as 02-02-2023 instead of 01-04-2023.
My code is as follows:
Let me know where I might be wrong.
I have referred to following for incorporating date picker in my userform:
Fully Customizable VBA Date Picker
I recently created a customizable, VBA-based date picker, and thought it might be of use to someone else. I know there are already a lot of custom date pickers out there to overcome the difficulties of using Microsoft's MonthView control. But I wanted one that could be customized to match the...
chandoo.org
I have made a userform, which is meant to gather data of some chemicals, where user need to put manufacturing date using date picker and enter shelf life details. Expiry is calculated based on the input in shelf life and manufacturing date. So, whenever I enter date 1st of Mar'2023 i.e. 01-03-2023 and shelf life 1 months, I get output expiry date as 02-02-2023 instead of 01-04-2023.
My code is as follows:
Code:
Private Sub cmdSave_Click()
Dim lRow As Long
Dim c As String, p As String, f As String,
Dim e As Date
Dim ws As Worksheet
Set ws = Worksheets("Database")
'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
p = frmdatainput4.txtshlflf.value ' numbers 1 to x'
c = frmdatainput4.cmblfunit.value ' getting unit eg.month, year or days'
e = frmdatainput4.txtmfgdt.value 'getting manufacting date'
If c = "Months" Then
f = Format(DateAdd("m", p, e) - 1, "DD-MM-YYYY")
MsgBox "Modified date is " & f
ElseIf c = "Days" Then
f = Format(DateAdd("d", p, e), "DD-MM-YYYY")
ElseIf c = "Weeks" Then
f = Format(DateAdd("ww", p, e) - 1, "DD-MM-YYYY")
ElseIf c = "Years" Then
f = Format(DateAdd("yyyy", p, e) - 1, "DD-MM-YYYY")
Else
f = Format(e, "DD-MM-YYYY")
End If
With ws
.Unprotect Password:="123"
.Cells(lRow, 1).value = frmdatainput4.cmbreagent.value
.Cells(lRow, 2).value = frmdatainput4.txtmfgdt.value
.Cells(lRow, 3).value = f
.Protect Password:="123"
End With
'clear the data
frmdatainput4.cmbreagent.value = ""
frmdatainput4.txtmfgdt.value = ""
frmdatainput4.txtshlflf.value = ""
frmdatainput4.cmblfunit.value = ""
End Sub
Let me know where I might be wrong.