Cursors, foiled again! (Excel 2002)

[Howard Kaikow]

In a class, I'm using the following in the code for the click event for an ActiveX worksheet command button:
<pre>With appExcel
.Cursor = xlWait
RunSomeCode
.Cursor = xlDefault
End With
</pre>

Where appExcel is the Excel Application object set up when the instance of the class was created.

The problem is the cursor does not always change back to the default unless I move the mouse.

How do I work around this?

The problem occurs both when I include the class in VBA and when I use the class via a VB 6 DLL.

I eliminated everything in the worksheet, other than the command button, and the problem persisted. I also eliminated just about all non-relevant code, and the problem persisted.

I then exported what was left of the code created a new workbook, with just the command button, and then imported the code into the new workbook.

In the new workbook, the problem occurs ONLY the FIRST time I click the command button. Subsequent clicks work as expected.

However, when I close the workbook, then open the workbook, the problem again occurs ONLY on the first click of the button.

Implication is that I may not be initializing the class properly.
Here's the module code, followed by the class code.
<pre>Option Explicit
Private clsInstance As clsPizza

Private Sub Auto_Open()
' This test is needed because the class may have been instantiated by a
UDF called before Auto_Open is run
If clsInstance Is Nothing Then
InitializeClass
End If
End Sub

Private Sub Auto_Close()
Set clsInstance = Nothing
End Sub

Private Sub InitializeClass()
Set clsInstance = New clsPizza
With clsInstance
Set .ExcelApp = Application
Set .ExcelWbk = ThisWorkbook
End With
End Sub

''''''''' Class follows
Option Explicit
Private WithEvents appExcel As Excel.Application
Private WithEvents wbkExcel As Excel.Workbook
Private WithEvents wstExcel As Excel.Worksheet

Private WithEvents btnXXX As MSForms.CommandButton

Public Sub SetClass(appIs As Excel.Application, wbkIs As Excel.Workbook)
Set ExcelApp = appIs
Set ExcelWbk = wbkIs
End Sub

Public Property Get ExcelApp() As Excel.Application
Set ExcelApp = appExcel
End Property

Public Property Set ExcelApp(ByVal appNew As Excel.Application)
Set appExcel = appNew
End Property

Public Property Get ExcelWbk() As Excel.Workbook
Set ExcelWbk = wbkExcel
End Property

Public Property Set ExcelWbk(ByVal wbkNew As Excel.Workbook)
Set wbkExcel = wbkNew
Set wstExcel = wbkExcel.ActiveSheet
On Error Resume Next
With wstExcel
Set btnXXX = .OLEObjects("btnXXX").Object
' Other controls omitted because they do not affect this problem
End With
On Error GoTo 0
End Property

Private Sub Class_Terminate()
Set appExcel = Nothing
Set wbkExcel = Nothing
Set wstExcel = Nothing

Set btnXXX = Nothing
End Sub

Private Sub wbkExcel_SheetActivate(ByVal wstLocal As Object)
Set wstExcel = wstLocal
On Error Resume Next
With wstExcel
Set btnXXX = .OLEObjects("btnXXX").Object
' Other controls omitted because they do not affect this problem
End With
On Error GoTo 0
End Sub

Private Sub btnXXX_Click()
With appExcel
.Cursor = xlWait
' Do something trivial
wstExcel.Range("L12") = Rnd & " " & CDbl(Now)
.Cursor = xlDefault
End With
End Sub
</pre>

[Howard Kaikow]

The problem does appear to be focus related.

As I stated before, the problem occurs the first time I click on the command button and not thereafter.

However, if I click on the command button, then click on a cell, when I next click on the command button the problem reappears.

I've tried setting TakeFocusOnClick to False and to True, seems to not make a difference.

[pieterse]

So adding the cludge "ActiveCell.Activate" might solve this?

[Howard Kaikow]

I had tried that, it didn't work.