Code to run dialogue box at workbook startup
 

Howdy,

Can anyone suggest code to run a dialogue box when a workbook opens but
only on the first of every month?

Ta :)

Code to run dialogue box at workbook startup
 

hi
use one buffer cell (Ex AA1 of sheet1)
now see the code below what i mentioned
'below mentioned code you have to paste on your 'thisworkbook' module

Private Sub Workbook_Open()
If Month(Date) < Sheet1.Range("AA1").Value Then
MsgBox "This is the first time you are opening this workbook on this
month"
Sheet1.Range("AA1").Value = Month(Date)
End If
End Sub

please do rate

"Doo0592" wrote:

Howdy,

Can anyone suggest code to run a dialogue box when a workbook opens but
only on the first of every month?

Ta :)

Code to run dialogue box at workbook startup
 

I think Rafeek has misunderstood you slightly!
try this:

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 Then
MsgBox "It's the first of the month"
End If
End Sub

If I did misunderstand you, then I apologize for any
inconvenience.

Cheers
Carlo

"Doo0592" wrote:

Howdy,

Can anyone suggest code to run a dialogue box when a workbook opens but
only on the first of every month?

Ta :)

Code to run dialogue box at workbook startup
 

Thanks folks.

Not sure I understand your code either Rafeek. I get that you are
trying to reference cell AA1 as the date but it didn't seem to execute
properly when I made the date the first of the month... it did work
when there was no date in AA1 at all thou! lol

Carlo.. I think I understand your code. If Now() calls the system date
for today and "DD" the actual date, if DD= the first of the month then
the msgbox will run. Am I right?
Also, is there a way I can test this without having wait till the
beginning of the month? lol (even thou it is only 2 days away!)

Thanks again all xx
Doo

Carlo wrote:

I think Rafeek has misunderstood you slightly!
try this:

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 Then
MsgBox "It's the first of the month"
End If
End Sub

If I did misunderstand you, then I apologize for any
inconvenience.

Cheers
Carlo

"Doo0592" wrote:

Howdy,

Can anyone suggest code to run a dialogue box when a workbook opens but
only on the first of every month?

Ta :)

Code to run dialogue box at workbook startup
 

Actually Rafeek, I think I understand your code now. If I am right it
will only execute once per month? If I am right, is there a way we can
combine your code with Carlo's. I think it will probably annoy my end
users if this msg box executes every time they open the workbook! lol

ta :)

Doo
Doo0592 wrote:

Thanks folks.

Not sure I understand your code either Rafeek. I get that you are
trying to reference cell AA1 as the date but it didn't seem to execute
properly when I made the date the first of the month... it did work
when there was no date in AA1 at all thou! lol

Carlo.. I think I understand your code. If Now() calls the system date
for today and "DD" the actual date, if DD= the first of the month then
the msgbox will run. Am I right?
Also, is there a way I can test this without having wait till the
beginning of the month? lol (even thou it is only 2 days away!)

Thanks again all xx
Doo

Carlo wrote:

I think Rafeek has misunderstood you slightly!
try this:

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 Then
MsgBox "It's the first of the month"
End If
End Sub

If I did misunderstand you, then I apologize for any
inconvenience.

Cheers
Carlo

"Doo0592" wrote:

Howdy,

Can anyone suggest code to run a dialogue box when a workbook opens but
only on the first of every month?

Ta :)

Code to run dialogue box at workbook startup
 

Hi Doo

You're right, now() calls the system date, so,
DD ist the Format for Day.
i.e. format("31.8.2006";"DD") = 31
If you want to test it, just change the one to
the current day.

Ok, let's try to combine those two codes:
(shouldn't be that big a Problem!)
As Rafeek stated, you need a Cell, which you can
alter any time without problems, so take one which
is at the right bottom of your sheet: AA65000 would
be ok i think, or AA1 if you are sure, that this cell
will never be used.
Enter the Date of Yesterday (just needed to be done
once!)

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 and worksheets("test").range("AA65000") <
format(now(), "DD.MM.YYYY") Then
MsgBox "It's the first of the month and first time you open the
workbook today"
sheet("test").range("AA65000").formular1c1 = format(now),
"DD.MM.YYYY")
End If
End Sub

you have to change the sheetname "test", the dateformat to your preferred
format and the cell to the cell you want to use!

Hope i could help

Cheers Carlo

"Doo0592" wrote:

Actually Rafeek, I think I understand your code now. If I am right it
will only execute once per month? If I am right, is there a way we can
combine your code with Carlo's. I think it will probably annoy my end
users if this msg box executes every time they open the workbook! lol

ta :)

Doo
Doo0592 wrote:

Thanks folks.

Not sure I understand your code either Rafeek. I get that you are
trying to reference cell AA1 as the date but it didn't seem to execute
properly when I made the date the first of the month... it did work
when there was no date in AA1 at all thou! lol

Carlo.. I think I understand your code. If Now() calls the system date
for today and "DD" the actual date, if DD= the first of the month then
the msgbox will run. Am I right?
Also, is there a way I can test this without having wait till the
beginning of the month? lol (even thou it is only 2 days away!)

Thanks again all xx
Doo

Carlo wrote:

I think Rafeek has misunderstood you slightly!
try this:

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 Then
MsgBox "It's the first of the month"
End If
End Sub

If I did misunderstand you, then I apologize for any
inconvenience.

Cheers
Carlo

"Doo0592" wrote:

Howdy,

Can anyone suggest code to run a dialogue box when a workbook opens but
only on the first of every month?

Ta :)

Code to run dialogue box at workbook startup
 

Thanks Carlo :)

Think I have it now x

Carlo wrote:
Hi Doo

You're right, now() calls the system date, so,
DD ist the Format for Day.
i.e. format("31.8.2006";"DD") = 31
If you want to test it, just change the one to
the current day.

Ok, let's try to combine those two codes:
(shouldn't be that big a Problem!)
As Rafeek stated, you need a Cell, which you can
alter any time without problems, so take one which
is at the right bottom of your sheet: AA65000 would
be ok i think, or AA1 if you are sure, that this cell
will never be used.
Enter the Date of Yesterday (just needed to be done
once!)

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 and worksheets("test").range("AA65000") <
format(now(), "DD.MM.YYYY") Then
MsgBox "It's the first of the month and first time you open the
workbook today"
sheet("test").range("AA65000").formular1c1 = format(now),
"DD.MM.YYYY")
End If
End Sub

you have to change the sheetname "test", the dateformat to your preferred
format and the cell to the cell you want to use!

Hope i could help

Cheers Carlo

"Doo0592" wrote:

Actually Rafeek, I think I understand your code now. If I am right it
will only execute once per month? If I am right, is there a way we can
combine your code with Carlo's. I think it will probably annoy my end
users if this msg box executes every time they open the workbook! lol

ta :)

Doo
Doo0592 wrote:

Thanks folks.

Not sure I understand your code either Rafeek. I get that you are
trying to reference cell AA1 as the date but it didn't seem to execute
properly when I made the date the first of the month... it did work
when there was no date in AA1 at all thou! lol

Carlo.. I think I understand your code. If Now() calls the system date
for today and "DD" the actual date, if DD= the first of the month then
the msgbox will run. Am I right?
Also, is there a way I can test this without having wait till the
beginning of the month? lol (even thou it is only 2 days away!)

Thanks again all xx
Doo

Carlo wrote:

I think Rafeek has misunderstood you slightly!
try this:

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 Then
MsgBox "It's the first of the month"
End If
End Sub

If I did misunderstand you, then I apologize for any
inconvenience.

Cheers
Carlo

"Doo0592" wrote:

Howdy,

Can anyone suggest code to run a dialogue box when a workbook opens but
only on the first of every month?

Ta :)

Code to run dialogue box at workbook startup
 

Ok, maybe I don't! lol

I have this:

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 And _
Worksheets("sheet2").Range("A1") < Format(Now(), "DD.MM.YYYY")
Then _
MsgBox ("It's the first of the month and first time you open
the workbook today"), vbOKOnly
worksheets("sheet2").range("A1").formular1c1 = format(now),
"DD.MM.YYYY"
End If
End Sub

but there is something wrong with the syntax of this line
worksheets("sheet2").range("A1").formular1c1 = format(now),
"DD.MM.YYYY"


Doo0592 wrote:
Thanks Carlo :)

Think I have it now x

Carlo wrote:
Hi Doo

You're right, now() calls the system date, so,
DD ist the Format for Day.
i.e. format("31.8.2006";"DD") = 31
If you want to test it, just change the one to
the current day.

Ok, let's try to combine those two codes:
(shouldn't be that big a Problem!)
As Rafeek stated, you need a Cell, which you can
alter any time without problems, so take one which
is at the right bottom of your sheet: AA65000 would
be ok i think, or AA1 if you are sure, that this cell
will never be used.
Enter the Date of Yesterday (just needed to be done
once!)

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 and worksheets("test").range("AA65000") <
format(now(), "DD.MM.YYYY") Then
MsgBox "It's the first of the month and first time you open the
workbook today"
sheet("test").range("AA65000").formular1c1 = format(now),
"DD.MM.YYYY")
End If
End Sub

you have to change the sheetname "test", the dateformat to your preferred
format and the cell to the cell you want to use!

Hope i could help

Cheers Carlo

"Doo0592" wrote:

Actually Rafeek, I think I understand your code now. If I am right it
will only execute once per month? If I am right, is there a way we can
combine your code with Carlo's. I think it will probably annoy my end
users if this msg box executes every time they open the workbook! lol

ta :)

Doo
Doo0592 wrote:

Thanks folks.

Not sure I understand your code either Rafeek. I get that you are
trying to reference cell AA1 as the date but it didn't seem to execute
properly when I made the date the first of the month... it did work
when there was no date in AA1 at all thou! lol

Carlo.. I think I understand your code. If Now() calls the system date
for today and "DD" the actual date, if DD= the first of the month then
the msgbox will run. Am I right?
Also, is there a way I can test this without having wait till the
beginning of the month? lol (even thou it is only 2 days away!)

Thanks again all xx
Doo

Carlo wrote:

I think Rafeek has misunderstood you slightly!
try this:

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 Then
MsgBox "It's the first of the month"
End If
End Sub

If I did misunderstand you, then I apologize for any
inconvenience.

Cheers
Carlo

"Doo0592" wrote:

Howdy,

Can anyone suggest code to run a dialogue box when a workbook opens but
only on the first of every month?

Ta :)

Code to run dialogue box at workbook startup
 

Got this in the end from Bob,


Private Sub workbook_open()

If Month(Date) < Sheet2.Range("A1").Value And _
Day(Date) = 1 Then
MsgBox ("Please archive this tracker today. Thanks.")
Sheet2.Range("A1").Value = Month(Date)
End If

End Sub

Wherer A1 is yesterdays date. It seems to be working fine. Thanks for
your help Carlos :)

Doo

Doo0592 wrote:

Ok, maybe I don't! lol

I have this:

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 And _
Worksheets("sheet2").Range("A1") < Format(Now(), "DD.MM.YYYY")
Then _
MsgBox ("It's the first of the month and first time you open
the workbook today"), vbOKOnly
worksheets("sheet2").range("A1").formular1c1 = format(now),
"DD.MM.YYYY"
End If
End Sub

but there is something wrong with the syntax of this line
worksheets("sheet2").range("A1").formular1c1 = format(now),
"DD.MM.YYYY"


Doo0592 wrote:
Thanks Carlo :)

Think I have it now x

Carlo wrote:
Hi Doo

You're right, now() calls the system date, so,
DD ist the Format for Day.
i.e. format("31.8.2006";"DD") = 31
If you want to test it, just change the one to
the current day.

Ok, let's try to combine those two codes:
(shouldn't be that big a Problem!)
As Rafeek stated, you need a Cell, which you can
alter any time without problems, so take one which
is at the right bottom of your sheet: AA65000 would
be ok i think, or AA1 if you are sure, that this cell
will never be used.
Enter the Date of Yesterday (just needed to be done
once!)

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 and worksheets("test").range("AA65000") <
format(now(), "DD.MM.YYYY") Then
MsgBox "It's the first of the month and first time you open the
workbook today"
sheet("test").range("AA65000").formular1c1 = format(now),
"DD.MM.YYYY")
End If
End Sub

you have to change the sheetname "test", the dateformat to your preferred
format and the cell to the cell you want to use!

Hope i could help

Cheers Carlo

"Doo0592" wrote:

Actually Rafeek, I think I understand your code now. If I am right it
will only execute once per month? If I am right, is there a way we can
combine your code with Carlo's. I think it will probably annoy my end
users if this msg box executes every time they open the workbook! lol

ta :)

Doo
Doo0592 wrote:

Thanks folks.

Not sure I understand your code either Rafeek. I get that you are
trying to reference cell AA1 as the date but it didn't seem to execute
properly when I made the date the first of the month... it did work
when there was no date in AA1 at all thou! lol

Carlo.. I think I understand your code. If Now() calls the system date
for today and "DD" the actual date, if DD= the first of the month then
the msgbox will run. Am I right?
Also, is there a way I can test this without having wait till the
beginning of the month? lol (even thou it is only 2 days away!)

Thanks again all xx
Doo

Carlo wrote:

I think Rafeek has misunderstood you slightly!
try this:

Private Sub Workbook_Open()
If Format(Now(), "DD") = 1 Then
MsgBox "It's the first of the month"
End If
End Sub

If I did misunderstand you, then I apologize for any
inconvenience.

Cheers
Carlo

"Doo0592" wrote:

Howdy,

Can anyone suggest code to run a dialogue box when a workbook opens but
only on the first of every month?

Ta :)