Need to make progress indicator more efficient

[Jeremy]

ok, a little long here, hang with me...

I have a sheet with a column of names, say process 1, process 2, etc, about
20 total. Next to these names I have a cell that says incomplete (red) or
complete (green).

Elsewhere I have a userform with a listbox in it populated with the same 20
names.

CURRENTLY: When someone clicks on a name in that listbox, that name is
populated in a cell (T1). I then have worksheet code on the sheet with the
indicators as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B10").Value = Range("T1") Then
Range("B10").Value = "Complete"
End If

If Range("B13").Value = Range("T1") Then
Range("B13").Value = "Complete"
End If

....etc for all the items.

It works, problem is, it seems to be killing the tool I'm making as it's
draining resources. Is there any way to make it more efficient? I thought
about a Case function, but I'm not totally sure how to do it and not sure if
it would be faster. I did try adding some code to each if that would ignore
the function if it's already marked complete.

I have an export button that grabs this sheet and a couple of others later
in the tool. I don't need this sheet updated until that point, problem is
when you click on something in the listbox, it outputs that name, but when
you go to another item, it stops outputing that name and does the new name,
that means if I waited until I exported to calculate, it would only get the
status of the last name clicked.

Sorry for the long description, wanted to be detailed.

Thanks

 

[Alan]

Jeremy,

If you mean showing progress while the user waits for it to
complete, I would recommend using a Progress Bar. See
http://digiassn.blogspot.com/2006/03/excel-progress-bar-indicator-with-vba.html.

If you mean speeding up the process you are doing, you could store
the status of each "process" in a single array.
Then, when all that is complete, write out the statuses as you wish.
This takes that part out of the processing loop.
If you are not familiar with arrays, go to Help in the VBE and type
"Arrays," then click on "Using Arrays."

You may want a combination of the above. Alan

 

[Jeremy]

yea, it's not really a progress indicator like a bar that grows. It's simply
a "traffic light" style that says if that item has been accessed or not.

I like the array idea, I'll try to give that shot.

Thanks.

 

[ryguy7272]

Have you ever seen this?
http://www.andypope.info/charts/trafficlight.htm

HTH,
Ryan--

 

[Jeremy]

Very interesting. I never thought of using a chart for this.

Thanks for the link.