View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.newusers
Gandalph Gandalph is offline
external usenet poster
 
Posts: 8
Default Worksheet_Change sub routine

I am trying to understand various points of the programme listed below and
given in a book as an example (I have deleted some of the lines of code not
necessary to this question, and commented some of the lines €“ the original
had no commenting):-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant €˜returns TRUE or text string
Set VRange = Range("InputRange") €˜InputRange is defined area on spread
sheet
For Each cell In Target
If Union(cell, VRange).Address = VRange.Address Then €˜is the cell in
InputRange
ValidateCode = EntryIsValid(cell)
If ValidateCode = True Then
Exit Sub
Else
€˜This outputs message of reason not valid entry
End If
End If
Next cell
End Sub

Private Function EntryIsValid(cell) As Variant
' Returns True if cell is an integer between 1 and 12 or blank
' Otherwise it returns a string that describes the problem
€˜ i.e. entry is Text, 12 or Not an Intiger
End Function

My queries are
1. Why the line Set VRange = Range("InputRange"), why not use the
expression Range("InputRange") ?
2. Why For Each cell in Target, when sub routine gives only one
cell reference?