| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Private Sub Worksheet_Change(ByVal Target As Excel.Range) | Excel Worksheet Functions | |||
| Worksheet_Change(ByVal Target As Excel.Range) | Excel Programming | |||
| Can you get the range reference for each page in a worksheet print range? | Excel Programming | |||
| Many Sub Worksheet_Change(ByVal Target As Range) In One Worksheet | Excel Programming | |||
Linear Mode
