Have macro abort if cursor is active?

[GeneralDisarray]

Is there a way to determine if the activesheet has a cursor active? In other words, the user is still "in" a cell.

I would like to put in an if statement to the effect of:

If (??cursor is active somewhere in the sheet??) then
'kick out of the macro with a message
else
'run the macro - rest of code here
End if

[GeneralDisarray]

**bump**

I'm having a problem with a macro running prior to the change (users typed input) being committed to the sheet. If the user types in the data and presses a button (prior to committing the change to the sheet).... then i get a bad result.

Does anyone know access something like Application.cursor.active = true.... i just can't seem to find the right property to check

[Norie]

Code can't normally run if a cell is in edit mode.

What code are you having problems with?

[Lifesigns]

Hi GeneralDisarray,

Edit mode isn't exposed to VBA so you cannot place a cell into edit mode or exit it if is in edit mode, what you could try is

Please Login or Register  to view this content.

This will return True if automated actions can occur, depends on your scenario - do you have an example?

[GeneralDisarray]

@Norie: the problem is related to the macro being launched via a button press. The code is somehow able to run even if there is a cell actively being edited.

i'll try to post an example

@Lifesigns: application.ready evaluates to true whether or not the button press happened while in edit mode. Thank you though, I'll look futher here.

[Norie]

If I'm in edit mode and click a button Excel exits edit mode.

[GeneralDisarray]

Sorry this took so long, there was a lot to remove from this.

Basically, when you type into the red box (and commit the change first!), then press the new report button. All is well

When you type and press the button before you have commited the change....
then the new report is made.. and the change is commited
this causes an worksheet_change event to happen
if the user selects NO when prompted, the bug occurs.

I took care of the bug with the "on error resume next" line added in BUT i really don't want the event to happen on that sheet at all.

I want to be able to force the button press to be ignored until the change has been committed.
Basically, I want the event_change check to happen on the report it was made on... not the report it was commited on...

Does this make sense? i've removed 99% of the project and you can view the code in the attached book.

Please Login or Register  to view this content.

[Norie]

There's one way to get rid of the error - goto Tools>Options and set the error trapping level to Break on Unhandled Errors, which I think is the default setting.

By the way, as far as I can see the error isn't caused by being edit mode.

PS What are you trying to undo?

[GeneralDisarray]

the change in the value (change from asdf to something else).

basically, there is a section of the report not shown that will be cleared whenever the interval is changed. to see this, change the interval name on the second report (or third, or any report other than the first). If you choose 'no' then the name will go back to what it was (in the red box) before you tried to change it.


-----

The problem is the event i want to happen upon changing the name for the 'Interval' is being misapplied when the button is pressed while that cell is in edit mode.

[GeneralDisarray]

Ok, i see now what's going on kinda.

Basically, if i allow the code to break and go to "debug" then it takes me to the DMR#1 worksheet_change event (not the DMR#2 worksheet_change code) -- so the undo is just no longer in sync with that event.

SO it's really a logical bug. I'll just have to handle it by deleting the report that was made and figuring out a way to get the user back to the edit mode .... where they left off.


i gets it now, but i'm not happy about it -- if i could just disable the buttons until they have commited the change i would be much happier

[Norie]

Which event is that and how is it being misapplied?

This is the code that triggers the worksheet change event when the button is pressed.

Please Login or Register  to view this content.

PS Did you try my suggestion?

[GeneralDisarray]

the problem is, there needs to be a change capture for the cell that was being edited...not further down the line. i need to halt the addition of the new report until the change in interval name has been dealt with.

no i didn't try that, because i now see that i need to handle that incident (and not just move along).

[Norie]

I'm confused.

What do you want to happen and what don't you want to happen?

Is the button triggering an event you don't want it to?

Is the change event not being triggered when you want it to be?

[GeneralDisarray]

I'm confused.


Is the change event not being triggered when you want it to be?

Yes, in the previously described situation,the change event is not happening when I want it to. It seems to be following the macro
which adds the report.

This only seems to be a problem if the user presses the add report button while editing.

[Norie]

If you don't want the event to be triggered when the button is clicked turn off events in the button code.