Explaination Required for Slow learner...

[Barry]

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]

Apparently it is just making sure that col a date is before col b date. Put
in a sheet module and test to see.

 

[Bob Phillips]

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



--
---
HTH

Bob

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

 

[ilia]

See inline comments.

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)

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
End If
End With

ws_exit:
Application.EnableEvents = True

Restore event processing

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...

Hope this helps.

 

[Barry]

A GREAT BIG THANKS!!!!! to all who responded.

ilia you hit it on the head. Thanks bunches!


Barry

 

[ilia]

Glad I could help. I hope this assists you in your quest to master
VBA programming.

-Ilia