progress bar for a refresh on open pivot tables

Jackeb · Mar 27th, 2002, 02:25 AM

Hi,

I have a program that uses pivot tables, these tables refresh their contents on open of the workbook,

my question is how can you provide a dialog or such like that will display the progress of the refresh. Bearing in mind that there are about six pivot tables in the workbook??

Cheers Ed

Tom Schreiner · Mar 27th, 2002, 02:46 AM

Hi
Look up help on Progress bar
You will need a way to figure out how to break down the process into a mathematical figure or as a second choice, you can use an average time though this would vary on different systems.

I would insert the progress bar on a userform, but it appears that you can insert one directly on the worksheet itself if you'd like. If you understand pivot tables, you won't have any problem using a progress bar.
Tom

Jackeb · Mar 27th, 2002, 02:51 AM

Ts, cheers for that... how would I find the progress bar control and how should I attach that to the refresh on open??

Ed

Tom Schreiner · Mar 27th, 2002, 02:56 AM

Hi again.
While in design mode, click on the little tools icon at the bottom of the Control Toolbox menu bar,
then select the Microsoft Progress Bar.
I'm assuming that you have it on your system.
If you need more detail, please repost.
Tom

Jackeb · Mar 27th, 2002, 02:58 AM

TS I hate to say it but because I am using a corperate version of MS2000 i don't have that feature... arrgghhhhh Y cannt they just stop being so paranoid

Jackeb · Mar 27th, 2002, 03:00 AM

Just as a forethought, how would you suggest that I control the userform, ie make it open without pausing the refresh and make it close once the refresh is complete??

Ed

Tom Schreiner · Mar 27th, 2002, 03:07 AM

Well,
Some of the guys on here, namely Dave, have raised some security issues with using non-modal forms. If you are using code to work with your pivot tables, you can place this code in the userform itself or you can display the form non-modal or use doevents.
If you are running Office 2000 then you can use non-modal userforms, if 97, it gets more complicated, but there is a workaround available though I do not have the website link.
Are you using code to refresh your pivot tables?
Reply

Tom Schreiner · Mar 27th, 2002, 03:08 AM

Which feature don't you have?

Jackeb · Mar 27th, 2002, 03:10 AM

Ts,

I am using the refresh on open for the pivot tables, should I code the refresh events ??

I am using COE3.2 version of MS2000...?

Tom Schreiner · Mar 27th, 2002, 03:14 AM

I don't know the first thing about pivot tables.
I'm new to Excel and it appears there is very little that you cannot code.
I'm gonna get one of the other guys to help you. You'll get better help.
Tom

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 02:25 AM	#1
Jackeb Board Regular Join Date: Mar 2002 Posts: 81	Hi, I have a program that uses pivot tables, these tables refresh their contents on open of the workbook, my question is how can you provide a dialog or such like that will display the progress of the refresh. Bearing in mind that there are about six pivot tables in the workbook?? Cheers Ed

Mar 27th, 2002, 02:46 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Look up help on Progress bar You will need a way to figure out how to break down the process into a mathematical figure or as a second choice, you can use an average time though this would vary on different systems. I would insert the progress bar on a userform, but it appears that you can insert one directly on the worksheet itself if you'd like. If you understand pivot tables, you won't have any problem using a progress bar. Tom

Mar 27th, 2002, 02:51 AM	#3
Jackeb Board Regular Join Date: Mar 2002 Posts: 81	Ts, cheers for that... how would I find the progress bar control and how should I attach that to the refresh on open?? Ed

Mar 27th, 2002, 02:56 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi again. While in design mode, click on the little tools icon at the bottom of the Control Toolbox menu bar, then select the Microsoft Progress Bar. I'm assuming that you have it on your system. If you need more detail, please repost. Tom

Mar 27th, 2002, 02:58 AM	#5
Jackeb Board Regular Join Date: Mar 2002 Posts: 81	TS I hate to say it but because I am using a corperate version of MS2000 i don't have that feature... arrgghhhhh Y cannt they just stop being so paranoid

Mar 27th, 2002, 03:00 AM	#6
Jackeb Board Regular Join Date: Mar 2002 Posts: 81	Just as a forethought, how would you suggest that I control the userform, ie make it open without pausing the refresh and make it close once the refresh is complete?? Ed

Mar 27th, 2002, 03:07 AM	#7
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Well, Some of the guys on here, namely Dave, have raised some security issues with using non-modal forms. If you are using code to work with your pivot tables, you can place this code in the userform itself or you can display the form non-modal or use doevents. If you are running Office 2000 then you can use non-modal userforms, if 97, it gets more complicated, but there is a workaround available though I do not have the website link. Are you using code to refresh your pivot tables? Reply

Mar 27th, 2002, 03:08 AM	#8
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Which feature don't you have?

Mar 27th, 2002, 03:10 AM	#9
Jackeb Board Regular Join Date: Mar 2002 Posts: 81	Ts, I am using the refresh on open for the pivot tables, should I code the refresh events ?? I am using COE3.2 version of MS2000...?

Mar 27th, 2002, 03:14 AM	#10
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I don't know the first thing about pivot tables. I'm new to Excel and it appears there is very little that you cannot code. I'm gonna get one of the other guys to help you. You'll get better help. Tom