Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Meter during macro execution
I know how to programatically change the text that is
displayed in the statusbar, but how do i access the progress meter (displays during saves and calculations) with code. I have looked thru the object browser, mostly in the application object, and havent found any reference to it. I am guessing that it is accessable via the panels collection of the status bar but i cant find any documentation about it. I am open to other suggestions. with out going in to a lot of detail, the setup is: 1) user clicks button 2) code executes (duration varies) 3) control is returned to user i would like some way of informing the user that the macro is still running and not frozen. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Meter during macro execution
Rob
What version of Excel are you using as i beleive the later version have a progress bar built in to it that you can access. If you are using 97 or earlier you would need to build your own using form to display it. I use my own progress built progress bar within access 9 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Meter during macro execution
Im using excel 2000. how do i access it via the object
model (i.e. application.statusbar.progressmeter)? -----Original Message----- Rob What version of Excel are you using as i beleive the later versions have a progress bar built in to it that you can access. If you are using 97 or earlier you would need to build your own using a form to display it. I use my own progress built progress bar within access 97 --- Message posted from http://www.ExcelForum.com/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Meter during macro execution
Posted by Michael Pierron (May 28, 2004)
Microsoft.public.excel.programming Private Declare Function FindWindow& Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName$, ByVal lpWindowName$) Private Declare Function CreateWindowEX& Lib "user32" Alias _ "CreateWindowExA" (ByVal dwExStyle&, ByVal lpClassName$ _ , ByVal lpWindowName$, ByVal dwStyle&, ByVal x&, ByVal y& _ , ByVal nWidth&, ByVal nHeight&, ByVal hWndParent& _ , ByVal hMenu&, ByVal hInstance&, lpParam As Any) Private Declare Function DestroyWindow& Lib "user32" (ByVal hWnd&) Private Declare Function SendMessage& Lib "user32" Alias _ "SendMessageA" (ByVal hWnd&, ByVal wMsg&, ByVal wParam&, lParam As Any) Private Declare Function GetClientRect& Lib "user32" _ (ByVal hWnd&, lpRect As RECT) Private Declare Function FindWindowEx& Lib "user32" Alias _ "FindWindowExA" (ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1$, ByVal lpsz2$) Private Type RECT cl As Long ct As Long cr As Long cb As Long End Type Sub PBarDraw() Dim BarState As Boolean Dim hWnd&, pbhWnd&, y&, h&, i&, R As RECT hWnd = FindWindow(vbNullString, Application.Caption) hWnd = FindWindowEx(hWnd, ByVal 0&, "EXCEL4", vbNullString) GetClientRect hWnd, R h = (R.cb - R.ct) - 6: y = R.ct + 3 pbhWnd = CreateWindowEX(0, "msctls_progress32", "" _ , &H50000000, 35, y, 185, h, hWnd, 0&, 0&, 0&) SendMessage pbhWnd, &H409, 0, ByVal RGB(0, 0, 125) BarState = Application.DisplayStatusBar Application.DisplayStatusBar = True For i = 1 To 50000 DoEvents Application.StatusBar = Format(i / 50000, "0%") SendMessage pbhWnd, &H402, Val(Application.StatusBar), 0 Next i DestroyWindow pbhWnd Application.StatusBar = False Application.DisplayStatusBar = BarState End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... Im using excel 2000. how do i access it via the object model (i.e. application.statusbar.progressmeter)? -----Original Message----- Rob What version of Excel are you using as i beleive the later versions have a progress bar built in to it that you can access. If you are using 97 or earlier you would need to build your own using a form to display it. I use my own progress built progress bar within access 97 --- Message posted from http://www.ExcelForum.com/ . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Meter during macro execution
I have a couple of progress meter examples on my web site.
One in the Status Bar (based on post by Michel Pierron) One on a Command Bar (toolbar) -- Rob van Gelder - http://www.vangelder.co.nz/excel "Rob" wrote in message ... I know how to programatically change the text that is displayed in the statusbar, but how do i access the progress meter (displays during saves and calculations) with code. I have looked thru the object browser, mostly in the application object, and havent found any reference to it. I am guessing that it is accessable via the panels collection of the status bar but i cant find any documentation about it. I am open to other suggestions. with out going in to a lot of detail, the setup is: 1) user clicks button 2) code executes (duration varies) 3) control is returned to user i would like some way of informing the user that the macro is still running and not frozen. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Meter during macro execution
Tom,
If I have a macro like: Dim X as String Sub Test() -----Original Message----- Posted by Michael Pierron (May 28, 2004) Microsoft.public.excel.programming Private Declare Function FindWindow& Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName$, ByVal lpWindowName$) Private Declare Function CreateWindowEX& Lib "user32" Alias _ "CreateWindowExA" (ByVal dwExStyle&, ByVal lpClassName$ _ , ByVal lpWindowName$, ByVal dwStyle&, ByVal x&, ByVal y& _ , ByVal nWidth&, ByVal nHeight&, ByVal hWndParent& _ , ByVal hMenu&, ByVal hInstance&, lpParam As Any) Private Declare Function DestroyWindow& Lib "user32" (ByVal hWnd&) Private Declare Function SendMessage& Lib "user32" Alias _ "SendMessageA" (ByVal hWnd&, ByVal wMsg&, ByVal wParam&, lParam As Any) Private Declare Function GetClientRect& Lib "user32" _ (ByVal hWnd&, lpRect As RECT) Private Declare Function FindWindowEx& Lib "user32" Alias _ "FindWindowExA" (ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1 $, ByVal lpsz2$) Private Type RECT cl As Long ct As Long cr As Long cb As Long End Type Sub PBarDraw() Dim BarState As Boolean Dim hWnd&, pbhWnd&, y&, h&, i&, R As RECT hWnd = FindWindow(vbNullString, Application.Caption) hWnd = FindWindowEx(hWnd, ByVal 0&, "EXCEL4", vbNullString) GetClientRect hWnd, R h = (R.cb - R.ct) - 6: y = R.ct + 3 pbhWnd = CreateWindowEX(0, "msctls_progress32", "" _ , &H50000000, 35, y, 185, h, hWnd, 0&, 0&, 0&) SendMessage pbhWnd, &H409, 0, ByVal RGB(0, 0, 125) BarState = Application.DisplayStatusBar Application.DisplayStatusBar = True For i = 1 To 50000 DoEvents Application.StatusBar = Format(i / 50000, "0%") SendMessage pbhWnd, &H402, Val (Application.StatusBar), 0 Next i DestroyWindow pbhWnd Application.StatusBar = False Application.DisplayStatusBar = BarState End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... Im using excel 2000. how do i access it via the object model (i.e. application.statusbar.progressmeter)? -----Original Message----- Rob What version of Excel are you using as i beleive the later versions have a progress bar built in to it that you can access. If you are using 97 or earlier you would need to build your own using a form to display it. I use my own progress built progress bar within access 97 --- Message posted from http://www.ExcelForum.com/ . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Meter during macro execution
Tom,
Sorry if this is the second post - hit the wrong key earlier. If I have a macro like: Dim I as String Sub Test() Code Code End Sub How/where does the code below fit in? I don't know where to put it and how it gets "invoked." -----Original Message----- Posted by Michael Pierron (May 28, 2004) Microsoft.public.excel.programming Private Declare Function FindWindow& Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName$, ByVal lpWindowName$) Private Declare Function CreateWindowEX& Lib "user32" Alias _ "CreateWindowExA" (ByVal dwExStyle&, ByVal lpClassName$ _ , ByVal lpWindowName$, ByVal dwStyle&, ByVal x&, ByVal y& _ , ByVal nWidth&, ByVal nHeight&, ByVal hWndParent& _ , ByVal hMenu&, ByVal hInstance&, lpParam As Any) Private Declare Function DestroyWindow& Lib "user32" (ByVal hWnd&) Private Declare Function SendMessage& Lib "user32" Alias _ "SendMessageA" (ByVal hWnd&, ByVal wMsg&, ByVal wParam&, lParam As Any) Private Declare Function GetClientRect& Lib "user32" _ (ByVal hWnd&, lpRect As RECT) Private Declare Function FindWindowEx& Lib "user32" Alias _ "FindWindowExA" (ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1 $, ByVal lpsz2$) Private Type RECT cl As Long ct As Long cr As Long cb As Long End Type Sub PBarDraw() Dim BarState As Boolean Dim hWnd&, pbhWnd&, y&, h&, i&, R As RECT hWnd = FindWindow(vbNullString, Application.Caption) hWnd = FindWindowEx(hWnd, ByVal 0&, "EXCEL4", vbNullString) GetClientRect hWnd, R h = (R.cb - R.ct) - 6: y = R.ct + 3 pbhWnd = CreateWindowEX(0, "msctls_progress32", "" _ , &H50000000, 35, y, 185, h, hWnd, 0&, 0&, 0&) SendMessage pbhWnd, &H409, 0, ByVal RGB(0, 0, 125) BarState = Application.DisplayStatusBar Application.DisplayStatusBar = True For i = 1 To 50000 DoEvents Application.StatusBar = Format(i / 50000, "0%") SendMessage pbhWnd, &H402, Val (Application.StatusBar), 0 Next i DestroyWindow pbhWnd Application.StatusBar = False Application.DisplayStatusBar = BarState End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... Im using excel 2000. how do i access it via the object model (i.e. application.statusbar.progressmeter)? -----Original Message----- Rob What version of Excel are you using as i beleive the later versions have a progress bar built in to it that you can access. If you are using 97 or earlier you would need to build your own using a form to display it. I use my own progress built progress bar within access 97 --- Message posted from http://www.ExcelForum.com/ . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Meter during macro execution
This is the part of the macro where the statusbar is advanced:
For i = 1 To 50000 DoEvents Application.StatusBar = Format(i / 50000, "0%") SendMessage pbhWnd, &H402, Val(Application.StatusBar), 0 Next i so you would adjust the portion of code above facilitate doing your work and updating the progress bar. Your code has to make the determination on what progress is being made and then set the progress bar to reflect that. In Michael's demo, he loops 50000 times, so he passes the val(format(i/50000,"0%")) to the API that is managing the progress bar. -- Regards, Tom Ogilvy "Ken" wrote in message ... Tom, Sorry if this is the second post - hit the wrong key earlier. If I have a macro like: Dim I as String Sub Test() Code Code End Sub How/where does the code below fit in? I don't know where to put it and how it gets "invoked." -----Original Message----- Posted by Michael Pierron (May 28, 2004) Microsoft.public.excel.programming Private Declare Function FindWindow& Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName$, ByVal lpWindowName$) Private Declare Function CreateWindowEX& Lib "user32" Alias _ "CreateWindowExA" (ByVal dwExStyle&, ByVal lpClassName$ _ , ByVal lpWindowName$, ByVal dwStyle&, ByVal x&, ByVal y& _ , ByVal nWidth&, ByVal nHeight&, ByVal hWndParent& _ , ByVal hMenu&, ByVal hInstance&, lpParam As Any) Private Declare Function DestroyWindow& Lib "user32" (ByVal hWnd&) Private Declare Function SendMessage& Lib "user32" Alias _ "SendMessageA" (ByVal hWnd&, ByVal wMsg&, ByVal wParam&, lParam As Any) Private Declare Function GetClientRect& Lib "user32" _ (ByVal hWnd&, lpRect As RECT) Private Declare Function FindWindowEx& Lib "user32" Alias _ "FindWindowExA" (ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1 $, ByVal lpsz2$) Private Type RECT cl As Long ct As Long cr As Long cb As Long End Type Sub PBarDraw() Dim BarState As Boolean Dim hWnd&, pbhWnd&, y&, h&, i&, R As RECT hWnd = FindWindow(vbNullString, Application.Caption) hWnd = FindWindowEx(hWnd, ByVal 0&, "EXCEL4", vbNullString) GetClientRect hWnd, R h = (R.cb - R.ct) - 6: y = R.ct + 3 pbhWnd = CreateWindowEX(0, "msctls_progress32", "" _ , &H50000000, 35, y, 185, h, hWnd, 0&, 0&, 0&) SendMessage pbhWnd, &H409, 0, ByVal RGB(0, 0, 125) BarState = Application.DisplayStatusBar Application.DisplayStatusBar = True For i = 1 To 50000 DoEvents Application.StatusBar = Format(i / 50000, "0%") SendMessage pbhWnd, &H402, Val (Application.StatusBar), 0 Next i DestroyWindow pbhWnd Application.StatusBar = False Application.DisplayStatusBar = BarState End Sub -- Regards, Tom Ogilvy "Rob" wrote in message ... Im using excel 2000. how do i access it via the object model (i.e. application.statusbar.progressmeter)? -----Original Message----- Rob What version of Excel are you using as i beleive the later versions have a progress bar built in to it that you can access. If you are using 97 or earlier you would need to build your own using a form to display it. I use my own progress built progress bar within access 97 --- Message posted from http://www.ExcelForum.com/ . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
progress meter for Calculation? | Excel Discussion (Misc queries) | |||
Macro Execution | Excel Discussion (Misc queries) | |||
Automatic execution of macro | Excel Programming | |||
Triggering Macro Execution | Excel Programming | |||
Restricting Macro Execution | Excel Programming |