Progress Indicator

[Panoos64]

Hi all, i prepared a VBA command by which i issue management accounts and it takes around 20-30 seconds when i run it. I would like to add a progress indicator in percentage into the above command, so that when i run it to appear this information on screen, e.g. Management Accounts in progress... I tried to create a user form in vba, but i couldn't managed it. I would be greatly appreciated any support. Thanks all in advance

 

[ranman256]

you need to submit the value to the prog bar. progBar = CurrAmt/TotalAmt
CurrAmt = value where the progress is.
TotalAmt = the total amount.

but since I dont know what your calculating....

 

[Panoos64]

Hi ran, I have no CurrAmt / Total Amnt. Just i export it from Acc. System and i format the rows and columns. E.g. i enter a grey colour on headings and rows with control a/cs numbers, like INCOME, TOTAL INCOME. In addition i arrange the code to prepare borders e.t.c. Below is an extract / template of my P&L layout and it's range is from "A1" till "L120" including last year's columns. In addition i would like to inform you that, i managed to install it in "Status Bar", left down corner in excel, but i prefer it in the middle of the screen. Is there any command by which the status bar will be appear on the screen?
However i would like to thank you for your support and your advice. Have a nice day



[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 175"]PROJECT 6[/TD]
[TD="width: 68"][/TD]
[TD="width: 64"][/TD]
[TD="width: 61"][/TD]
[TD="width: 62"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]PROFIT & LOSS[/TD]
[TD="class: cms_table_xl65, colspan: 2"]JANUARY 2017[/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl68"]MONTH[/TD]
[TD="class: cms_table_xl68"]MONTH %[/TD]
[TD="class: cms_table_xl68"]YEARLY[/TD]
[TD="class: cms_table_xl68"]YEARLY %[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]ACCOUNTS[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]INCOME[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Accommodation[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Food Income[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Beverage Income[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Other Income[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]TOTAL INCOME[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]COST OF SALES[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Food Cost[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Beverage Cost[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]ADMINISTRATION EXPENSES[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Printing & Stationery[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Postages & Transportations[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Rates & Licenses[/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]TOTAL ADMINISTR.EXP.[/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]NET PROFIT / LOSS[/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Panoos64]

Hi all, any help please is appreciated. I would like to explain me how to create a progress indicator user form. Many thanks in advance

 

[My Aswer Is This]

Post the code here showing how you did this:
You said:

In addition i would like to inform you that, i managed to install it in "Status Bar", left down corner in excel, but i prefer it in the middle of the screen. Is there any command by which the status bar will be appear on the screen? ​

 

[Panoos64]

Hi My Ans, just is for design purposes. On left down corner the format of the letters and numbers are too small and so usually we don't see the progress indicator. I know that, there is an option through VBA, by which anyone can create and design such indicators, with his/her own format. Such indicators, whenever we run a code, it appears in the middle of screen. Thanks for your support

 

[MickG]

See here !!
http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/

 

[My Aswer Is This]

See here !!
http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/

This looks like a good plan. I was thinking myself about the same sort of thing. I was going to use a Command Button instead of a frame control but either way would work. My Command button width would expand and its caption would indicate progress. I'm not sure how you would determine how much of the calculation progress has been done at any one moment. That's why I asked user to show me his script for his progress bar he said worked but did not like it on bottom of screen.
I will continue to monitor this thread and see what I can learn.

 

[Panoos64]

Hi Mick, the issue for me is that i didn't manage to create UserForm, so that to be able to copy and run your introducing vba code. I know that, if i manage to create UserForm, i am sure that, your suggested code will work. However i will study again and i will keep on to work on userform and i hope a time to achieve it. Furthermore, i would like to thank you once again for your support and also thanks to My Aswe. Have both a great day.

 

[kelly mort]

See here !!
http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/

Hello MickG,
I have been able to run the code and it works great. But I need help to adapt to the code :
I want to avoid using the writing of the random numbers to the sheet and instead, it should track data an already entered data on a worksheet say sheet1 .

Thanks