[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

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.

0
RobJanine
Asked:
RobJanine
  • 5
  • 4
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now