Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If criteria not met then message box
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If criteria not met then message box
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If criteria not met then message box
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 " wrote in message ... 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 cell 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 says "the date you entered is not equal to Sunday, change the date. " Then 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. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If criteria not met then message box
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If criteria not met then message box
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 " wrote in message ... Bernie: Where do I enter your code you gave me? I right clicked on the sheet tab at the bottom and hit "view code" then I entered what you gave me? It did not work? As I said I am very new to code? Michael --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If criteria not met then message box
~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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If criteria not met then message box
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 -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
If criteria not met then message box
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 "daniels012 " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Error Message as test Criteria | Excel Worksheet Functions | |||
Adding an error message at close of file when criteria are met | Excel Discussion (Misc queries) | |||
popup message if certain criteria met in formula result | Excel Worksheet Functions | |||
changing the message in an error message | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |