After macro completes, there is no active cell

I didn't even think it was possible to have a state where there is no active cell.

Attached is a sample file. There is a spin button at the top. The purpose of the spin button is to move the selected item up or down. (details below)

The spin button works, but after the code completes, there is no cell selected/active on the worksheet, even though the macro explicitly activates a cell. The address box is blank. If I step through the code in debug mode, it works as expected, with a selection border around the desired cell and its address in the address box.

Select any item in column B. The spin button will become enabled. Click the Up arrow to move the item up in the list, and the Down arrow to move it down.

How do I get it to leave a cell selected, as it does when I step through the code?

Can only be a focus issue...Spin Button takes the focus away from the sheet...
Add a shape to sheet and see...

Please Login or Register  to view this content.

This solves...

I tried DoEvents and Activating the sheet too...This is not stable enough though...works only sometimes...
Perhaps better to make use of Forms Control in this case...

You could probably get away with toggling the visibility of the control

Please Login or Register  to view this content.

but yeah, better not to be using activex.

Originally Posted by Sintek

Can only be a focus issue...Spin Button takes the focus away from the sheet...

I think you are onto something there. I was going to change the "TakeFocusOnClick" property, but spin buttons don't have that (command buttons do).

Originally Posted by romperstomper

better not to be using activex.

<sigh> I hate coding for forms controls, not even sure how to trap a click event. But I'll look into that.

Originally Posted by Alex Blakenburg

Adding the Me.SpinButtonMoveItem.Select before Cell Activate line seems to fix it.

It sure does. And who the hƏll knows why.

This whole behavior is perplexing. As I mentioned earlier, I didn't even think it was possible for Excel to be in a state where no cell was active. But this fixes it in my file.