Remember SelectionChange range in the Change sheet event?

[Marie J-son]

Hi,

In the Worksheet_Change event, I evaluate with Intersect method where the
change take place. If not intersect is nothing, then I need to change format
in the cells BEFORE (the earlier selection). I guess I can do this if I can
"store" the Worksheet_SelectionChange Target.address and pick it up in the
Worksheet_Change event.

I don't want to store it as a temporary txt file, but solve the problem into
the VBA. How can this be done? If it can't with this approach, does anybody
know a better one?

/Kind Regards

 

[Guest]

Do you need to store it if the file is saved, or only while the file is open?

If only while open, insert a module in VBA and use it to create a public
object variable of type Range:

Public StoreSelection as Range

Then, in your event procedure:
Set StoreSelection = Target

If you need to store this range (by the address) between sessions (i.e. even
when the book is closed) you can't do it simply in VBA since your VBA code
will necessarily be shut down when you leave Excel - but instead of a text
file, why not store it in the actual workbook? You could create a hidden
sheet (I'll call it SaveAddress) and pick a cell (say A1) that will hold the
address of the target range - so in your Selection_Change procedure:

Sheets("SaveAddress").Range("A1").Value = Target.Address

Hope this helps!

 

[Marie J-son]

Ohh...Right!

Thank you, you are right. There really are a lot of fields that can containt
variabels in Excel....IV x 65536 of them....
/Regards