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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top