none
Force Stop Editing Cell to cause Worksheet_Change when macro launches RRS feed

  • Question

  • I have an Excel 2010 worksheet that contains a rounded rectangle form control.  I have assigned a macro to the rounded rectangle form control that executes when the rectangle is clicked upon.  I know that Microsoft says that you cannot run a macro while a user is currently editing a cell.  When a cell is being edited, I do see that the Macro Menu command is disabled to prevent the user from selecting and running a macro.  However, in my case, the user can click on my rectangle form control which launches the macro assigned to it so there is a way to run a macro while a cell is being edited.  I don't know if Microsoft anticipated this or not.  

    My problem is that while the user is editing the cell and clicks the rectangle form control and the macro launches, the Worksheet_Change event does not occur until after the macro completes.  Thus, during macro execution, the cell is still in edit mode.  This is causing me some problems because I have code in the Worksheet_Change event that needs to execute.  I am trying to get the Worksheet_Change event to occur when the user clicks on the rectangle object but it does not occur.  I have tried, in my macro, to do things like activate a different worksheet and call DoEvents to force the Change event and then activate the original worksheet to return back to the worksheet containing the rectangle object.  This does not cause the Change event to occur.

    Any other ideas would be appreciated.

    Thanks,

    Bob

    Tuesday, December 20, 2016 2:43 PM

All replies

  • I have assigned a macro to the rounded rectangle form control that executes when the rectangle is clicked upon. 

    However, in my case, the user can click on my rectangle form control which launches the macro assigned to it so there is a way to run a macro while a cell is being edited.

    My problem is that while the user is editing the cell and clicks the rectangle form control and the macro launches, the Worksheet_Change event does not occur until after the macro completes.  

    Thus, during macro execution, the cell is still in edit mode. 

    No, that conclusion is wrong.

    Make a new file, add a shape to the sheet and assign this macro:

    Sub Test()
      Debug.Print "Test in"
      Range("a1") = Range("a1") + 1
      Debug.Print "Test out"
    End Sub

    Copy this code into the code module of the sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
      Debug.Print Now, "Worksheet_Change", Target.Address
    End Sub


    Select cell B2, press F2, then click the shape.

    If you look into the immediate window the output is this:

    Test in
    Worksheet_Change            $A$1
    Test out
    Worksheet_Change            $B$1

    Means if you click the shape, you exit the Edit mode of the cell and everything works.

    Andreas.

    Tuesday, December 20, 2016 4:48 PM
  • If the user is editing cell B2 and they click the shape to launch the macro, I want the Worksheet_Change event for cell B2 to run before the macro launches.  What I'm seeing is the B2 change event is occurring after the macro completes.
    Tuesday, December 20, 2016 5:27 PM
  • B Catlett -

    I observed the same behavior as you did. I could force the execution sequence by using setting flags in the Selection_Change event of the Worksheet:

    Option Explicit
    Option Compare Text
    
    Private varStartContent As Variant
    Private boolSelectionChangeExecuted As Boolean
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        varStartContent = Target.Value2
        boolSelectionChangeExecuted = False
    End Sub
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not boolSelectionChangeExecuted Then DoCellChangeStuff Target
    End Sub
    Public Sub DoCellChangeStuff(ByVal rngCheckCell As Range)
        varStartContent = rngCheckCell.Value2
        MsgBox "Doing Cell Change Stuff"
        boolSelectionChangeExecuted = True
    End Sub
    Public Sub DoStuff()
        Dim varCheckCellValue2 As Variant
        varCheckCellValue2 = Excel.ActiveCell.Value
        If varCheckCellValue2 <> varStartContent Then
            DoCellChangeStuff Excel.ActiveCell
        End If
        MsgBox "Stuff Done"
    End Sub

    By using a separate sub() for each of the actions, you can control what is executed when. I used Value2 so that cell formatting is ignored.

    Forgot to mention that the sub "DoStuff" is the macro assigned to the Rectangle...

    Hope this helps


    -MainSleuth


    • Edited by MainSleuth Tuesday, December 20, 2016 6:20 PM Clarification
    Tuesday, December 20, 2016 6:03 PM
  • This still does not work for me.  When I click in a cell, for example, A2 and enter some text with cell A2 still in the edit state, I then click on my rectangle that launches the macro, the Worksheet_Change event for A2 doesn't get processed until the macro has completed.
    Tuesday, December 20, 2016 6:28 PM
  • Right. You can't make the Worksheet_Change event fire. Using a combination of other events, you can detect that a user has in fact, started to edit the cell by comparing oldvalue with new value. If NewValue=oldvalue, then the user hasn't made any change. When you start the code associated with your rectangle, you must first check to see if oldvalue=newvalue. If not, then you call the same code as the Worksheet_Change event calls before executing the code of your Rectangle code.

    This means that you have to put the code executing in your current Worksheet_Change event into a separate sub() and call it from multiple spots including the Worksheet_Change and your Rectangle code.

    Other things that can occur but I don't think apply in your situation:

    1 - Excel.Application.EnableEvents=false is in effect so no event will fire

    2 - Macro's are not enabled

    3 - The application silently errors out so the code associated with the Worksheet_Change is never executed.

    4 - The Worksheet_Change code is on a worksheet other than the Rectangle.


    -MainSleuth


    • Edited by MainSleuth Tuesday, December 20, 2016 7:17 PM
    Tuesday, December 20, 2016 7:08 PM
  • Hi B Catlett,

    you had mentioned that you are using rectangle form control.

    it's a button not a rectangle shape.

    you had mentioned that when you edit the cell and it still in edit mode and you click the button then button click event executes first and after executing the code it will executes worksheet_change event.

    because worksheet_change event will occur when you finish the editing cell and move to other cell by pressing arrow keys, pressing enter key or by clicking somewhere by mouse.

    but here you are still in editing mode. it means till now there is no changes made in the sheet.

    when you click on button it is executes the button click event. working as expected.

    so after it finish the execution of code. cell will exit form editing mode and now there is a change in the sheet so worksheet_change event will fire.

    here everything is working correctly.

    you want that when you click on the button then it should execute worksheet_change event first.

    but by just clicking on the button that doesn't contains code that make any changes on the sheet (click event with no code). then no any changes are made and worksheet_change event will not fire.

    so if I try to edit the cell by clicking on it but I did not type anything but still I am in editing mode and now when I click on button. then only button click will execute. worksheet_change event will not fire this time because there is no change in the sheet.

    so you need to think about it and try to set your code that executes on correct time when you want it to execute.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, December 21, 2016 12:59 AM
    Moderator
  • If you are already using Form Control, then change to ActiveX control which supports KeyPress events.

    After each key press you can get latest input in control.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, December 21, 2016 9:34 AM
    Answerer
  • If the user is editing cell B2 and they click the shape to launch the macro, I want the Worksheet_Change event for cell B2 to run before the macro launches.  What I'm seeing is the B2 change event is occurring after the macro completes.
    Why should be care about that?

    The cell is not in edit mode anymore, means the value in the cell already has the value that the user wrote if your macro starts.
    From that point there is no difference if the event occur before or after, the value in the cell is the same.

    I do not want to be rude, but I want to say it clearly: IMHO you are asking for a work around of a (design) bug anywhere in your code.

    Andreas.
    Wednesday, December 21, 2016 11:03 AM
  • The Click-event from an ActiveX-button will always be after the Change-event from the sheet so that indeed is what you should use instead of a form control.

    Jan

    Wednesday, December 21, 2016 12:13 PM