Make a changing cell visible while still running
 

I want to keep a running tally in a cell while the vba code is still
running. So I say sometheing like...

For K = 1 to 100
TopicWS.Cells(1, 2).Value = K
'-- do something which takes a second or two
Next

But the new values of K (1..99) do not become visible in Cell(1, 2)
until the program stops, at which point only the last one (100) is
showing.

How do I get it to update visibly while still running?

Thanks. --David.

Make a changing cell visible while still running
 

Whoops.. that simple code does the right thing. OK, what might I be
doing to prevent the cell from visibly updating in my actual program?

Well, I'm reading a text file line by line into cells of a different
worksheet than the one with the changing cell. Basically, I am trying
to keep the user "entertained" while a bunch of such files are read, by
updating that cell with a count each time a file read starts. Otherwise
the program just sits there silently while the files are read, and the
user has no idea if it is hung or progressing properly.

But, only the final value shows up... after the program stops.

I did start the whole process by processing
Workbook_SheetSelectionChange and Workbook_SheetBeforeDoubleClick
events. But by the time I start this loop, I am not doing anything
special with those events anymore.

Hmmm, maybe that is a clue. I initiate the file reads with a
double-click on a specific cell. That seems to leave the cursor in
editing mode inside that cell when the code stops. Could that be
preventing the update? If so, how do I get out of that? I guess I
could throw up a dialog with a button to initiate the reads, but the
cell double-click seemed so simple and elegant.

Thanks. --David.

Thanks. --David.

Make a changing cell visible while still running
 

David,

Use Cancel in your double-click event to cancel the normal double-click
behavior:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
Dim i As Long

'cancels the normal double-click behavior
Cancel = True
For i = 1 To 1000
ActiveCell.Value = i
Next i

End Sub

Or use Application.ScreenUpdating = False at the beginning and True at the
end and maybe it will go so fast you don't have to distract them.

hth,

Doug


"David Lewis" wrote in message
oups.com...
Whoops.. that simple code does the right thing. OK, what might I be
doing to prevent the cell from visibly updating in my actual program?

Well, I'm reading a text file line by line into cells of a different
worksheet than the one with the changing cell. Basically, I am trying
to keep the user "entertained" while a bunch of such files are read, by
updating that cell with a count each time a file read starts. Otherwise
the program just sits there silently while the files are read, and the
user has no idea if it is hung or progressing properly.

But, only the final value shows up... after the program stops.

I did start the whole process by processing
Workbook_SheetSelectionChange and Workbook_SheetBeforeDoubleClick
events. But by the time I start this loop, I am not doing anything
special with those events anymore.

Hmmm, maybe that is a clue. I initiate the file reads with a
double-click on a specific cell. That seems to leave the cursor in
editing mode inside that cell when the code stops. Could that be
preventing the update? If so, how do I get out of that? I guess I
could throw up a dialog with a button to initiate the reads, but the
cell double-click seemed so simple and elegant.

Thanks. --David.

Thanks. --David.

Make a changing cell visible while still running
 

Try

DoEvents

after each update. Or use the statusbar instead.

Tim


"David Lewis" wrote in message
oups.com...
Whoops.. that simple code does the right thing. OK, what might I be
doing to prevent the cell from visibly updating in my actual program?

Well, I'm reading a text file line by line into cells of a different
worksheet than the one with the changing cell. Basically, I am trying
to keep the user "entertained" while a bunch of such files are read, by
updating that cell with a count each time a file read starts. Otherwise
the program just sits there silently while the files are read, and the
user has no idea if it is hung or progressing properly.

But, only the final value shows up... after the program stops.

I did start the whole process by processing
Workbook_SheetSelectionChange and Workbook_SheetBeforeDoubleClick
events. But by the time I start this loop, I am not doing anything
special with those events anymore.

Hmmm, maybe that is a clue. I initiate the file reads with a
double-click on a specific cell. That seems to leave the cursor in
editing mode inside that cell when the code stops. Could that be
preventing the update? If so, how do I get out of that? I guess I
could throw up a dialog with a button to initiate the reads, but the
cell double-click seemed so simple and elegant.

Thanks. --David.

Thanks. --David.

Make a changing cell visible while still running
 

Thanks for the suggestions. All the processing takes place during the
double-click handler, so I don't think "cancel" would do it. (Related
question -- I'm uncomfortable with that; how do I let the double-click
handler terminate and then execute the real code? Seems like there
will be no more events to trigger code. Maybe I should just go to a
dialog, which I was trying to avoid for no good reason beyond laziness
-- and now the challenge ;-)

I do need the "entertainment", since it takes quite a while to process
things. And, I want it in the spreadsheet itself in straegic locations.
It's not really just entertainment, it's a meaningful progress
indicator.

DoEvents does not help. SendKey neither.

But throwing in a delay loop does the trick! I'm now experimenting with
how long a delay is necessary. I don't want to use the Wait function,
since that only goes in whole seconds, and I definitely don't need to
delay more than a fraction. Any ideas?

Thank. --David.

Make a changing cell visible while still running
 

David,

The cancel only cancels the normal double-click behavior of entering
cell-edit mode. Not very risky as far as I can see, certainly less so than
SendKeys, for example.

Throwing in a Wait so that you can tell your users how long they'll have to
wait seems counter-productive.

I agree that there are other better ways to do progress indicators. Google
is your friend.

hth,

Doug


"David Lewis" wrote in message
oups.com...
Thanks for the suggestions. All the processing takes place during the
double-click handler, so I don't think "cancel" would do it. (Related
question -- I'm uncomfortable with that; how do I let the double-click
handler terminate and then execute the real code? Seems like there
will be no more events to trigger code. Maybe I should just go to a
dialog, which I was trying to avoid for no good reason beyond laziness
-- and now the challenge ;-)

I do need the "entertainment", since it takes quite a while to process
things. And, I want it in the spreadsheet itself in straegic locations.
It's not really just entertainment, it's a meaningful progress
indicator.

DoEvents does not help. SendKey neither.

But throwing in a delay loop does the trick! I'm now experimenting with
how long a delay is necessary. I don't want to use the Wait function,
since that only goes in whole seconds, and I definitely don't need to
delay more than a fraction. Any ideas?

Thank. --David.

Make a changing cell visible while still running
 

I now suspect that it is simply a timing problem -- if you try to
update a cell too quickly in succession, it does not change at all.
Does that ring a bell? It's easy enough to test.

As for progress indicators, let me assure you that what I am doing
makes some sense. The user picks a subset of lines from about 40 on the
screen, and then hits "go" (presently a double-click on a cell, but
could be a dialog). The system then process a bunch of files
associated with each chosen line.

So, rather than a single, anonymous progress bar where you really have
no idea what is actually happening, I thought it would be nice to show
progress for each line's files *on that line* -- like "finding files
for line 3", "processing file 3.01", "processing file 3.02", etc, "Done
with 74 files for 3", then go on the "finding files for line 7" etc,
where the progress indicators would appear on the relevant lines of the
spreadsheet, right next to each selection.

Anyway, I got it working. Thanks for the good discussion on the topic.
I learned some things, even if they didn't solve the problem at hand.
If I check out the timing issue, I'll start a new thread.

Thanks. --David.