Message telling calculation is complete

D

deeds

I am looking for something that would tell end user that the calculation
process is complete. Something simple like text in a cell that appears when
calc is complete. Any ideas? Thanks Much!
 
J

jlclyde

I am looking for something that would tell end user that the calculation
process is complete.  Something simple like text in a cell that appears when
calc is complete.  Any ideas?  Thanks Much!
Click to expand...

In the bottom left hand corner of the screen there is a calculate.
When it is calcualting it says so it also says what % it is at.

Jay
 
D

deeds

Thanks...however, that area of the screen is being taken up by another
application. So, I cannot see the calculation meter. Is there some quick
little messge box I can create or a cell I can put code to tell user when
calc is complete? Thanks.
 
D

deeds

Thanks...these look great...however, can you tell me how I actually add one
of these progress meters to my file? Thanks.
 
G

Gord Dibben

You would have to download the workbook from Andy's site then copy the userform
and code module to your workbook..

I would recommend moving the UserForm1 and Module1 to your Personal.xls so you
have access to all the types of meters for all open workbooks

To access the form and module, open Andy's workbook then Alt + F11 to get to the
Visual Basic Editor.


Gord
 
D

deeds

Sorry to keep going here...but I did copy the userform and module into my
workbook. But what do I need to do to get the progress bar to appear during
a calculation. Thanks again.
 
G

Gord Dibben

Haven't got a clue.

Will have to download and look at it all.

Try to get back to you today but off to Nanaimo this afternoon for shopping.


Gord
 
D

deeds

Thanks for checking on this. Ultimately all I want to accomplish is a little
progress bar OR just something simple like a box that pops up or shows up in
a cell that says..."Calculation Complete" Right now the way the users
computer is working...they can't see the "Excel calc meter" and so, the only
way for them to see the calc is complete is when the curser changes to an
pointing arrow.
Any help/ideas would be great.

Thanks again!
 
J

jlclyde

Thanks for checking on this.  Ultimately all I want to accomplish is a little
progress bar OR just something simple like a box that pops up or shows up in
a cell that says..."Calculation Complete"  Right now the way the users
computer is working...they can't see the "Excel calc meter" and so, the only
way for them to see the calc is complete is when the curser changes to an
pointing arrow.
Any help/ideas would be great.

Thanks again!







- Show quoted text -
Click to expand...

If you add a line to say Range(whatever)= Done. this woudl tell the
operator that it is done. that way you do nto have to fool around
with progress bars. even though they are very cool.

Jay
 
G

Gord Dibben

OK.....no progress bar but a message when calculation is complete.

Sample code.

Runs on a worksheet with data in A1:Z1600 and copies that data to one column in
an added worksheet named "CopyTo"

Sub rowstocol()
Dim wks As Worksheet
Dim colnos As Long
Dim CopytoSheet As Worksheet

If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
wks.Activate
Range("A1").Select
colnos = 26
StartTime = Timer
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.EntireRow.Insert
wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
MsgBox "Calculation is complete. Elapsed Time was " _
& Timer - StartTime & " Seconds"

'MsgBox "Calculation is complete"

End Sub

Add the Timer and msgbox or just the msgbox at end.


Gord
 
D

deeds

Thanks....this looks like I could make work. However, if for instance on
sheet1 the user makes a change to a cell or group of cells...how do I get
something like this to automatically run. In other words I want to mimic the
Excel calculation meter. So, automatically after changes are made and Excel
starts to calculate...a message box appears telling user the calc is
complete. Thanks again.
 
D

deeds

Thanks...where would I put Range(whatever)=done? Would this automatically
run after user makes changes to the worksheet so that it will just pop up
saying calc is complete? Thanks again.
 
G

Gord Dibben

Making a change to some cells on a sheet and having the message box pop up would
require event code behind the worksheet.

But that would run every time any change was made and a calculation took place
even if for a nano-second.

That would annoy the heck out of me.

I would restrict the code to a specfic macro that ran for some some length of
time rather than at any calculation.


Gord
 
D

deeds

Thanks...however, the only time cells would change in this case would be a
long calculation. So, I think a cell based calculation meter would work fine
for this situation. In otherwords, when the user changes a choose box on
this workbook the calc takes awhile. That would be the only change users
would be doing. Any quick and easy way to just have a message pop up in a
cell to let them know calc is complete? Thanks for all your help.
 
G

Gord Dibben

In the sheet module.

Private Sub Worksheet_Calculate()
MsgBox "calculation is complete"
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the above into that
module.

Alt + q to return to the Excel window.


Gord
 
Top