Hi All,

I'm in the process of making the monumentus shift from Excel 97(!) to
2007 and have just discovered the joys of modeless userforms.

I am forever writing macros to edit over 2500 .csv files and have used
the status bar before now to show progress. Now, however, I have
created a modeless userform that gives a visual indication of progress,
using the code below. Is it possible to add a 'Cancel' button somewhere
on UserForm1 to exit the macro on click?

This is what I have:

>>Sub ProgBar()

>>Dim lngInt As Long
>>Dim lngMax As Long


>>lngMax = InputBox("What is lngMax?")

>>With UserForm1

>>.CommandButton1.Caption = "0% Complete"
>>.Show vbModeless

>>For lngInt = 1 To lngMax
>> .Label1.Width = 234 * (lngInt / lngMax)
>> .CommandButton1.Caption = CInt(lngInt * 100 / lngMax) & "% Complete"
>> .Repaint
>>Next lngInt

>>.Hide
>>.CommandButton1.Caption = "Click Run Macro"
>>.Label1.Width = 0

>>End With

>>End Sub

Thanks in advance, Matt

The flickering is a bit annoying, no?

A better example can be found at
http://www.enhanceddatasystems.com/E...rogressBar.htm

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi All,
>
> I'm in the process of making the monumentus shift from Excel 97(!) to
> 2007 and have just discovered the joys of modeless userforms.
>
> I am forever writing macros to edit over 2500 .csv files and have used
> the status bar before now to show progress. Now, however, I have
> created a modeless userform that gives a visual indication of progress,
> using the code below. Is it possible to add a 'Cancel' button somewhere
> on UserForm1 to exit the macro on click?
>
> This is what I have:
>
>>>Sub ProgBar()
>
>>>Dim lngInt As Long
>>>Dim lngMax As Long
>
>
>>>lngMax = InputBox("What is lngMax?")
>
>>>With UserForm1
>
>>>.CommandButton1.Caption = "0% Complete"
>>>.Show vbModeless
>
>>>For lngInt = 1 To lngMax
>>> .Label1.Width = 234 * (lngInt / lngMax)
>>> .CommandButton1.Caption = CInt(lngInt * 100 / lngMax) & "% Complete"
>>> .Repaint
>>>Next lngInt
>
>>>.Hide
>>>.CommandButton1.Caption = "Click Run Macro"
>>>.Label1.Width = 0
>
>>>End With
>
>>>End Sub
>
> Thanks in advance, Matt
>

Cheers Bob, works perfectly. Doesn't flicker at all on mine either.

Bob Phillips wrote:
> The flickering is a bit annoying, no?
>
> A better example can be found at
> http://www.enhanceddatasystems.com/E...rogressBar.htm
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi All,
> >
> > I'm in the process of making the monumentus shift from Excel 97(!) to
> > 2007 and have just discovered the joys of modeless userforms.
> >
> > I am forever writing macros to edit over 2500 .csv files and have used
> > the status bar before now to show progress. Now, however, I have
> > created a modeless userform that gives a visual indication of progress,
> > using the code below. Is it possible to add a 'Cancel' button somewhere
> > on UserForm1 to exit the macro on click?
> >
> > This is what I have:
> >
> >>>Sub ProgBar()
> >
> >>>Dim lngInt As Long
> >>>Dim lngMax As Long
> >
> >
> >>>lngMax = InputBox("What is lngMax?")
> >
> >>>With UserForm1
> >
> >>>.CommandButton1.Caption = "0% Complete"
> >>>.Show vbModeless
> >
> >>>For lngInt = 1 To lngMax
> >>> .Label1.Width = 234 * (lngInt / lngMax)
> >>> .CommandButton1.Caption = CInt(lngInt * 100 / lngMax) & "% Complete"
> >>> .Repaint
> >>>Next lngInt
> >
> >>>.Hide
> >>>.CommandButton1.Caption = "Click Run Macro"
> >>>.Label1.Width = 0
> >
> >>>End With
> >
> >>>End Sub
> >
> > Thanks in advance, Matt
> >