If criteria not met then message box

[daniels012]

I am very new to code. I have created macros in the past.
How can I write code that would let me manipulate data. For example:

I want them to enter a date that automatically puts Sunday in the cel
above it (Cell C5). I have this done in a formula so far!!
What I want if it's not Sunday, is a message box that pops up and say
"the date you entered is not equal to Sunday, change the date. " The
go back to the cell so they can enter the new date?

I am sure this is basic for some. I just can't get it to work

 

[Guest]

Put this in your Worksheet change event
Make changes asif needed as noted next to code

Private Sub Worksheet_Change(ByVal Target As Range
On Error Resume Nex

If Target.Cells.Count = 1 And
Not IsEmpty(Target) And
Target.Address = "$C$6" Then '<< Assumes this is your entry cel

If Not IsDate(Target) The
MsgBox "Must be a Valid Date!
Target.Value = "
Exit Su
End I

If Weekday(CDate(Target.Value)) <> 1 The
MsgBox "Must be a Valid Sunday Date!
Target.Value = "
Exit Su
End I

'>>Use this if you need to check that date is not a Future dat
'If CDate(Target.Value) > Now The
'MsgBox "Can Not be a Future date!
'>>Use this if you need to check that date is not a Past dat
'If CDate(Target.Value) < Now The
'MsgBox "Can Not be a Past date!

'Target.Value = "
'Exit Su
'End I

End I
End Su

----- daniels012 > wrote: ----

I am very new to code. I have created macros in the past.
How can I write code that would let me manipulate data. For example:

I want them to enter a date that automatically puts Sunday in the cel
above it (Cell C5). I have this done in a formula so far!
What I want if it's not Sunday, is a message box that pops up and say
"the date you entered is not equal to Sunday, change the date. " The
go back to the cell so they can enter the new date

I am sure this is basic for some. I just can't get it to work

 

[Bernie Deitrick]

daniels,

Sub GetSunday()
Dim mydate As Date
Dim msg As String
msg = "Enter a Sunday"
GetDate:
mydate = CDate(Application.InputBox(msg, , , , , , , 2))
If Format(mydate, "dddd") <> "Sunday" Then
msg = "That wasn't a Sunday, enter a SUNDAY!"
GoTo GetDate
End If
With Range("C5")
.NumberFormat = "mm/dd/yy"
.Value = mydate
End With
End Sub

HTH,
Bernie
MS Excel MVP

 

[daniels012]

Bernie: Where do I enter your code you gave me? I right clicked on th
sheet tab at the bottom and hit "view code" then I entered what yo
gave me? It did not work? As I said I am very new to code?

Michae

 

[Bernie Deitrick]

Michael,

View Code is only for event code, not for regular macros.

Use Alt-F11, then Ctrl-R, select your workbook in the window that appears,
thes use Insert |Module and paste the code in the window that appears. Then
you can use Tools | Macros...... to run the macro.

HTH,
Bernie
MS Excel MVP

 

[daniels012]

~x
I used yours and it worked fine, only I wanted to got back into cell c
instead of the cursor moving down one cell??

Thanks for your hel

 

[daniels012]

Thank you so much for the info. As I said I am new to code in excel!

How do I get it to return to the cell to re-enter the data?


Michael
:confused

 

[Bernie Deitrick]

Michael,

The lines

With Range("C5")
.NumberFormat = "mm/dd/yy"
.Value = mydate
End With

actually enter the data. What do you mean re-enter the data? Do you want to
loop through different cells, or different values?

:also confused:
Bernie
MS Excel MVP