Create "a thing" that shows vba code running

  • Thread starter Thread starter gatarossi
  • Start date Start date
G

gatarossi

Dear all,

Is it possible to create "a thing" that shows for the user that some
vba codes are running?

For example, I have four vba codes and it takes a few minutes to
process, but the user can get confused if it is working or not.

Thanks in advance!!!
 
Dear Bob,

This is the main code:

Sub Test()
Dim PB As clsProgBar
Dim nCounter As Integer
Dim lWaitCount As Long

Set PB = New clsProgBar

With PB
.Show

For nCounter = 0 To 100

.Progress = nCounter

For lWaitCount = 0 To 1000000

If UserCancelled = True Then GoTo EndRoutine

Next lWaitCount

Next nCounter

EndRoutine:

.Finish

End With

Set PB = Nothing

End Sub

But how can the excel calculate the progress of some codes? Is it
possible? I think it's necessary to adapt this code, isn't it?

Thanks in advance!!!

André
 
Is it possible to create "a thing" that shows for the user that some
vba codes are running?

For example, I have four vba codes and it takes a few minutes to
process, but the user can get confused if it is working or not.
Click to expand...

Maybe you can make use the following. I have more familiarity with using
this code in a compiled VB program, but it seems to work okay on an Excel
spreadsheet. The code will change the cursor to the "busy" icon (the
spinning hourglass for Windows versions before Vista and that spinning
circle for Vista) before your code executes and back to the default icon
afterwards. Simply copy/paste the following into the applicable Worksheet
code window and call your macro (Sub Test() for this example) from the
spreadsheet itself (via Alt+F8 or as the assigned macro for a control, such
as a button, located on the sheet).

Private Declare Function LoadCursor Lib "user32" _
Alias "LoadCursorA" _
(ByVal hInstance As Long, _
ByVal lpCursorName As Long) As Long

Private Declare Function SetCursor Lib "user32" _
(ByVal hCursor As Long) As Long

Dim HoldCursor As Long ' Receives handle of default cursor
Dim NewCursor As Long ' Newly created cursor
Const IDC_WAIT = 32514&

Sub Test()
NewCursor = LoadCursor(ByVal 0&, IDC_WAIT)
HoldCursor = SetCursor(NewCursor)
'
' Your code goes here -- the MsgBox statement is just an example
'
MsgBox "Hello"
SetCursor HoldCursor
End Sub

Note: I am pretty sure that the spinning icon will only be spinning over the
spreadsheet itself (so if you pop a MessageBox, the cursor won't be busy
icon while over the MessageBox even though your code popped it up).

Rick
 
You can put a modeless userform on the screen. In it, you can put messages (via a label)
that say what the program is doing at the time ("Creating user profiles," "Formatting drive
C," etc.), or just "Running." This is for Excel 2000 and up, I think.
 
gatarossi -

Maybe the simplest way, but without creating "a thing," is to use

Application.DisplayStatusBar = True
Application.StatusBar = "Some VBA code is running. Please be patient..."

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel
 
That is only a demo, you would need to integrate it into your code.

What does your code look like, hopefully not too much, not too complex?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Dear Bob,

This is the main code:

Sub Test()
Dim PB As clsProgBar
Dim nCounter As Integer
Dim lWaitCount As Long

Set PB = New clsProgBar

With PB
.Show

For nCounter = 0 To 100

.Progress = nCounter

For lWaitCount = 0 To 1000000

If UserCancelled = True Then GoTo EndRoutine

Next lWaitCount

Next nCounter

EndRoutine:

.Finish

End With

Set PB = Nothing

End Sub

But how can the excel calculate the progress of some codes? Is it
possible? I think it's necessary to adapt this code, isn't it?

Thanks in advance!!!

André
 
Back
Top