Excel VBA - Finish editing a Cell

Question

Excel VBA - Finish editing a Cell

Anonymous

It's always the details that hang me up. :}

I have a macro that cuts and pastes rows to reorder them. It works unless the the User has started editing a cell and didn't hit return. Then the Cut does not succeed and then the macro fails on the Paste. Is there someway to force the completion of a manual cell edit at the beginning of the macro. Here's a snippet of my code.

For R\_Cnt1 = 7 To NumRows1

     If HDWSh.Range("A" & R\_Cnt1) = "O" Then

         If R\_Cnt1 &lt;&gt; L\_Cnt1 Then

             HDWSh.Rows(R\_Cnt1).Cut

             HDWSh.Rows(L\_Cnt1).Insert Shift:=xlShiftDown

         End If

         L\_Cnt1 = L\_Cnt1 + 1

     End If

Next R_Cnt1

I tried adding a

Application.SendKeys "{Enter}", True

but that didn't do anything.

Thanks for your Help!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Answer 1

Anonymous

You may be right that it's a BUG! I tried UNSELECT too and that didn't do anything either. Well... Onwards into the fog I go...:).

Thanks for your help

0 comments

Answer 2

Way! If you run it from the VBA debugger window it will run the macro while a cell is in mid edit.

You think it does, but it doesn't. If it worked, your Sendkeys code would exit the Edit mode, right?

It may be a detail that this window accepts input on a Mac in Edit mode ... this is a bug, it should be locked.

However, in Edit mode the application is locked, whatever you try using a macro in Excel did not work.

Andreas.

Answer 3

Anonymous

Way! If you run it from the VBA debugger window it will run the macro while a cell is in mid edit. You are correct if you try to run it from the applications window, it won't start.

Thanks for your reply!

0 comments

Answer 4

Andreas Killer 144.1K Volunteer Moderator

No way. If Excel is in Edit mode you can't run any macro at all.

Andreas.

0 comments

Share via

Excel VBA - Finish editing a Cell

4 answers