ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Using IS statements (https://www.excelbanter.com/excel-programming/371777-using-statements.html)

Doo0592
Using IS statements
 
Hi all,

Can anyone help me combine these two statements into an IS statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of every
month but only the first time the workbook opens as I don't want it to
annoy my end users.

Ta much for any help :)

Doo

Don Guillett
Using IS statements
 
try
using OR

--
Don Guillett
SalesAid Software

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of every
month but only the first time the workbook opens as I don't want it to
annoy my end users.

Ta much for any help :)

Doo

Bob Phillips
Using IS statements
 
Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value And _
Day(Date) = 1 Then
MsgBox("This message should appear on the first of every month" & _
"and only the first time the workbook opens")
Sheet1.Range("A1").Value = Month(Date)
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of every
month but only the first time the workbook opens as I don't want it to
annoy my end users.

Ta much for any help :)

Doo

NickHK
Using IS statements
 
Surely the first of the month criteria is irrelevant if you want the msgbox
the first time the WB opens ?

Unless you mean the first time AND the first of the month ??

NickHK

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of every
month but only the first time the workbook opens as I don't want it to
annoy my end users.

Ta much for any help :)

Doo

Doo0592
Using IS statements
 

NickHK wrote:
Surely the first of the month criteria is irrelevant if you want the msgbox
the first time the WB opens ?

Unless you mean the first time AND the first of the month ??

NickHK

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of every
month but only the first time the workbook opens as I don't want it to
annoy my end users.

Ta much for any help :)

Doo

Doo0592
Using IS statements
 
Yup, that's the plan Nick :) Does anyone know how I can test this
without waiting for the first of the month or changing the system time
( it's been disabled cause I is at work)?

Ta

Doo


NickHK wrote:
Surely the first of the month criteria is irrelevant if you want the msgbox
the first time the WB opens ?

Unless you mean the first time AND the first of the month ??

NickHK

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of every
month but only the first time the workbook opens as I don't want it to
annoy my end users.

Ta much for any help :)

Doo

NickHK
Using IS statements
 
So which is it ?
1 - Only the first time the WB is opened ?
2 - The first time AND every 1st of the month ?

NickHK

"Doo0592" wrote in message
ups.com...
Yup, that's the plan Nick :) Does anyone know how I can test this
without waiting for the first of the month or changing the system time
( it's been disabled cause I is at work)?

Ta

Doo


NickHK wrote:
Surely the first of the month criteria is irrelevant if you want the
msgbox
the first time the WB opens ?

Unless you mean the first time AND the first of the month ??

NickHK

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of
every
month but only the first time the workbook opens as I don't want it to
annoy my end users.

Ta much for any help :)

Doo

Doo0592
Using IS statements
 
First time the workbook opens on the first of the month.

So far I have this (provided by carlos)

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 the last line doesn't work.. syntax is wrong. Can anyone spot why?

Bob...still having a look at yours honey :)


NickHK wrote:
So which is it ?
1 - Only the first time the WB is opened ?
2 - The first time AND every 1st of the month ?

NickHK

"Doo0592" wrote in message
ups.com...
Yup, that's the plan Nick :) Does anyone know how I can test this
without waiting for the first of the month or changing the system time
( it's been disabled cause I is at work)?

Ta

Doo


NickHK wrote:
Surely the first of the month criteria is irrelevant if you want the
msgbox
the first time the WB opens ?

Unless you mean the first time AND the first of the month ??

NickHK

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of
every
month but only the first time the workbook opens as I don't want it to
annoy my end users.

Ta much for any help :)

Doo

Doo0592
Using IS statements
 
Right, ignore my last post cause I noticed what I'd done wrong :) Bob's
code works fine so I've added that... thanks Bob!

Ta to all who inputted as well. Learning a lot in this forum so it's
mucho appreciated

Doo xx

Doo0592 wrote:
First time the workbook opens on the first of the month.

So far I have this (provided by carlos)

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 the last line doesn't work.. syntax is wrong. Can anyone spot why?

Bob...still having a look at yours honey :)


NickHK wrote:
So which is it ?
1 - Only the first time the WB is opened ?
2 - The first time AND every 1st of the month ?

NickHK

"Doo0592" wrote in message
ups.com...
Yup, that's the plan Nick :) Does anyone know how I can test this
without waiting for the first of the month or changing the system time
( it's been disabled cause I is at work)?

Ta

Doo


NickHK wrote:
Surely the first of the month criteria is irrelevant if you want the
msgbox
the first time the WB opens ?

Unless you mean the first time AND the first of the month ??

NickHK

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of
every
month but only the first time the workbook opens as I don't want it to
annoy my end users.

Ta much for any help :)

Doo

NickHK
Using IS statements
 
OK, but before you did not mention the TODAY aspect of "first time you open
the workbook today".
This works for me :

Private Sub Workbook_Open()
With Worksheets("sheet2").Range("A1")
If Format(Now(), "DD") = 31 And .Value < Format(Now(), "DD.MM.YYYY")
Then
MsgBox ("It's the first of the month and first time you open the
workbook today"), vbOKOnly
.Value = Format(Now(), "DD.MM.YYYY")
End If
End With
End Sub

NickHK

"Doo0592" wrote in message
oups.com...
First time the workbook opens on the first of the month.

So far I have this (provided by carlos)

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 the last line doesn't work.. syntax is wrong. Can anyone spot why?

Bob...still having a look at yours honey :)


NickHK wrote:
So which is it ?
1 - Only the first time the WB is opened ?
2 - The first time AND every 1st of the month ?

NickHK

"Doo0592" wrote in message
ups.com...
Yup, that's the plan Nick :) Does anyone know how I can test this
without waiting for the first of the month or changing the system time
( it's been disabled cause I is at work)?

Ta

Doo


NickHK wrote:
Surely the first of the month criteria is irrelevant if you want the
msgbox
the first time the WB opens ?

Unless you mean the first time AND the first of the month ??

NickHK

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS
statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every
month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of
every
month but only the first time the workbook opens as I don't want
it to
annoy my end users.

Ta much for any help :)

Doo

Doo0592
Using IS statements
 
Cheers Nick,

Got something similar to that in the end. Really appreciate the input
from everyone, can't believe how much I have learned in jsut 3 days!

Doo x


NickHK wrote:
OK, but before you did not mention the TODAY aspect of "first time you open
the workbook today".
This works for me :

Private Sub Workbook_Open()
With Worksheets("sheet2").Range("A1")
If Format(Now(), "DD") = 31 And .Value < Format(Now(), "DD.MM.YYYY")
Then
MsgBox ("It's the first of the month and first time you open the
workbook today"), vbOKOnly
.Value = Format(Now(), "DD.MM.YYYY")
End If
End With
End Sub

NickHK

"Doo0592" wrote in message
oups.com...
First time the workbook opens on the first of the month.

So far I have this (provided by carlos)

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 the last line doesn't work.. syntax is wrong. Can anyone spot why?

Bob...still having a look at yours honey :)


NickHK wrote:
So which is it ?
1 - Only the first time the WB is opened ?
2 - The first time AND every 1st of the month ?

NickHK

"Doo0592" wrote in message
ups.com...
Yup, that's the plan Nick :) Does anyone know how I can test this
without waiting for the first of the month or changing the system time
( it's been disabled cause I is at work)?

Ta

Doo


NickHK wrote:
Surely the first of the month criteria is irrelevant if you want the
msgbox
the first time the WB opens ?

Unless you mean the first time AND the first of the month ??

NickHK

"Doo0592" wrote in message
ups.com...
Hi all,

Can anyone help me combine these two statements into an IS
statement?
Don't quite understand the VB help! Funnily enough! :) lol

Private sub Workbook_Open()
If Month (Date) < Sheet1.Range("A1").Value Then
MsgBox("This message should appear on the first of every
month
and only the first time the workbook opens")
Sheet1.Range("A1").Value.Month(Date)
End If
End Sub

AND

Private Sub Workbook_Open()
If Format(Now(),"DD") = 1 Then
MsgBox("Same as above")
End If
End Sub

To explain, I would like a reminder to appear on the first day of
every
month but only the first time the workbook opens as I don't want
it to
annoy my end users.

Ta much for any help :)

Doo


All times are GMT +1. The time now is 01:38 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com