Hi,

I have a very large worksheet that collect data from more then 50 other
sheets. During the CalculateFull statement the processor runs at 100% for 80
minutes.
Only the Taskmanager indicates that the program is stil running.

I need a progressbar to get a direct control in my worksheet.

Where is the tip that solves my problem?

Hi Knokie
I use an API to run a progressbar it wont take much to add it to project.
HTH
Charles

Private Declare Function CreateWindowEX _
Lib "user32" _
Alias "CreateWindowExA" ( _
ByVal dwExStyle As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String, _
ByVal dwStyle As Long, _
ByVal X As Long, _
ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal hWndParent As Long, _
ByVal hMenu As Long, _
ByVal hInstance As Long, _
lpParam As Any) _
As Long

Private Declare Function DestroyWindow _
Lib "user32" ( _
ByVal hwnd As Long) _
As Long

Private Declare Function SetParent _
Lib "user32" ( _
ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) _
As Long

Private Declare Function SendMessage _
Lib "user32" _
Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) _
As Long

Private Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As Long

Dim rTemp
Dim m_frmHwnd As Long
Dim m_pbHwnd As Long
Dim m_strAviFile As String



Sub UpDateProgressBar()
'
iVal = Format(X / iMax, "0%")
Label3.Caption = iVal
SendMessage m_pbHwnd, &H402, ByVal Val(iVal), 0& '&H402
DoEvents
End Sub

Sub SetupProgressBar()
'
m_frmHwnd = FindWindow(vbNullString, Me.Caption)
W = Me.InsideWidth * 4 / 3
H = Me.InsideHeight * 4 / 3
Me.Repaint
'// ProgressBar [Leds: &H50000000] [Smooth: &H50000001]
m_pbHwnd = CreateWindowEX(0, "msctls_progress32", "", &H50000000, 7, H -
90, W - 15, 25, m_frmHwnd, 0, 0, 0)
SetParent m_pbHwnd, m_frmHwnd
DoEvents
End Sub
Sub EndProgressBar()
'
Label3.Caption = ""
DestroyWindow m_pbHwnd
DoEvents
End Sub



"Knokie From Holland" wrote:

Hi,

I have a very large worksheet that collect data from more then 50 other
sheets. During the CalculateFull statement the processor runs at 100% for 80
minutes.
Only the Taskmanager indicates that the program is stil running.

I need a progressbar to get a direct control in my worksheet.

Where is the tip that solves my problem?

Knokie From Holland wrote:
Hi,

I have a very large worksheet that collect data from more then 50 other
sheets. During the CalculateFull statement the processor runs at 100% for 80
minutes.
Only the Taskmanager indicates that the program is stil running.

I need a progressbar to get a direct control in my worksheet.

Where is the tip that solves my problem?


---------------------

Chip Pearson has a nice "thermometer" style progressor bar that a lot of people
here like. I use it for long running programs. Here is the web page which
describes and provides it.

http://www.cpearson.com/excel/Progress.htm

Good luck...

Bill

Your approach interests me so I decided to play with it. I did a simple
copy/paste from your note into a VBE module and set up a dummy macro to call it
from. When I "Call SetupProgressBar" though, I get an immediate compile error
"Invalid use of Me keyword" in the first line of your routine.

The thing which comes to mind is that I'm still running Excel97 (under XP-Home
with all updates). Should your code work with that level of Excel, or does the
problem lie elsewhere?

Thanks...

Bill
--------------------
vqthomf wrote:
Hi Knokie
I use an API to run a progressbar it wont take much to add it to project.
HTH
Charles

Private Declare Function CreateWindowEX _
Lib "user32" _
Alias "CreateWindowExA" ( _
ByVal dwExStyle As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String, _
ByVal dwStyle As Long, _
ByVal X As Long, _
ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal hWndParent As Long, _
ByVal hMenu As Long, _
ByVal hInstance As Long, _
lpParam As Any) _
As Long

Private Declare Function DestroyWindow _
Lib "user32" ( _
ByVal hwnd As Long) _
As Long

Private Declare Function SetParent _
Lib "user32" ( _
ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) _
As Long

Private Declare Function SendMessage _
Lib "user32" _
Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) _
As Long

Private Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As Long

Dim rTemp
Dim m_frmHwnd As Long
Dim m_pbHwnd As Long
Dim m_strAviFile As String



Sub UpDateProgressBar()
'
iVal = Format(X / iMax, "0%")
Label3.Caption = iVal
SendMessage m_pbHwnd, &H402, ByVal Val(iVal), 0& '&H402
DoEvents
End Sub

Sub SetupProgressBar()
'
m_frmHwnd = FindWindow(vbNullString, Me.Caption)
W = Me.InsideWidth * 4 / 3
H = Me.InsideHeight * 4 / 3
Me.Repaint
'// ProgressBar [Leds: &H50000000] [Smooth: &H50000001]
m_pbHwnd = CreateWindowEX(0, "msctls_progress32", "", &H50000000, 7, H -
90, W - 15, 25, m_frmHwnd, 0, 0, 0)
SetParent m_pbHwnd, m_frmHwnd
DoEvents
End Sub
Sub EndProgressBar()
'
Label3.Caption = ""
DestroyWindow m_pbHwnd
DoEvents
End Sub



"Knokie From Holland" wrote:


Hi,

I have a very large worksheet that collect data from more then 50 other
sheets. During the CalculateFull statement the processor runs at 100% for 80
minutes.
Only the Taskmanager indicates that the program is stil running.

I need a progressbar to get a direct control in my worksheet.

Where is the tip that solves my problem?

Hi Charles,

The problem is still that during the execution of the CalculateFull
statement no other patrs of the code can be executed. The solution must be in
an external program the shares a part of the processor running time. An
example is the ProgramManager. This indicates that the job is still running,
but gives not a progress indication.

Leo


"vqthomf" wrote:

Hi Knokie
I use an API to run a progressbar it wont take much to add it to project.
HTH
Charles

Private Declare Function CreateWindowEX _
Lib "user32" _
Alias "CreateWindowExA" ( _
ByVal dwExStyle As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String, _
ByVal dwStyle As Long, _
ByVal X As Long, _
ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal hWndParent As Long, _
ByVal hMenu As Long, _
ByVal hInstance As Long, _
lpParam As Any) _
As Long

Private Declare Function DestroyWindow _
Lib "user32" ( _
ByVal hwnd As Long) _
As Long

Private Declare Function SetParent _
Lib "user32" ( _
ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) _
As Long

Private Declare Function SendMessage _
Lib "user32" _
Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) _
As Long

Private Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As Long

Dim rTemp
Dim m_frmHwnd As Long
Dim m_pbHwnd As Long
Dim m_strAviFile As String



Sub UpDateProgressBar()
'
iVal = Format(X / iMax, "0%")
Label3.Caption = iVal
SendMessage m_pbHwnd, &H402, ByVal Val(iVal), 0& '&H402
DoEvents
End Sub

Sub SetupProgressBar()
'
m_frmHwnd = FindWindow(vbNullString, Me.Caption)
W = Me.InsideWidth * 4 / 3
H = Me.InsideHeight * 4 / 3
Me.Repaint
'// ProgressBar [Leds: &H50000000] [Smooth: &H50000001]
m_pbHwnd = CreateWindowEX(0, "msctls_progress32", "", &H50000000, 7, H -
90, W - 15, 25, m_frmHwnd, 0, 0, 0)
SetParent m_pbHwnd, m_frmHwnd
DoEvents
End Sub
Sub EndProgressBar()
'
Label3.Caption = ""
DestroyWindow m_pbHwnd
DoEvents
End Sub



"Knokie From Holland" wrote:

Hi,

I have a very large worksheet that collect data from more then 50 other
sheets. During the CalculateFull statement the processor runs at 100% for 80
minutes.
Only the Taskmanager indicates that the program is stil running.

I need a progressbar to get a direct control in my worksheet.

Where is the tip that solves my problem?