Error 1004 when I try to Union ranges - why?

[tskogstrom]

Hi. I try to have routines to restore formats on changes, to cover also
drag and drop. I store last selection with SelectionChange and trig
format restore on Change event. I send the current target and try to
union with stored selection address. THERE I get error 1004 - on third
row from bottom : Set rng =
Application.Union(Range(Blad300.Range("A1").Value), rng). WHY?

/kind regards


In a sheet I have this two procedures:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Store address of last selection
'*************************************
Application.EnableEvents = False
Sheet1.Range("A1").Value = Target.Address
Application.EnableEvents = True
End Sub

Sub Worksheet_Change(ByVal Target As Range)
' Restore format in changed target and the selection before
' to cover drag- and drop event
'************************************
Application.EnableEvents = False
Application.ScreenUpdating = False
Call shtFormat(Target)
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

In a global module, I have this (very simplifed) procedure:
Sub shtFormat(rng As Range)
' SelArRaCl is range name from SelctionChange event from each worksheet
'***************************************
' Union second last selection to restore if drag and drop occure
Set rng = Application.Union(Range(Blad300.Range("A1").Value), rng)
' ERROR 1004 HERE
rng.Interior.Color = RGB(235, 255, 255) 'Simplified format
End Sub

 

[Jim Cone]

Remove ".Value" from the line of code.
However, your code may still not work.
I cannot determine what "Blad300" and rng refers to.
All range references in Application.Union must be to the same sheet.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"tskogstrom"
<[email protected]>
wrote in message
Hi. I try to have routines to restore formats on changes, to cover also
drag and drop. I store last selection with SelectionChange and trig
format restore on Change event. I send the current target and try to
union with stored selection address. THERE I get error 1004 - on third
row from bottom : Set rng =
Application.Union(Range(Blad300.Range("A1").Value), rng). WHY?
/kind regards

In a sheet I have this two procedures:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Store address of last selection
'*************************************
Application.EnableEvents = False
Sheet1.Range("A1").Value = Target.Address
Application.EnableEvents = True
End Sub

Sub Worksheet_Change(ByVal Target As Range)
' Restore format in changed target and the selection before
' to cover drag- and drop event
'************************************
Application.EnableEvents = False
Application.ScreenUpdating = False
Call shtFormat(Target)
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

In a global module, I have this (very simplifed) procedure:
Sub shtFormat(rng As Range)
' SelArRaCl is range name from SelctionChange event from each worksheet
'***************************************
' Union second last selection to restore if drag and drop occure
Set rng = Application.Union(Range(Blad300.Range("A1").Value), rng)
' ERROR 1004 HERE
rng.Interior.Color = RGB(235, 255, 255) 'Simplified format
End Sub