Disable a ribbon button while user is editing a cell

[sharpcells]

I am developing an Excel add-in which includes a custom ribbon component. With one of the functions I'm finding that Excel will crash if the user was in the process of editing a cell when they clicked the button.
Is there any way to disable the button while a user is editing a cell or detect that an edit is in progress so that I can change the behavior of the button?

 

[Jaafar Tribak]

It depends on the code you have assigned to the ribbon button. You should debug your code and figure out what causes the crashing.

If all fails, you could check the Enabled Property of some ribbon control. If the control's Enabled Property returns FALSE, it means the worksheet is in edit mode so skip the offending code.

 

[sharpcells]

Thanks for the help. I had gotten so focused on the code I was changing I didn't think to look elsewhere. The source of the error was a call to Application.Run("myCommand") which is a valid xll command macro but it must be disabled while a cell is being edited. I will try to find a way to detect this scenario properly. Checking other control's enabled property seems a bit hacky but that might be the easiest solution. I suppose another thing is I could try is creating a do nothing xll command Application.Run("ping") and confirming that works before proceeding with the rest of the button actions.

 

[sharpcells]

I attempted both the "ping" method and testing the Enabled property on the ribbon but neither achieved the behavior I was looking for. Instead I found a new method which appears to be robust but mostly undocumented in the Excel world. I made a blog post about it so that others can apply it to their own code if required.

 

[Jaafar Tribak]

I attempted both the "ping" method and testing the Enabled property on the ribbon but neither achieved the behavior I was looking for. Instead I found a new method which appears to be robust but mostly undocumented in the Excel world. I made a blog post about it so that others can apply it to their own code if required.

Nice. Didn't try it but I like the innovative thinking and am happy you got this working.

Does your code checks the edit status repetitively ?

Another method, in case anyone reads this in the future is to check the visibility of the EXCEL6 window. this Window is visible when excel is in edit mode, otherwise it is invisible. We can take advantage of this fact.

Here is a quick example :

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Declare PtrSafe Function IsWindowVisible Lib "user32" (ByVal hwnd As LongPtr) As Long
    Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
#Else
    Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
    Declare Function IsWindowVisible Lib "user32" (ByVal hwnd As Long) As Long
    Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
#End If

Sub Test()
    Call SetTimer(Application.hwnd, 0, 0, AddressOf EditStatusOutput)
End Sub

Sub EditStatusOutput()
 
    #If Win64 Then
        Dim hwnd As LongLong
    #Else
        Dim hwnd As Long
    #End If
   
    hwnd = FindWindowEx(Application.hwnd, 0, "XLDESK", vbNullString)
    hwnd = FindWindowEx(hwnd, 0, "EXCEL6", vbNullString)
   
    Debug.Print IsWindowVisible(hwnd)

End Sub

Sub EndTimer()
    Call KillTimer(Application.hwnd, 0)
End Sub

Don't forget to run the EndTimer SUB when done.

 

[sharpcells]

That's another interesting solution. I'm not that familiar with all the functions available on user32 nor the internal sub-window names in Excel like "EXCEL6".

> Does your code checks the edit status repetitively ?

Yes, I set up a background thread which makes calls to LPenHelper every ~10ms. The F# async { } block does this rather than relying on OS managed threads with SetTimer.

 

[Jaafar Tribak]

That's another interesting solution. I'm not that familiar with all the functions available on user32 nor the internal sub-window names in Excel like "EXCEL6".

> Does your code checks the edit status repetitively ?

Yes, I set up a background thread which makes calls to LPenHelper every ~10ms. The F# async { } block does this rather than relying on OS managed threads with SetTimer.

I don't know F Sharp that's why I properly couldn't follow the instructions on your blog post.

SetTimer simulates mutithreading by not blocking the UI and is reasonably safe to use in vba if coded properly.

Thanks very much.