-1

I tried creating a while loop to make sure it was set in case the user was typing but it seems like the macro magically stops when it tries to assign a value and the user is already typing something.

Basically how do you stop a macro from ending from this unexpected situation and how can I detect if the user is blocking?

0m3r
  • 12,286
  • 15
  • 35
  • 71
CodeCamper
  • 6,609
  • 6
  • 44
  • 94
  • This isn't very clear. For example -- what does "it" in the first sentence refer to? The macro? The while loop? But -- what does it mean to "set" a macro or a loop? And -- how is it even possible to type when the macro is running? Are you using `DoEvents`? Maybe you can post the code and describe the problem more precisely. – John Coleman Aug 21 '15 at 17:41
  • Macros typically do not run if the user is in "edit mode", and VBA can't directly detect that: https://social.msdn.microsoft.com/Forums/en-US/3333e18b-cef3-4d78-b47a-6916a1b2d84c/excel-edit-mode?forum=isvvba – Tim Williams Aug 21 '15 at 17:50
  • @JohnColeman very simple I am checking a cell for a change and when the change occurs I have it change the value of an adjacent cell to a time. It works fine but if the user is in edit mode as Tim called it my macro stops running. – CodeCamper Aug 21 '15 at 18:02
  • @TimWilliams for example I run a macro that loops for a long time and changes the value of a single cell. It runs fine as long as the user is not in edit mode the moment it tries to assign a value to a cell. When that does occur the code abruptly stops. This is crazy! What if I have files opened up in the memory!? In this simple case I just want to loop assign the cell value until it lets me but the macro just stops suddenly it seems. – CodeCamper Aug 21 '15 at 18:04

2 Answers2

1

from your comments you have a misconception of how VBA works.

first, a macro run from a module is taking control of the workbook, you can't detect user input in a while or for loop like that, what you want is to use an event listener like in this tutorial: a good site for vba basics basiclly use the "Private Sub Worksheet_Change" option for a sheet/workbook. also, if you're monitoring just one cell, check out this how to use worksheet change

Community
  • 1
  • 1
Avishay Cohen
  • 1,978
  • 2
  • 21
  • 34
  • thanks for your help. The issue I have is not that I can't detect user input but that user input suddenly stops my macro in the middle of running. This is a serious problem because I also have macros which open up files in memory. But the immediate problem I have is not that, but rather that I am trying to change Cell A1 to TRUE for example while the user is editing other cells. The problem is my macro takes a while to run so you work on your sheet and then BAM when it hits the point where it wants to assign cell A1 the whole macro goes kaplut and this is not good. – CodeCamper Aug 21 '15 at 18:40
  • I have tried using Application.OnTIme to perhaps wait for the user input to finish but this can't work ether because Application.OnTime stops when the user is in input mode. This is crazy because other code runs while the user is in input mode giving me all sort of deadly memory leaks. Everyone says blah blah code can't run in edit mode, that is not true, it certainly can and when you are in edit mode only certain code causes your entire code to stop running! – CodeCamper Aug 21 '15 at 18:42
0
Function IsEditing() As Boolean

    If Application.Interactive = False Then
        IsEditing = False
        Exit Function
    End If

On Error GoTo err:
    Application.Interactive = False
    Application.Interactive = True

    IsEditing = False
    Exit Function

err:
    IsEditing = True
    Exit Function
End Function
Jason Church
  • 1
  • 1