Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Date Error

Posted on 2011-05-11
6
Medium Priority
?
276 Views
Last Modified: 2012-05-11
I have an excel sheet that asks the user to select a date from the calender object in VBA.   The user has to select a Monday and is given a WARNING message box before he inputs but there is NO way of stopping him from chosing a Tuesday Date.

Is there anyway I can restrict the date to just Mondays?

0
Comment
Question by:RichardH1976
  • 2
  • 2
  • 2
6 Comments
 
LVL 18

Accepted Solution

by:
x-men earned 2000 total points
ID: 35738789
try "data validation" under the "Data" menu
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35738858
Another way to avoid the nuisance is to let the user select any date and return the nearest Monday or the next Monday or the previous Monday.
0
 

Author Comment

by:RichardH1976
ID: 35739060
i think i need to use VBA as the calender is inserting the data into the cell any by passing the data validation
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:x-men
ID: 35739568
test your "data validation" configuration to make sure it's bloking what you need
0
 

Author Comment

by:RichardH1976
ID: 35740835

used some VBA in the end.   I will award some points.

If Weekday(Range("B2")) = 2 Then
Exit Sub
Else: MsgBox "THIS IS NOT A MONDAY, PLEASE TRY AGAIN"
Range("B2").Select
Selection.ClearContents
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35741165
You can add some convenience to the validation by using this code


Sub test()
wd = Weekday(Range("B2"))
If wd = 2 Then
    Exit Sub
Else
    fm = MsgBox("THIS IS NOT A MONDAY" & vbCr & """yes"" for next Monday" & vbCr & """no"" for previous Monday", vbYesNoCancel)
    Select Case fm
        Case 2: Range("B2").ClearContents
        Case 6: If wd < 2 Then Range("B2") = Range("B2") + 1 Else Range("B2") = Range("B2") + (7 - (wd - 2))
        Case 7: If wd < 2 Then Range("B2") = Range("B2") - 6 Else Range("B2") = Range("B2") - (wd - 2)
    End Select
End If

End Sub

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question