Selected cell doesn't stay selected

**minifreak** · 10-25-2012, 03:35 AM

I have a simple piece of VBA code that works great but.....
after a while the code just stops working ???
The idea is to keep Cell A1 selected, and when a user selects a different cell, the selected cell should return to A1 again
Can anyone see what the problem should be ?
The code is

Please Login or Register  to view this content.

**Winon** · 10-25-2012, 03:57 AM

Hello minifreak,

It seems like you only want Cell A1 to be the Cell that can be accessed. Then try:

Please Login or Register  to view this content.

**TMS** · 10-25-2012, 04:02 AM

It's a selection change event ... and you are selecting a cell in it. So, what do you think happens? And it's going to fire every time any cell on the sheet is fired. I suspect there might be a bit of a loop going on there.

Please Login or Register  to view this content.

Regards, TMS

**Winon** · 10-25-2012, 04:13 AM

@ TMSchucks,

Hi TMS,

But that is what the OP states:

when a user selects a different cell, the selected cell should return to A1 again

To me it would appear that only Cell A1 can or should be selected.

In which case just

Please Login or Register  to view this content.

would suffice.

**TMS** · 10-25-2012, 04:41 AM

@Winon: but you can't select a cell, any cell, in a selection change event without disabling event handling.

My understanding was not that the OP cannot select a cell other than A1 but that he cannot select a cell other than A1 if A1 is blank. Not sure of the value of the variant variable Q but, whatever.

Try leaving cell A1 blank and selecting a cell other than A1; step through the code:

Please Login or Register  to view this content.

You'll see that it executes the code again (at least once). I think Excel/VBA is clever enough to notice this but you should still cater for it.

At best, it slows down the code. At worst, it could crash.

Regards, TMS