worksheet_change can reference excel range on other worksheet
 

My problem is worksheet code "worksheet_change" does not allow usage of a
named range thatis in another worksheet.
Example code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Start As Variant

If Target.Address = Range("A1").Address Then
Range("A4").Value = Range("myDate").Offset(Range("A1").Value - 1,
0).Resize(1, 1).Value
End If

End Sub

This code is behind worksheet1, the range "myDate" is in worksheet2

Any suggestions?

worksheet_change can reference excel range on other worksheet
 

See if it works when you tell it where to find the range:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Start As Variant
If Target.Address = Range("A1").Address Then
'Tell it where Range("myDate") is
Range("A4").Value = Worksheets(2).Range("myDate").Offset(Range _
("A1").Value - 1, 0).Resize(1, 1).Value
End If
End Sub


"Dreiding" wrote:

My problem is worksheet code "worksheet_change" does not allow usage of a
named range thatis in another worksheet.
Example code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Start As Variant

If Target.Address = Range("A1").Address Then
Range("A4").Value = Range("myDate").Offset(Range("A1").Value - 1,
0).Resize(1, 1).Value
End If

End Sub

This code is behind worksheet1, the range "myDate" is in worksheet2

Any suggestions?

worksheet_change can reference excel range on other worksheet
 

Yes, this works and should take care of my immediate needs.
Thanks, - Pat


"JLGWhiz" wrote:

See if it works when you tell it where to find the range:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Start As Variant
If Target.Address = Range("A1").Address Then
'Tell it where Range("myDate") is
Range("A4").Value = Worksheets(2).Range("myDate").Offset(Range _
("A1").Value - 1, 0).Resize(1, 1).Value
End If
End Sub


"Dreiding" wrote:

My problem is worksheet code "worksheet_change" does not allow usage of a
named range thatis in another worksheet.
Example code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Start As Variant

If Target.Address = Range("A1").Address Then
Range("A4").Value = Range("myDate").Offset(Range("A1").Value - 1,
0).Resize(1, 1).Value
End If

End Sub

This code is behind worksheet1, the range "myDate" is in worksheet2

Any suggestions?