worksheet events and referring to named ranges on other worksheets

[Andrew H]

Hi all,
Have been reading about and experimenting with my question, and have
almost given up. I just wanted to make sure that I'm understanding
things here.

I have workbook-level names defined on a worksheet that holds data
that all other worksheets need to use. Using the Worksheet_Change
event on one of these other worksheets, it seems that I cannot refer
to those named ranges without putting the worksheet name first. Is
there any way around this extra naming?

e.g., The following does not work: (I get the "Method 'Range' of
object '_Worksheet' failed.)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Not Range("A1").value = Range("current_month").value Then
MsgBox "you've changed the month"
'etc. etc.
End If
End If
End Sub

But, this event code works if if the 3rd line is changed to
"If Not Range("A1").value = Worksheets(1).Range("current_month").value
Then"

So, even though I can refer to the cell "current_month" from other
worksheets in Excel, is it true that I cannot refer to that cell
(without saying what sheet it's on) in a Worksheet event?

Many thanks,
Andrew

 

[Peter Beach]

Hi Andrew,

There are a number of funnies concerning scope in worksheet events. I just
keep qualifying it until I get it right <g>. The following appears to work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1")) Is Nothing Then
If Month(Target.Value) <>
Month(ThisWorkbook.Names("CurrMonth").RefersToRange) Then
MsgBox "Month changed"
End If
End If
End Sub

Within the worksheet code the Names collection implicitly refers to names on
that sheet, so it needs to be qualified with ThisWorkbook, and RefersToRange
is just an easy way of getting the value of that range.

HTH

Peter Beach