Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / October 2007

Tip: Looking for answers? Try searching our database.

Data Validation Issue

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Barry - 16 Oct 2007 17:12 GMT
Excel 2003 under Windows XP SP2

I have two cells A20 and B20 both contain date info provided by a popup
calendar routine.
It is necessary for the date in B20 to be greater than that in A20,
otherwise calculations performed on the two cells provide negative numbers.

Any help would be GREATLY APPRECIATED! :)

Barry
Bob Phillips - 16 Oct 2007 17:28 GMT
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B20"     '<== change to suit

   On Error GoTo ws_exit
   Application.EnableEvents = False

   With Target
       If Not Intersect(Target, Me.Range("A20")) Is Nothing Then
           If .Offset(0, 1).Value <> "" Then
               If .Value > .Offset(0, 1).Value Then
                   MsgBox "Invalid date"
                   .Value = ""
               End If
           End If
       ElseIf Not Intersect(Target, Me.Range("B20")) Is Nothing Then
           If .Value <> "" Then
               If .Value <= .Offset(0, -1).Value Then
                   MsgBox "Invalid date"
                   .Value = ""
               End If
           End If
       End If
   End With

ws_exit:
   Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Excel 2003 under Windows XP SP2
>
[quoted text clipped - 7 lines]
>
> Barry
Barry - 16 Oct 2007 18:32 GMT
Well I tried Bob's approach but it still needs tweaking. If anyone wants to
look at the worksheet, let me know!

Thanks

Barry
Gord Dibben - 16 Oct 2007 19:07 GMT
Bob's approach works for me.

What "tweaking" do you think it needs?

Data Validation won't work with the date picker or a copied date.

Gord

>Well I tried Bob's approach but it still needs tweaking. If anyone wants to
>look at the worksheet, let me know!
>
>Thanks
>
>Barry
Barry - 16 Oct 2007 19:33 GMT
I think his code was just what I was looking for. I was just confused as to
how to apply it to the rest of the worksheet. I will be happy to post the
sheet if anyone wants to give it a looksee! It's the only way I can explain.

Barry
Gord Dibben - 16 Oct 2007 20:05 GMT
Use either of the 2 free filehosts below to upload a sample of your actual
worksheet.

(Desensitize it first, if needed). Then copy & paste the generated link to
your sample file in response here:

http://www.flypicture.com/
http://cjoint.com/index.php

Gord

>I think his code was just what I was looking for. I was just confused as to
>how to apply it to the rest of the worksheet. I will be happy to post the
>sheet if anyone wants to give it a looksee! It's the only way I can explain.
>
>Barry
Barry - 16 Oct 2007 21:11 GMT
Ok here it is, althow I couldn't read the page I figured it out!

This link is where you can find the whorsheet/workbook I need a bit of help
with!

http://cjoint.com/?kqwhz1cAZI

Thanks,

Barry
Bob Phillips - 16 Oct 2007 21:28 GMT
http://cjoint.com/?kqwCOJx8oI

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Ok here it is, althow I couldn't read the page I figured it out!
>
[quoted text clipped - 6 lines]
>
> Barry
Gord Dibben - 16 Oct 2007 21:52 GMT
Have downloaded and saved the workbook.

See you have adjusted target range but that doesn't create a problem in my
estimation.

If a bad date <date in A20:A23 is entered in B2:B23 you get the "Invalid Date"
message and date is not entered.

What tweaking do you specifically need doing?

Gord

>Ok here it is, althow I couldn't read the page I figured it out!
>
[quoted text clipped - 6 lines]
>
>Barry
Gord Dibben - 16 Oct 2007 22:02 GMT
Looks like Bob already tweaked it.

http://cjoint.com/?kqwCOJx8oI

That's the one I mistakenly downloaded.

Gord

>Have downloaded and saved the workbook.
>
[quoted text clipped - 18 lines]
>>
>>Barry
Barry - 16 Oct 2007 22:50 GMT
GREAT BIG THANKS!!! To all who responded.

Bob Philips and Gord Dibben I hope you two realize what a super job you do
here on a regular basis.
I look foward to having another hickup for you guys to help me with....LOL

Barry
Gord Dibben - 16 Oct 2007 23:05 GMT
Thanks for the thanks.

We all await with bated breath for your next hiccup<g>

Gord

>GREAT BIG THANKS!!! To all who responded.
>
[quoted text clipped - 3 lines]
>
>Barry
Bernard Liengme - 16 Oct 2007 17:31 GMT
Select B20
Use Data | Validation, and select Date from the Allow options
Now in the Setting tab specify: Date: Greater Than" Start date A20
best wishes
Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Excel 2003 under Windows XP SP2
>
[quoted text clipped - 7 lines]
>
> Barry
Gord Dibben - 16 Oct 2007 17:45 GMT
Bernard

Excel 2003 still allows a "less than" date to be entered when using the date
picker.

Manually entered dates will trigger the DV warning as advertised.

Gord Dibben  MS Excel MVP

>Select B20
>Use Data | Validation, and select Date from the Allow options
>Now in the Setting tab specify: Date: Greater Than" Start date A20
>best wishes
Bernard Liengme - 16 Oct 2007 17:49 GMT
Oh dear, I missed the 'date picker' bit. I was imagining someone typing.
Must read questions more carefully!
thanks
Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Bernard
>
[quoted text clipped - 10 lines]
>>Now in the Setting tab specify: Date: Greater Than" Start date A20
>>best wishes
Crowbar - 16 Oct 2007 18:01 GMT
You can either use the data/validation wizard to achieve this or
use vb to monitor this range

the wizard is easier

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.