VBA that runs automatically when open

pretzel

New Member
Joined
Oct 2, 2014
Messages
18
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 Auto_Open() in normal modules but it isn't working. My script as below:

Sub Auto_Open()
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!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,405
Office Version
  1. 365
Platform
  1. Windows
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.
Why not? You should be able to add more code to your current Workbook_Open event. It is not relegated to just doing a single task.
If it makes you more comfortable to segregate them, but both of your tasks in separate procedures, and simply call them from your Workbook_Open event, i.e.
Code:
Private Sub Workbook_Open()
    Call Task1
    Call Task2
End Sub


Sub Task1()
'   vba code here
End Sub


Sub Task2()
'   vba code here
End Sub
 

pretzel

New Member
Joined
Oct 2, 2014
Messages
18
Thank you Joe. It works!

Now that my macro below is running, I want to enhance it such that it will check every single cell after A5 and down. And it will tell me exacty which item, correspondingly on column B that is running low. I am thinking of using TempArray(A,i) where Dim i as Integer. I don't know how to script. Any hints? I'll probably do it on Saturday morning. :)

Sub Autorunchecks()

With Sheets("Sheet1")
.Select

Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer
Dim dtmNewDate As Date

intYear = DatePart("yyyy", Range("A5"))
intMonth = DatePart("m", Range("A5"))
intDay = DatePart("d", Range("A5"))

dtmNewDate = DateSerial(intYear, intMonth, intDay)

today = Date

If dtmNewDate = today Then

MsgBox "Update xxx inventory!"
Else
Calculate
End If
End With

End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,405
Office Version
  1. 365
Platform
  1. Windows
If you want to loop through a range of cells starting in A5 and going down until you find a blank cell in column A, you can do that like this:
Code:
Dim cell as Range
For each cell in Range(Range("A5"), Range("A5").End(xlDown))
    ...
Next cell
 

pretzel

New Member
Joined
Oct 2, 2014
Messages
18

ADVERTISEMENT

If you want to loop through a range of cells starting in A5 and going down until you find a blank cell in column A, you can do that like this:
Code:
Dim cell as Range
For each cell in Range(Range("A5"), Range("A5").End(xlDown))
    ...
Next cell


Hi Joe,

I ketp getting errors such as: Next without For Or Object missing. Any advice?






Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer
Dim dtmNewDate As Date

dtmNewDate = DateSerial(intYear, intMonth, intDay)

intYear = DatePart("yyyy", Range("A5"))
intMonth = DatePart("m", Range("A5"))
intDay = DatePart("d", Range("A5"))

Dim cell As Range
Set cell.Value = dtmNewDate
today = Date

For Each cell In Range(Range("A5"), Range("A5").End(xlDown))
If cell = today Then
MsgBox "Update inventory!"
Else
Next cell

End If
End Sub

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,405
Office Version
  1. 365
Platform
  1. Windows
You have your END IF line after the NEXT line.
Since your IF statement starts inside of your FOR/NEXT loop, it has to end inside of the FOR/NEXT loop.
 

pretzel

New Member
Joined
Oct 2, 2014
Messages
18

ADVERTISEMENT

Thank you very much! It works!

I realized there is no need to define the whole range into the specific integer when I have declared Range(Range("A5"), Range("A5").End(xlDown)). Is that always the case in VBA?


My final code:
.
.
.

For Each cell In Range(Range("A5"), Range("A5").End(xlDown))

intYear = DatePart("yyyy", Range("A5"))
intMonth = DatePart("m", Range("A5"))
intDay = DatePart("d", Range("A5"))
dtmNewDate = DateSerial(intYear, intMonth, intDay)

today = Date
If cell = today Then
MsgBox "Update inventory!"




How do I improve my VBA skills? It is tough and I don't feel that I'm progressing at all. I feel that I'm grabbing bits here and there.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,405
Office Version
  1. 365
Platform
  1. Windows
I am not quite sure exactly what you are trying to do/accomplish here:
Code:
[COLOR=#0000CD]For Each cell In Range(Range("A5"), Range("A5").End(xlDown))[/COLOR]

[COLOR=#0000CD]intYear = DatePart("yyyy", Range("A5"))[/COLOR]
[COLOR=#0000CD]intMonth = DatePart("m", Range("A5"))[/COLOR]
[COLOR=#0000CD]intDay = DatePart("d", Range("A5"))[/COLOR]
[COLOR=#0000CD]dtmNewDate = DateSerial(intYear, intMonth, intDay)[/COLOR]

[COLOR=#0000CD]today = Date[/COLOR]
[COLOR=#0000CD]If cell = today Then[/COLOR]
[COLOR=#0000CD]MsgBox "Update inventory!"[/COLOR]
Basically, you would be looping through each cell in the Range("A5") down to the first blank in column A, but in every step of the loop, you are pulling the values of A5 into of the current cell.

Also, why are you pulling out the values of the date, only to put them back together again in your dtmNewDate variable. It seems totally unnecessary.

Maybe it will make more sense if you can explain exactly what you are trying to accomplish with this block of code.
 

pretzel

New Member
Joined
Oct 2, 2014
Messages
18
I am not quite sure exactly what you are trying to do/accomplish here:
Code:
[COLOR=#0000CD]For Each cell In Range(Range("A5"), Range("A5").End(xlDown))[/COLOR]

[COLOR=#0000CD]intYear = DatePart("yyyy", Range("A5"))[/COLOR]
[COLOR=#0000CD]intMonth = DatePart("m", Range("A5"))[/COLOR]
[COLOR=#0000CD]intDay = DatePart("d", Range("A5"))[/COLOR]
[COLOR=#0000CD]dtmNewDate = DateSerial(intYear, intMonth, intDay)[/COLOR]

[COLOR=#0000CD]today = Date[/COLOR]
[COLOR=#0000CD]If cell = today Then[/COLOR]
[COLOR=#0000CD]MsgBox "Update inventory!"[/COLOR]
Basically, you would be looping through each cell in the Range("A5") down to the first blank in column A, but in every step of the loop, you are pulling the values of A5 into of the current cell.

Also, why are you pulling out the values of the date, only to put them back together again in your dtmNewDate variable. It seems totally unnecessary.

Maybe it will make more sense if you can explain exactly what you are trying to accomplish with this block of code.


Oh the code is not completed yet. I'm just taking baby steps to write the code from scratch whilst learning. I have a list of items with the names on column D and the respective expiry dates on column A. So I would like to have their names reflect on MsgBox "update item xyz!" When today=expiry date.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,405
Office Version
  1. 365
Platform
  1. Windows
But do you mean to pull from cell A5 every time, or do you wish to go down the list?
And what is the point of pulling each out into variables (intYear, intMonth, and intDate), only to put them back together in dtmNewDate?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,313
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top