Check for date
 

Memberfee is due every 1. day of month. How do I make a automatic check for
the date when the workbook open?

Check for date
 

'-------------------------------------------------------------------------
Private Sub Workbook_Open()
'-------------------------------------------------------------------------
If Day(Date) = 1 Then
'do the check
End If
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips

"Kjeldc" wrote in message
...
Memberfee is due every 1. day of month. How do I make a automatic check
for
the date when the workbook open?

Check for date
 

On Tue, 9 Aug 2005 08:02:05 -0700, Kjeldc
wrote:

Memberfee is due every 1. day of month. How do I make a automatic check for
the date when the workbook open?

Right click on some worksheet tab and select View Code.

In the project explorer window, select the line in your project that says "This
Workbook". Double click on that line and paste the code below into the window
that opens:

====================================
Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox ("Member Fee Due")
End If
End Sub
================================

Obviously, you may want to pretty this up a bit. But event-driven macros are
the key, here.


--ron

Check for date
 

Thanks. Appreciate your help

"Bob Phillips" skrev:

'-------------------------------------------------------------------------
Private Sub Workbook_Open()
'-------------------------------------------------------------------------
If Day(Date) = 1 Then
'do the check
End If
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips

"Kjeldc" wrote in message
...
Memberfee is due every 1. day of month. How do I make a automatic check
for
the date when the workbook open?

Check for date
 

Thanks. Appreciate your help

"Ron Rosenfeld" skrev:

On Tue, 9 Aug 2005 08:02:05 -0700, Kjeldc
wrote:

Memberfee is due every 1. day of month. How do I make a automatic check for
the date when the workbook open?

Right click on some worksheet tab and select View Code.

In the project explorer window, select the line in your project that says "This
Workbook". Double click on that line and paste the code below into the window
that opens:

====================================
Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox ("Member Fee Due")
End If
End Sub
================================

Obviously, you may want to pretty this up a bit. But event-driven macros are
the key, here.


--ron

Check for date
 

Hello Ron,

I have a similar need. Hope you can help...
I have a worksheet with a list of equipment and their relative date of
validity (time limit for use).
I would like a code that highlight in red colour for example all the dates
that are within 45 days.

Thanks for your support.
Luc

-------------------------

"Ron Rosenfeld" wrote:

On Tue, 9 Aug 2005 08:02:05 -0700, Kjeldc
wrote:

Memberfee is due every 1. day of month. How do I make a automatic check for
the date when the workbook open?

Right click on some worksheet tab and select View Code.

In the project explorer window, select the line in your project that says "This
Workbook". Double click on that line and paste the code below into the window
that opens:

====================================
Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox ("Member Fee Due")
End If
End Sub
================================

Obviously, you may want to pretty this up a bit. But event-driven macros are
the key, here.


--ron

Check for date
 

Luc,

Use the standard feature "Conditional format" in your menu. You don't have
to program something.

grtx's M.

"Luc" schreef in bericht
...
Hello Ron,

I have a similar need. Hope you can help...
I have a worksheet with a list of equipment and their relative date of
validity (time limit for use).
I would like a code that highlight in red colour for example all the dates
that are within 45 days.

Thanks for your support.
Luc

-------------------------

"Ron Rosenfeld" wrote:

On Tue, 9 Aug 2005 08:02:05 -0700, Kjeldc

wrote:

Memberfee is due every 1. day of month. How do I make a automatic check
for
the date when the workbook open?

Right click on some worksheet tab and select View Code.

In the project explorer window, select the line in your project that
says "This
Workbook". Double click on that line and paste the code below into the
window
that opens:

====================================
Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox ("Member Fee Due")
End If
End Sub
================================

Obviously, you may want to pretty this up a bit. But event-driven
macros are
the key, here.


--ron

Check for date
 

Martijn,

Thank you for your propmpt answer.
I didn't know that feature and tried it. It effectively is very good and
simple!

I have further thought about my need and would like to submit a more precise
request.
I still have my equipment list with their limit date (all the dates in the
same column).

In the worksheet, I would like to have a button "Check status" that makes
the following actions when clicked:
- all the dates of the current month (e.g.: November 2005) to get RED;
- all the dates of the current month + 1 (e.g.: December 2005) to get YELLOW;
- all the dates of the current month + 2 (e.g.: January 2006) to get BLUE.
Excel should automatically get the current month from windows clock.

I thank you in advance if you can help for that. That example will allow me
to start working with simple codes and adapt existing example to my needs.

Greatings
Luc

-----------------------------------------

"Martijn" wrote:

Luc,

Use the standard feature "Conditional format" in your menu. You don't have
to program something.

grtx's M.

"Luc" schreef in bericht
...
Hello Ron,

I have a similar need. Hope you can help...
I have a worksheet with a list of equipment and their relative date of
validity (time limit for use).
I would like a code that highlight in red colour for example all the dates
that are within 45 days.

Thanks for your support.
Luc

-------------------------

"Ron Rosenfeld" wrote:

On Tue, 9 Aug 2005 08:02:05 -0700, Kjeldc

wrote:

Memberfee is due every 1. day of month. How do I make a automatic check
for
the date when the workbook open?

Right click on some worksheet tab and select View Code.

In the project explorer window, select the line in your project that
says "This
Workbook". Double click on that line and paste the code below into the
window
that opens:

====================================
Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox ("Member Fee Due")
End If
End Sub
================================

Obviously, you may want to pretty this up a bit. But event-driven
macros are
the key, here.


--ron

Check for date
 

As Martijn has said Conditional Formatting is the perfect tool for your
requirements and that applies to you updated request as well. However as
you have stated you would like to do this with VBA as a learning
exercise one way would be like this. Assuming dates in column F starting
in Row2:

Sub Macro1()

Dim cell As Range
Dim eRow As Long

eRow = Cells(Rows.Count, "F").End(xlUp).Row

For Each cell In Range("F2:F" & eRow)
If IsDate(cell.Value) Then
Select Case DateDiff("M", Date, cell.Value)
Case 0
cell.Interior.ColorIndex = 3
Case 1
cell.Interior.ColorIndex = 6
Case 2
cell.Interior.ColorIndex = 5
Case Else
cell.Interior.ColorIndex = xlNone
End Select
End If
Next cell

End Sub

Hope this helps
Rowan

Luc wrote:
Martijn,

Thank you for your propmpt answer.
I didn't know that feature and tried it. It effectively is very good and
simple!

I have further thought about my need and would like to submit a more precise
request.
I still have my equipment list with their limit date (all the dates in the
same column).

In the worksheet, I would like to have a button "Check status" that makes
the following actions when clicked:
- all the dates of the current month (e.g.: November 2005) to get RED;
- all the dates of the current month + 1 (e.g.: December 2005) to get YELLOW;
- all the dates of the current month + 2 (e.g.: January 2006) to get BLUE.
Excel should automatically get the current month from windows clock.

I thank you in advance if you can help for that. That example will allow me
to start working with simple codes and adapt existing example to my needs.

Greatings
Luc

-----------------------------------------

"Martijn" wrote:


Luc,

Use the standard feature "Conditional format" in your menu. You don't have
to program something.

grtx's M.

"Luc" schreef in bericht
...

Hello Ron,

I have a similar need. Hope you can help...
I have a worksheet with a list of equipment and their relative date of
validity (time limit for use).
I would like a code that highlight in red colour for example all the dates
that are within 45 days.

Thanks for your support.
Luc

-------------------------

"Ron Rosenfeld" wrote:


On Tue, 9 Aug 2005 08:02:05 -0700, Kjeldc



wrote:


Memberfee is due every 1. day of month. How do I make a automatic check

for

the date when the workbook open?

Right click on some worksheet tab and select View Code.

In the project explorer window, select the line in your project that

says "This

Workbook". Double click on that line and paste the code below into the

window

that opens:

====================================
Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox ("Member Fee Due")
End If
End Sub
================================

Obviously, you may want to pretty this up a bit. But event-driven

macros are

the key, here.


--ron