Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cursors, foiled again! (Excel 2002)

    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>


  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cursors, foiled again! (Excel 2002)

    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.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cursors, foiled again! (Excel 2002)

    So adding the cludge "ActiveCell.Activate" might solve this?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cursors, foiled again! (Excel 2002)

    I had tried that, it didn't work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •