Date drop down list or simlar

Hi again experts,
I have a cell where I need a user to input a "week ending date"...always a sunday. Is there a way of having a calander drop down and the user chose a date from the calander. Also needs to an error message come up if it is not a sunday.

Thanks

Rob.

RobJanineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

khairilCommented:
Hi,

AFAIK, I cannot find any build in function to pop up calendar directly form Excel cell, but you can do a little bit programming to pop up Microsoft Calendar and fill the cell with user's selected date. You can check the date before insertion.

Here is a tutorial to help on creating MS calendar for Excel, http://www.fontstuff.com/vba/vbatut07.htm and you need to enhanced calendar click event as code below:

Private Sub Calendar1_Click
	If  Weekday(Calendar1.Value) = vbSunday Then
		ActiveCell.Value = Calendar1.Value
		Unload Me
	Else
		Msgbox "It is NOT Sunday!"
	End If
End Sub

Open in new window


You may like to know more on Weekday function here, http://www.techonthenet.com/excel/formulas/weekday.php
0
VenuChakkothCommented:
Hi Rob,

Are you looking to populate a defined date range in the list (containing only Sundays within that date range)? or can it be any date which is a Sunday?
0
RobJanineAuthor Commented:
any date that is a sunday.
I think it would be best for the user to just input a date manually, but if its not a sunday for an error message to pop up.
This has to work on peoples personal laptops etc so the calander pop up refered to above might be difficult to get working on other pc's.
I guess there must be a way of making a form pop up and have sunday dates on it rangeing two weeks either side of todays date for the user to choose from.

can someone please help for error message on change in cell.

Rob
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

RobJanineAuthor Commented:
basically, this is for a time sheet programe, the user chooses a week ending date, witch has to be a sunday. then code deletes all days not within that date range : date chosen (sunday) - 6 days. so monday to sunday.
0
VenuChakkothCommented:
Hi Rob,

Please try the attached workbook...
Date-Check.xls
0
RobJanineAuthor Commented:
thanks....how does it work? I have alot of other cell I need to have error messages on?
0
VenuChakkothCommented:
Hi Rob,

I have used the Data Validation feature in excel. I have used the formula =Weekday(D3,2)=7 in the Data Validation Custom criteria, where D3 refers to the Cell, the date needs to be entered.

Also, In the Error tab of Data Validation, mention that its and invalid date and the user needs to key in a date that refers to a Sunday....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobJanineAuthor Commented:
brilliant...thankyou

Rob
0
VenuChakkothCommented:
Refer the attachments as an example
DataValidation.JPG
DataValidation-1.JPG
0
RobJanineAuthor Commented:
thanks again....I just asked a simlar question if your interested.

Cheers

Rob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.