NEED Help - How to capture the Event's progress in Progress Bar

[Guest]

Dear Friends,

I want to find out and capture the event's progress in a Progress Bar in
excel macro.

Example, in my project when a sheet is activated, there will some area where
the user can enter their data and the rest will be locked and hidden.

For example, the user can enter the data starting from Column 'A' till
Colimn 'AZ'. So I've written a code to hide the columns from BA till end.
While this process is running excel is busy and we can't do anything. This
process is running for around 2-3 seconds only.

But my wish is that, if this process is running, then a progress bar should
be shown, whose starting value and ending value should be in line with the
hiding event process's value. So that when the excel starts hiding the Column
'BA', the progress bar should start from 0 (zero) and will progress along
with the event's process and should End, when all the mentioned columns are
Hidden by the code.

This I want to implement for all sort of background process the code does
with my excel sheet or book.

Kindly help me out to achieve this.

Thanks,
Sriram

 

[Guest]

see one sample progress bar without using UserForm:

Private Sub Worksheet_Activate()
ProgressBar1.Visible = False
End Sub
Sub ShowProgressBar()
Dim i As Variant
ProgressBar1.Visible = True
ProgressBar1.Min = 0
ProgressBar1.Max = 10000
ProgressBar1.Top = 150
ProgressBar1.Left = 250
For i = 0 To 10000
ProgressBar1.Value = i
'write ur programm
Next i
ProgressBar1.Visible = False
End Sub

Note: Inser progressbar to your worksheet and copy above command and paste
to Sheet Module.

 

[Guest]

But you mean Mr. Md.

As you mentioned, in your code 'ProgressBar1', whether I've to keep a
progress bar with a name like this. or a simple code.

Pleas clear me to proceed further. I want to implement right now itself.

Thanks & Regards,
Sriram

 

[Bob Phillips]

Your problem is that with any progress bar, you need to be able to trap some
event to update the bar. If you issue a singleton Column(x:y).Hide (that is
a logical syntax, not necessarily actual), then you do not get another
opportunity until that method finishes. The only way I could see you doing
it is to hide them within a loop, and issue a PB update at every iteration
of the loop.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Yes you have to insert Progressbar to your worksheet: if you need more, mail
me: (e-mail address removed)

 

[Guest]

Yep I inserted a progress bar and now the time taken by the script is less
and it is working faster than earlier. i can't see the progressbar updating
and running from 0-10000.

Kindly tell me what to do.

Sriram

 

[Guest]

Sriram,
send me your code, i do update and will send you imm. (e-mail address removed)