Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remember SelectionChange range in the Change sheet event?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remember SelectionChange range in the Change sheet event?
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 procedu 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 procedu Sheets("SaveAddress").Range("A1").Value = Target.Address Hope this helps! "Marie J-son" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remember SelectionChange range in the Change sheet event?
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 "K Dales" skrev i meddelandet ... 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 procedu 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 procedu Sheets("SaveAddress").Range("A1").Value = Target.Address Hope this helps! "Marie J-son" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to use selectionchange instead of doubleclick event? | Excel Discussion (Misc queries) | |||
Worksheet SelectionChange Event | Excel Discussion (Misc queries) | |||
SelectionChange event | Excel Programming | |||
How to preserve cut/copy range on SelectionChange event? | Excel Programming | |||
SelectionChange Event | Excel Programming |