Hello peeps, I am so new to VBA and it's giving me quite a challenge.
I need to create a function or perhaps a procedure that will run the check by itself when I open up my workbook. But I already have a Private Sub Workbook_Open() written in my ThisWorkbook object so I don't think adding on to the list works. I have tried Sub Autpen() in normal modules but it isn't working. My script as below:
Sub Autpen()
With Sheets("Sheet1")
.Select
Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer
Dim dtmNewDate As Date
intYear = DatePart("yyyy", Range("A2"))
intMonth = DatePart("m", Range("A2"))
intDay = DatePart("d", Range("A2"))
dtmNewDate = DateSerial(intYear, intMonth, intDay)
Dim nowYear As Integer
Dim nowMonth As Integer
Dim nowDay As Integer
Dim today As Date
nowYear = DatePart("yyyy", Now)
nowYear = DatePart("m", Now)
nowYear = DatePart("d", Now)
today = DateSerial(nowYear, nowMonth, nowDay)
If dtmNewDate = today Then
MsgBox "Update inventory xyz!"
Else
Calculate
End If
End With
End Sub
Basically I have a list of dates from range(A2:A100). So if today's date falls on the same day (preferably checking against only day and month), I want my msgbox to tell me which items to update. I am testing out on my first item.
Please do correct my mistakes or critique my script. It looks a little lengthy.
Thanks!
I need to create a function or perhaps a procedure that will run the check by itself when I open up my workbook. But I already have a Private Sub Workbook_Open() written in my ThisWorkbook object so I don't think adding on to the list works. I have tried Sub Autpen() in normal modules but it isn't working. My script as below:
Sub Autpen()
With Sheets("Sheet1")
.Select
Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer
Dim dtmNewDate As Date
intYear = DatePart("yyyy", Range("A2"))
intMonth = DatePart("m", Range("A2"))
intDay = DatePart("d", Range("A2"))
dtmNewDate = DateSerial(intYear, intMonth, intDay)
Dim nowYear As Integer
Dim nowMonth As Integer
Dim nowDay As Integer
Dim today As Date
nowYear = DatePart("yyyy", Now)
nowYear = DatePart("m", Now)
nowYear = DatePart("d", Now)
today = DateSerial(nowYear, nowMonth, nowDay)
If dtmNewDate = today Then
MsgBox "Update inventory xyz!"
Else
Calculate
End If
End With
End Sub
Basically I have a list of dates from range(A2:A100). So if today's date falls on the same day (preferably checking against only day and month), I want my msgbox to tell me which items to update. I am testing out on my first item.
Please do correct my mistakes or critique my script. It looks a little lengthy.
Thanks!