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.

Explaination Required for Slow learner...

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Barry - 20 Oct 2007 19:00 GMT
I'm working on this worksheet and you folks have been wonderful in your
help.
Sometimes I accept your help without question but this time I need further
explanation..

Someone sent me this code which does the job but I think my next step
requires me to work with this same code and I only have a slight clue as to
what it means. If someone could explain what this code means in common terms
I would be greatly appreciative.

Private Sub Worksheet_Change(ByVal Target As Range)

   On Error GoTo ws_exit
   Application.EnableEvents = False

   With Target
       If Not Intersect(Target, Me.Range("A20:A23")) Is Nothing Then
           If .Offset(0, 1).Value <> "" Then
               If .Value > .Offset(0, 1).Value Then
                   MsgBox "Sorry, Invalid date!"
                   .Value = ""
                   .Offset(0, 2).Select
               End If
           End If
       ElseIf Not Intersect(Target, Me.Range("B20:B23")) Is Nothing Then
           If .Value <> "" Then
               If .Value <= .Offset(0, -1).Value Then
                   MsgBox "Date must be later then Start Date!"
                   .Value = ""
                   .Offset(0, 1).Select
               End If
           End If
       End If
   End With

ws_exit:
   Application.EnableEvents = True

End Sub

Thanks In Advance,

Barry

A copy of the worksheet can be downloaded from this address:
http://cjoint.com/?kut13HJGpY

My next step is to try and add code to evaluate  the contents of  cell B20 .
If B20 is greater than the 145th day of the year I would like to be able to
Set B20 to 145 and move the selected date to  B21 with A21= 145 and so
forth...
Don Guillett - 20 Oct 2007 19:26 GMT
Apparently it is just making sure that col a date is before col b date. Put
in a sheet module and test to see.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> I'm working on this worksheet and you folks have been wonderful in your
> help.
[quoted text clipped - 47 lines]
> to Set B20 to 145 and move the selected date to  B21 with A21= 145 and so
> forth...
Bob Phillips - 21 Oct 2007 18:28 GMT
Private Sub Worksheet_Change(ByVal Target As Range)

   On Error GoTo ws_exit
   Application.EnableEvents = False

   With Target
       If Not Intersect(Target, Me.Range("A20:A23")) Is Nothing Then
           If .Offset(0, 1).Value <> "" Then
               If .Value > .Offset(0, 1).Value Then
                   MsgBox "Sorry, Invalid date!"
                   .Value = ""
                   .Offset(0, 2).Select
               End If
           End If
       ElseIf Not Intersect(Target, Me.Range("B20:B23")) Is Nothing Then
           If .Value <> "" Then
               If .Value <= .Offset(0, -1).Value Then
                   MsgBox "Date must be later then Start Date!"
                   .Value = ""
                   .Offset(0, 1).Select
               End If
           End If
       End If
       If Me.Cells(.Row, "B").Value > DateSerial(Year(Date), 1, 145) Then
           Me.Cells(.Row, "B").Value = DateSerial(Year(Date), 1, 145)
       End If
   End With

ws_exit:
   Application.EnableEvents = True

End Sub

Signature

---
HTH

Bob

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

> I'm working on this worksheet and you folks have been wonderful in your
> help.
[quoted text clipped - 47 lines]
> to Set B20 to 145 and move the selected date to  B21 with A21= 145 and so
> forth...
ilia - 22 Oct 2007 15:08 GMT
See inline comments.

> I'm working on this worksheet and you folks have been wonderful in your
> help.
[quoted text clipped - 5 lines]
> what it means. If someone could explain what this code means in common terms
> I would be greatly appreciative.

> Private Sub Worksheet_Change(ByVal Target As Range)

Place this entire code in a worksheet's module.  Any time a value of a
cell or group of cells changes, this event is triggered.  Target
variable contains all cells affected by this change.

>     On Error GoTo ws_exit

In the event of an error, the code execution will resume at ws_exit.
This is done to avoid popping up error messages to unsuspecting users.

>     Application.EnableEvents = False

Temporarily disables event processing.  This is done so as not to
retrigger this procedure if a cell is changed within the code.

>     With Target

The With block enables the code to operate on Target without having to
resolve it each time.

>         If Not Intersect(Target, Me.Range("A20:A23")) Is Nothing Then

"If Target and Range A20:A23 have cells in common..."

>             If .Offset(0, 1).Value <> "" Then

"If the cell one column to the right of first cell in Target is not
blank...."

>                 If .Value > .Offset(0, 1).Value Then

"If the leftmost cell is target is greater than the one to the right
of it..."

>                     MsgBox "Sorry, Invalid date!"

Display message box with message above

>                     .Value = ""

Delete the value of Target.

>                     .Offset(0, 2).Select

Select the cell two columns to the right of target.

>                 End If
>             End If
>         ElseIf Not Intersect(Target, Me.Range("B20:B23")) Is Nothing Then

"If Target has cells in common with cells B20:B23..."

>             If .Value <> "" Then

"...and Target cell value is not blank...."

>                 If .Value <= .Offset(0, -1).Value Then

"...and Target cell is less than or equal to the cell one column to
the left of it..."

>                     MsgBox "Date must be later then Start Date!"

Display the message box.

>                     .Value = ""

Set value of Target to blank.

>                     .Offset(0, 1).Select

Select cell one column to the right of Target.

>                 End If
>             End If
[quoted text clipped - 3 lines]
> ws_exit:
>     Application.EnableEvents = True

Restore event processing

> End Sub
>
[quoted text clipped - 8 lines]
> Set B20 to 145 and move the selected date to  B21 with A21= 145 and so
> forth...

Hope this helps.
Barry - 22 Oct 2007 19:00 GMT
A GREAT BIG THANKS!!!!! to all who responded.

ilia you hit it on the head. Thanks bunches!

Barry

> See inline comments.
>
[quoted text clipped - 112 lines]
>
> Hope this helps.
ilia - 23 Oct 2007 06:09 GMT
Glad I could help.  I hope this assists you in your quest to master
VBA programming.

-Ilia

> A GREAT BIG THANKS!!!!! to all who responded.
>
[quoted text clipped - 120 lines]
>
> - Show quoted text -

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.