TimeValue in Macro linked to cell in worksheet

[armchairandy]

Hi

I have the below progress bar which goes from 10% to 100% changing every 2 seconds and runs in the status bar. This works fine..

I run 12 main macros that need the progress bar to run and each macro will have a variable amount of records to process, and changes each time I use the worksheet. I know how many records the macros can process each 60 seconds so I can work out the overall run time approximately (and divide by ten for each segment). This can be added to a sheet in the workbook, for each individual macro as I will know how many records are to be processed before I run each macro automatically.


But I would like to add a couple of tweaks:

1. How do I run this at the same time as "Macro #1 "
2. How do I change the colour - at the moment its dark blue and a lighter colour would stand out better- Green, Red or Orange maybe?.
3. Can I add an approximate time remaining to the progress bar in the status area ie after the text?.
Most importantly....
4. How do I link the TimeValue("00:00:02") to a cell, so the macro takes the new time from the updated formula in the worksheet?. This would be the same value 10 times.

Any help would be really appreciated

Andrew

Sub ShowProgress()
' make StatusBar visible

Application.DisplayStatusBar = True
'First Segment
Application.StatusBar = String(1, ChrW(9609)) & "Macro #1 Missing Links: 10% Complete....."
Application.Wait Now + TimeValue("00:00:02")
'Second Segment
Application.StatusBar = String(2, ChrW(9609)) & "Macro #1 Missing Links: 20% Complete....."
Application.Wait Now + TimeValue("00:00:02")
'Third Segment
Application.StatusBar = String(3, ChrW(9609)) & "Macro #1 Missing Links: 30% Complete....."
Application.Wait Now + TimeValue("00:00:02")
'Fourth Segment
Application.StatusBar = String(4, ChrW(9609)) & "Macro #1 Missing Links: 40% Complete....."
Application.Wait Now + TimeValue("00:00:02")
'Fifth Segment
Application.StatusBar = String(5, ChrW(9609)) & "Macro #1 Missing Links: 50% Complete....."
Application.Wait Now + TimeValue("00:00:02")
'Sixth Segment
Application.StatusBar = String(6, ChrW(9609)) & "Macro #1 Missing Links: 60% Complete....."
Application.Wait Now + TimeValue("00:00:02")
'Seventh Segment
Application.StatusBar = String(7, ChrW(9609)) & "Macro #1 Missing Links: 70% Complete....."
Application.Wait Now + TimeValue("00:00:02")
'Eighth Segment
Application.StatusBar = String(8, ChrW(9609)) & "Macro #1 Missing Links: 80% Complete....."
Application.Wait Now + TimeValue("00:00:02")
'Ninth Segment
Application.StatusBar = String(9, ChrW(9609)) & "Macro #1 Missing Links: 90% Complete....."
Application.Wait Now + TimeValue("00:00:02")
'Tenth Segment
Application.StatusBar = String(10, ChrW(9609)) & "Macro #1 Missing Links: 100% Complete"
Application.Wait Now + TimeValue("00:00:02")

'Relinquish the StatusBar
Application.StatusBar = False
End Sub