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

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

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.


"Sriram" wrote:

> 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

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


"Muhammed Rafeek M" wrote:

> 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.
>
>
> "Sriram" wrote:
>
> > 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

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)

"Sriram" <[email protected]> wrote in message
news:[email protected]...
> 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

Yes you have to insert Progressbar to your worksheet: if you need more, mail
me: [email protected]

"Sriram" wrote:

> 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
>
>
> "Muhammed Rafeek M" wrote:
>
> > 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.
> >
> >
> > "Sriram" wrote:
> >
> > > 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

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

"Muhammed Rafeek M" wrote:

> Yes you have to insert Progressbar to your worksheet: if you need more, mail
> me: [email protected]
>
> "Sriram" wrote:
>
> > 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
> >
> >
> > "Muhammed Rafeek M" wrote:
> >
> > > 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.
> > >
> > >
> > > "Sriram" wrote:
> > >
> > > > 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

Sriram,
send me your code, i do update and will send you imm. [email protected]

"Sriram" wrote:

> 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
>
> "Muhammed Rafeek M" wrote:
>
> > Yes you have to insert Progressbar to your worksheet: if you need more, mail
> > me: [email protected]
> >
> > "Sriram" wrote:
> >
> > > 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
> > >
> > >
> > > "Muhammed Rafeek M" wrote:
> > >
> > > > 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.
> > > >
> > > >
> > > > "Sriram" wrote:
> > > >
> > > > > 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