Check if excel is in Edit Mode or has a Dialog window on display

[Jaafar Tribak]

Hi all,

Let's assume we want to get a pointer to a workbook opened in a second insatnce of excel and edit a cell .. we would normally use something like this :

Sub Test()
    Dim wb As Workbook
    Set wb = GetObject("C:\MyBook.xls")
    wb.Worksheets(1).Range("a1").Value = 10
End Sub

The above works as expected but if the workbook happens to be in edit mode or an excel dialog is currently on display (for example the data validation window) then the caller application starts hanging

One could check for the presence of the EXCEL6 window that becomes visible when excel enters edit mode and could also check if an XL dialog is on display by determining the foreground window in the excel process but that would be too much hassle and i am not sure it would even work because the caller code doesn't hang when a userform is on display or a Msgbox

Is there another simpler way ?

 

[lepaule]

A simpler (?) way to check if Excel is going to hang when attempting to use automation. This is what I implemented in my project. I've only been using Office 2010+ so haven't tested the conditional compilations but it should work.

Add to the declaration section of some module.

'Registers with OLE the instance of an IMessageFilter interface, which is to be used for handling concurrency issues on the current thread. Only one message filter can be registered for each thread. Threads in multithreaded apartments cannot have message filters.'  HRESULT CoRegisterMessageFilter(
'    _In_opt_  LPMESSAGEFILTER lpMessageFilter,
'    _Out_opt_ LPMESSAGEFILTER *lplpMessageFilter
'  );
#If VBA7 Then
  Public Declare PtrSafe Function CoRegisterMessageFilter Lib "Ole32" (ByVal lpMessageFilter As LongPtr, lplpMessageFilter As LongPtr) As Long
#Else
  Public Declare Function CoRegisterMessageFilter Lib "Ole32" (ByVal lpMessageFilter As Long, lplpMessageFilter As Long) As Long
#End If

Add to procedures section of some module (within scope).

Public Function IsWorkbookAvailable(wkb As Object) As Boolean  'wkb is Excel.Workbook (or any Excel object that refers back to the Excel.Application object)
  On Error GoTo Local_Err
    Dim b As Boolean
    #If VBA7 Then
      Dim lp As LongPtr
    #Else
      Dim lp As Long
    #End If
    
    'Revoke the registered message filter
    '--- credit to kjr: http://www.techsupportforum.com/forums/f57/solved-excel-2003-and-quot-server-busy-and-quot-error-143569.html
    CoRegisterMessageFilter 0, lp
    
    'Try: will throw error if in edit mode (i.e. Interactive = False)
    '--- this block adapted from Andrei Smolin: https://www.add-in-express.com/creating-addins-blog/2011/03/23/excel-check-user-edit-cell/
    If wkb.Application.Interactive = True Then
      'Try: make sure interactivity is possible
      '  will throw error if in Protected View
      wkb.Application.Interactive = False
      wkb.Application.Interactive = True
    End If
    
    'Return value
    b = True
    
Local_Exit:
  On Error Resume Next
    
    '***Very Important: Register previous message filter
    If lp Then CoRegisterMessageFilter lp, lp
    
    IsWorkbookAvailable = b
    
  Exit Function
  
Local_Err:
  'Catch: handle if desired
  
  'Make sure to clean up
  Resume Local_Exit
End Function

And then do something like this.

Sub Test()

    Dim wb As Object 'Excel.Workbook
    
    Set wb = GetObject("C:\MyBook.xls")
    
    If IsWorkbookAvailable(wb) Then
      wb.Worksheets(1).Range("A1").Value = 10
    End If
    
End Sub

Or implement a Do While ... DoEvents ... Loop that self-terminates or opens a cancel message box (but that is a different beast).

 

[Jaafar Tribak]

Sorry for getting back on this so late ... I haven't been visiting Mrexcel for some time
Your solution works great ... the neat CoRegisterMessageFilter API function is what was needed ... This is an interesting area so I'll take a closer look at the function and at its relatives

Thank you very much lepaule

 

[dbooksta]

Add the following line to the Local_Err: block to avoid leaving target in non-interactive mode:

Local_Err:
  wkb.Application.Interactive = True  ' << Add this line
 
  'Make sure to clean up
  Resume Local_Exit
End Function

 

[dbooksta]

Better solution to prevent changing interactive mode of target workbook is to use this as the "try" block:

If wkb.Application.Interactive = True Then
     wkb.Application.Interactive = wkb.Application.Interactive
End If

 

[lepaule]

Add the following line to the Local_Err: block to avoid leaving target in non-interactive mode:

Local_Err:
  wkb.Application.Interactive = True  ' << Add this line
 
  'Make sure to clean up
  Resume Local_Exit
End Function

Trying to set Interactive property when the workbook is in Edit Mode or has a dialog open throws an error, so you can't put that line of code in your error block.

 

[lepaule]

Better solution to prevent changing interactive mode of target workbook is to use this as the "try" block:

If wkb.Application.Interactive = True Then
     wkb.Application.Interactive = wkb.Application.Interactive
End If

You may not have understood the proposed problem.

 

[lepaule]

You may not have understood the proposed problem.

Okay, I was wrong. Just tested it, and "wkb.Application.Interactive = wkb.Application.Interactive" throws an error. However, so does "wkb.Application.Interactive = true" so we can just get rid of the line "wkb.Application.Interactive = False":

If wkb.Application.Interactive = True Then
    'Try: make sure interactivity is possible
    '  will throw error if in Protected View
    wkb.Application.Interactive = True
End If