Check month

[jimmy]

Hi all,

How to set the entry in a specific column must in date type and checking for
this date must in a specific month? e.g. in the sheet name "Feb", all the
date in column in "A" other than February will not be accepted and prompt a
dialog box to ask the user to use another sheet..

Thanks

 

[Bob Phillips]

You could create data validation for the column, let's say column E for this
example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jimmy]

Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to test?
Thanks..

You could create data validation for the column, let's say column E for
this example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi all,

How to set the entry in a specific column must in date type and checking
for this date must in a specific month? e.g. in the sheet name "Feb", all
the date in column in "A" other than February will not be accepted and
prompt a dialog box to ask the user to use another sheet..

Thanks

 

[Tom Ogilvy]

Filename is an argument to the Cell worksheet function telling it to return
the fully qualified path and filename of the workbook (and the sheet name).
However, it returns nothing unless the workbook has been saved once (so it
has a fully qualified path and filename).

--
Regards,
Tom Ogilvy

Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to test?
Thanks..

You could create data validation for the column, let's say column E for
this example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi all,

How to set the entry in a specific column must in date type and checking
for this date must in a specific month? e.g. in the sheet name "Feb",
all the date in column in "A" other than February will not be accepted
and prompt a dialog box to ask the user to use another sheet..

Thanks

 

[jimmy]

My excel file is in the d:Book1.xls, I type the following in the sheet3 cell
"E1", then E1 display 1900/1/0. How to use it to check for the cell in "A1"
is my specific valid month?

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY())))

Filename is an argument to the Cell worksheet function telling it to
return the fully qualified path and filename of the workbook (and the
sheet name). However, it returns nothing unless the workbook has been
saved once (so it has a fully qualified path and filename).

--
Regards,
Tom Ogilvy

Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to test?
Thanks..

You could create data validation for the column, let's say column E for
this example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi all,

How to set the entry in a specific column must in date type and
checking for this date must in a specific month? e.g. in the sheet name
"Feb", all the date in column in "A" other than February will not be
accepted and prompt a dialog box to ask the user to use another sheet..

Thanks

 

[Tom Ogilvy]

First, the sheet must have a Month name, so change Sheet3 to Mar
Put this in cell E1 of the sheet named Mar (don't change the formula -
don't replace "filename")

=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

Now select Column A of the sheet named Mar

go to Data=>Validation

Select Custom and in the textbox put in the formula

=Month(A1)=$E$1

and click OK.

--
Regards,
Tom Ogilvy

My excel file is in the d:Book1.xls, I type the following in the sheet3
cell "E1", then E1 display 1900/1/0. How to use it to check for the cell
in "A1" is my specific valid month?

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY())))

Filename is an argument to the Cell worksheet function telling it to
return the fully qualified path and filename of the workbook (and the
sheet name). However, it returns nothing unless the workbook has been
saved once (so it has a fully qualified path and filename).

--
Regards,
Tom Ogilvy

Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to test?
Thanks..
"Bob Phillips" <[email protected]>
¼¶¼g©ó¶l¥ó·s»D:%[email protected]...
You could create data validation for the column, let's say column E for
this example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi all,

How to set the entry in a specific column must in date type and
checking for this date must in a specific month? e.g. in the sheet
name "Feb", all the date in column in "A" other than February will not
be accepted and prompt a dialog box to ask the user to use another
sheet..

Thanks

 

[jimmy]

Thank you..
In fact, what's the different between using this and just set 01-03-2007 to
31-03-2007 in the Data => Validation?

First, the sheet must have a Month name, so change Sheet3 to Mar
Put this in cell E1 of the sheet named Mar (don't change the formula -
don't replace "filename")

=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

Now select Column A of the sheet named Mar

go to Data=>Validation

Select Custom and in the textbox put in the formula

=Month(A1)=$E$1

and click OK.

--
Regards,
Tom Ogilvy

My excel file is in the d:Book1.xls, I type the following in the sheet3
cell "E1", then E1 display 1900/1/0. How to use it to check for the cell
in "A1" is my specific valid month?

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY())))

Filename is an argument to the Cell worksheet function telling it to
return the fully qualified path and filename of the workbook (and the
sheet name). However, it returns nothing unless the workbook has been
saved once (so it has a fully qualified path and filename).

--
Regards,
Tom Ogilvy


Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to test?
Thanks..
"Bob Phillips" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:%[email protected]...
You could create data validation for the column, let's say column E
for this example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi all,

How to set the entry in a specific column must in date type and
checking for this date must in a specific month? e.g. in the sheet
name "Feb", all the date in column in "A" other than February will
not be accepted and prompt a dialog box to ask the user to use
another sheet..

Thanks

 

[Bob Phillips]

This will vary the validation based upon the sheet name, so you can apply
the same formula to all sheets, not a different one to each.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thank you..
In fact, what's the different between using this and just set 01-03-2007
to 31-03-2007 in the Data => Validation?

First, the sheet must have a Month name, so change Sheet3 to Mar
Put this in cell E1 of the sheet named Mar (don't change the formula -
don't replace "filename")

=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

Now select Column A of the sheet named Mar

go to Data=>Validation

Select Custom and in the textbox put in the formula

=Month(A1)=$E$1

and click OK.

--
Regards,
Tom Ogilvy

My excel file is in the d:Book1.xls, I type the following in the sheet3
cell "E1", then E1 display 1900/1/0. How to use it to check for the
cell in "A1" is my specific valid month?

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY())))



"Tom Ogilvy" <[email protected]>
¼¶¼g©ó¶l¥ó·s»D:[email protected]...
Filename is an argument to the Cell worksheet function telling it to
return the fully qualified path and filename of the workbook (and the
sheet name). However, it returns nothing unless the workbook has been
saved once (so it has a fully qualified path and filename).

--
Regards,
Tom Ogilvy


Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to test?
Thanks..
"Bob Phillips" <[email protected]>
¼¶¼g©ó¶l¥ó·s»D:%[email protected]...
You could create data validation for the column, let's say column E
for this example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi all,

How to set the entry in a specific column must in date type and
checking for this date must in a specific month? e.g. in the sheet
name "Feb", all the date in column in "A" other than February will
not be accepted and prompt a dialog box to ask the user to use
another sheet..

Thanks

 

[jimmy]

ic..thank you.
The 12 sheets have named as January to December, I wish to write the code in
the Workbook open() function that the current month sheet activate...I tried
MONTH(TODAY()).ACTIVATE but failed. What the code should be?

This will vary the validation based upon the sheet name, so you can apply
the same formula to all sheets, not a different one to each.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Thank you..
In fact, what's the different between using this and just set 01-03-2007
to 31-03-2007 in the Data => Validation?

First, the sheet must have a Month name, so change Sheet3 to Mar
Put this in cell E1 of the sheet named Mar (don't change the formula -
don't replace "filename")

=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

Now select Column A of the sheet named Mar

go to Data=>Validation

Select Custom and in the textbox put in the formula

=Month(A1)=$E$1

and click OK.

--
Regards,
Tom Ogilvy

My excel file is in the d:Book1.xls, I type the following in the sheet3
cell "E1", then E1 display 1900/1/0. How to use it to check for the
cell in "A1" is my specific valid month?

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY())))



"Tom Ogilvy" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
Filename is an argument to the Cell worksheet function telling it to
return the fully qualified path and filename of the workbook (and the
sheet name). However, it returns nothing unless the workbook has been
saved once (so it has a fully qualified path and filename).

--
Regards,
Tom Ogilvy


Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to test?
Thanks..
"Bob Phillips" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:%[email protected]...
You could create data validation for the column, let's say column E
for this example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



Hi all,

How to set the entry in a specific column must in date type and
checking for this date must in a specific month? e.g. in the sheet
name "Feb", all the date in column in "A" other than February will
not be accepted and prompt a dialog box to ask the user to use
another sheet..

Thanks

 

[Bob Phillips]

Try

Worksheets(Format(Date,"mmmm")).Activate

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

ic..thank you.
The 12 sheets have named as January to December, I wish to write the code
in the Workbook open() function that the current month sheet activate...I
tried MONTH(TODAY()).ACTIVATE but failed. What the code should be?

This will vary the validation based upon the sheet name, so you can apply
the same formula to all sheets, not a different one to each.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Thank you..
In fact, what's the different between using this and just set 01-03-2007
to 31-03-2007 in the Data => Validation?

"Tom Ogilvy" <[email protected]>
¼¶¼g©ó¶l¥ó·s»D:[email protected]...
First, the sheet must have a Month name, so change Sheet3 to Mar
Put this in cell E1 of the sheet named Mar (don't change the formula -
don't replace "filename")

=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

Now select Column A of the sheet named Mar

go to Data=>Validation

Select Custom and in the textbox put in the formula

=Month(A1)=$E$1

and click OK.

--
Regards,
Tom Ogilvy

My excel file is in the d:Book1.xls, I type the following in the
sheet3 cell "E1", then E1 display 1900/1/0. How to use it to check
for the cell in "A1" is my specific valid month?

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY())))



"Tom Ogilvy" <[email protected]>
¼¶¼g©ó¶l¥ó·s»D:[email protected]...
Filename is an argument to the Cell worksheet function telling it to
return the fully qualified path and filename of the workbook (and the
sheet name). However, it returns nothing unless the workbook has been
saved once (so it has a fully qualified path and filename).

--
Regards,
Tom Ogilvy


Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to test?
Thanks..
"Bob Phillips" <[email protected]>
¼¶¼g©ó¶l¥ó·s»D:%[email protected]...
You could create data validation for the column, let's say column E
for this example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



Hi all,

How to set the entry in a specific column must in date type and
checking for this date must in a specific month? e.g. in the sheet
name "Feb", all the date in column in "A" other than February will
not be accepted and prompt a dialog box to ask the user to use
another sheet..

Thanks

 

[jimmy]

Solved now. Thank you.

Try

Worksheets(Format(Date,"mmmm")).Activate

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

ic..thank you.
The 12 sheets have named as January to December, I wish to write the code
in the Workbook open() function that the current month sheet activate...I
tried MONTH(TODAY()).ACTIVATE but failed. What the code should be?

This will vary the validation based upon the sheet name, so you can
apply the same formula to all sheets, not a different one to each.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Thank you..
In fact, what's the different between using this and just set
01-03-2007 to 31-03-2007 in the Data => Validation?

"Tom Ogilvy" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
First, the sheet must have a Month name, so change Sheet3 to Mar
Put this in cell E1 of the sheet named Mar (don't change the
formula - don't replace "filename")

=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

Now select Column A of the sheet named Mar

go to Data=>Validation

Select Custom and in the textbox put in the formula

=Month(A1)=$E$1

and click OK.

--
Regards,
Tom Ogilvy

My excel file is in the d:Book1.xls, I type the following in the
sheet3 cell "E1", then E1 display 1900/1/0. How to use it to check
for the cell in "A1" is my specific valid month?

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY())))



"Tom Ogilvy" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
Filename is an argument to the Cell worksheet function telling it to
return the fully qualified path and filename of the workbook (and
the sheet name). However, it returns nothing unless the workbook has
been saved once (so it has a fully qualified path and filename).

--
Regards,
Tom Ogilvy


Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to
test?
Thanks..
"Bob Phillips" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:%[email protected]...
You could create data validation for the column, let's say column
E for this example, with a custom type and a formula of

=MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



Hi all,

How to set the entry in a specific column must in date type and
checking for this date must in a specific month? e.g. in the
sheet name "Feb", all the date in column in "A" other than
February will not be accepted and prompt a dialog box to ask the
user to use another sheet..

Thanks