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!

On Apr 25, 10:04*am, deeds wrote:
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!

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

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.

"jlclyde" wrote:

On Apr 25, 10:04 am, deeds wrote:
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!

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

See Andy Pope's site for a workbook with various progess bars.

http://www.andypope.info/vba/pmeter.htm


Gord Dibben MS Excel MVP

On Mon, 28 Apr 2008 06:41:03 -0700, deeds
wrote:

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.

"jlclyde" wrote:

On Apr 25, 10:04 am, deeds wrote:
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!

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

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

"Gord Dibben" wrote:

See Andy Pope's site for a workbook with various progess bars.

http://www.andypope.info/vba/pmeter.htm


Gord Dibben MS Excel MVP

On Mon, 28 Apr 2008 06:41:03 -0700, deeds
wrote:

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.

"jlclyde" wrote:

On Apr 25, 10:04 am, deeds wrote:
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!

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

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


On Tue, 29 Apr 2008 07:25:03 -0700, deeds
wrote:

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

"Gord Dibben" wrote:

See Andy Pope's site for a workbook with various progess bars.

http://www.andypope.info/vba/pmeter.htm


Gord Dibben MS Excel MVP

On Mon, 28 Apr 2008 06:41:03 -0700, deeds
wrote:

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.

"jlclyde" wrote:

On Apr 25, 10:04 am, deeds wrote:
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!

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

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.

"Gord Dibben" wrote:

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


On Tue, 29 Apr 2008 07:25:03 -0700, deeds
wrote:

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

"Gord Dibben" wrote:

See Andy Pope's site for a workbook with various progess bars.

http://www.andypope.info/vba/pmeter.htm


Gord Dibben MS Excel MVP

On Mon, 28 Apr 2008 06:41:03 -0700, deeds
wrote:

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.

"jlclyde" wrote:

On Apr 25, 10:04 am, deeds wrote:
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!

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

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

On Tue, 29 Apr 2008 10:30:04 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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


On Tue, 29 Apr 2008 07:25:03 -0700, deeds
wrote:

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

"Gord Dibben" wrote:

See Andy Pope's site for a workbook with various progess bars.

http://www.andypope.info/vba/pmeter.htm


Gord Dibben MS Excel MVP

On Mon, 28 Apr 2008 06:41:03 -0700, deeds
wrote:

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.

"jlclyde" wrote:

On Apr 25, 10:04 am, deeds wrote:
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!

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

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!

"Gord Dibben" wrote:

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

On Tue, 29 Apr 2008 10:30:04 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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


On Tue, 29 Apr 2008 07:25:03 -0700, deeds
wrote:

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

"Gord Dibben" wrote:

See Andy Pope's site for a workbook with various progess bars.

http://www.andypope.info/vba/pmeter.htm


Gord Dibben MS Excel MVP

On Mon, 28 Apr 2008 06:41:03 -0700, deeds
wrote:

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.

"jlclyde" wrote:

On Apr 25, 10:04 am, deeds wrote:
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!

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

On Apr 30, 10:33*am, deeds wrote:
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!



"Gord Dibben" wrote:
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

On Tue, 29 Apr 2008 10:30:04 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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

On Tue, 29 Apr 2008 07:25:03 -0700, deeds
wrote:

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

"Gord Dibben" wrote:

See Andy Pope's site for a workbook with various progess bars.

http://www.andypope.info/vba/pmeter.htm

Gord Dibben *MS Excel MVP

On Mon, 28 Apr 2008 06:41:03 -0700, deeds
wrote:

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.

"jlclyde" wrote:

On Apr 25, 10:04 am, deeds wrote:
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!

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- Hide quoted text -

- Show quoted text -

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

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

On Wed, 30 Apr 2008 08:33:01 -0700, deeds
wrote:

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!

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.

"Gord Dibben" wrote:

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

On Wed, 30 Apr 2008 08:33:01 -0700, deeds
wrote:

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!

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.

"jlclyde" wrote:

On Apr 30, 10:33 am, deeds wrote:
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!



"Gord Dibben" wrote:
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

On Tue, 29 Apr 2008 10:30:04 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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

On Tue, 29 Apr 2008 07:25:03 -0700, deeds
wrote:

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

"Gord Dibben" wrote:

See Andy Pope's site for a workbook with various progess bars.

http://www.andypope.info/vba/pmeter.htm

Gord Dibben MS Excel MVP

On Mon, 28 Apr 2008 06:41:03 -0700, deeds
wrote:

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.

"jlclyde" wrote:

On Apr 25, 10:04 am, deeds wrote:
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!

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- Hide quoted text -

- Show quoted text -

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

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



On Thu, 1 May 2008 07:08:00 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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

On Wed, 30 Apr 2008 08:33:01 -0700, deeds
wrote:

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!

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.

"Gord Dibben" wrote:

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



On Thu, 1 May 2008 07:08:00 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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

On Wed, 30 Apr 2008 08:33:01 -0700, deeds
wrote:

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!

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

On Fri, 2 May 2008 07:19:01 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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



On Thu, 1 May 2008 07:08:00 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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

On Wed, 30 Apr 2008 08:33:01 -0700, deeds
wrote:

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!

Perfect! Turned out to be quite simple...sorry about any confusion. Thanks
so much!

"Gord Dibben" wrote:

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

On Fri, 2 May 2008 07:19:01 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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



On Thu, 1 May 2008 07:08:00 -0700, deeds
wrote:

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.

"Gord Dibben" wrote:

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

On Wed, 30 Apr 2008 08:33:01 -0700, deeds
wrote:

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!