Change mouse to "busy" pointer when I bring up a form

[Scott Lyon]

In the application that I'm working on (in VBA in Excel), there's one point
where, if the user clicks a certain button on the application, it brings up
a form that asks the user to fill in some information. When they click OK,
it processes that information (including copying a bunch of data), which
tends to take as long as 5-10 minutes.


I'd like, when they click the "OK" button that while it's processing, it
will change the pointer to a "busy" pointer. I've got code to do that, but
it does not appear to be working as planned.


Here's the code I'm using (in the code section of one of the Excel
worksheets):

strName = ""
Load frmName
frmName.Show

frmName.MousePointer = fmMousePointerHourGlass ' Set mouse to busy
pointer

' do all of the processing needed, based on data in strName

frmName.MousePointer = fmMousePointerDefault ' Set mouse back to
normal



In frmName, in the cmdOK_Click() sub, the code is simply:

strName = txtName.Value
Unload Me



What am I missing? Should I be using a different object than frmName for
setting the MousePointer stuff?


Thanks!
-Scott

 

[Tom Ogilvy]

Have you moved the mouse off the Userform. The Userform and each control
has a Mousepointer property that specifies the mouse icon when the mouse is
over that item - perhaps that is overriding your setting.

 

[Scott Lyon]

I was hoping I could force the busy pointer whenever the mouse was over the
entire Excel spreadsheet. However, I cannot seem to find the correct
MousePointer property to do so.


Thanks!
-Scott

 

[Bob Phillips]

Scott,

The problem is due to where you are setting the pointer. You show the form
and then issue the pointer command in that module. However, the form show
passes control to the form, and until that is completed the pointer command
does not get executed.

What you need to do is put the command in the form activate code, for
instance

Private Sub UserForm_Activate()
frmName.MousePointer = fmMousePointerHourGlass
End Sub

You will also need to ensure it gets reset, so put that in the QueryClose
event

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
frmName.MousePointer = fmMousePointerDefault
End Sub

 

[Tom Ogilvy]

Building on Bob's recommendation, I created a userform with two buttons.

Private Sub CommandButton1_Click()
If frmname.MousePointer = fmMousePointerHourGlass Then
frmname.MousePointer = fmMousePointerDefault
Application.Cursor = xlDefault
Else
frmname.MousePointer = fmMousePointerHourGlass
Application.Cursor = xlWait
End If
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Activate()
frmname.MousePointer = fmMousePointerHourGlass
Application.Cursor = xlWait
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
frmname.MousePointer = fmMousePointerDefault
Application.Cursor = xlDefault
End Sub

So clicking commandbutton1, toggled the cursor display for both the
worksheet and userform.