To add a ProgressBar in Excel 2003 Macro

[sugaprasad]

Hi Everyone,

I'm building a Excel Macro. This Macro updates a word document and save them in different name for each iteration. This takes a long time. so to inform the user that macro is functioning in backgroud i just want to let them know through a Progress Bar. Surprisingly Progressbar addin not working in Excel 2003. So i'm using Userform & Label combination. I'm using two Labels one with maximum width set and another one set with minimum width. so for each iteration the later Label's width will keep on increasing.

I'm facing a problem here. My logic is working fine and macro does wat it is supposed to do. But the userform Hanging during the execution. Its turining white while the code is executing. I worked in debug mode and label widths are getting updated correctly. Can u please help me what i'm missing here. attaching the screenshots and code. Thanks in advance.

Sub Open_Word_Document()

Dim ABC_Template_Path As String
Dim ABC_Name As String, SBC_Ouput_Path As String
Dim objWord As Object 'New Word.Application
Dim Doc As Object 'Word.Document
Dim StartRow As Integer
Dim EndRow As Integer
Dim oExcel As Object
Dim oSheet As Object
Dim oBook As Object
Dim sExcelPath As String
Dim range As Object
Dim sMPC, sCC, sSegment, sSt, sPt, sSDate, sEDate, sService, sDed, sPl As String
Dim strDate As String
Dim datNow As Date
Dim NewPercent As Double
Dim FSO As Object, Log_File, Log_File_Path As String


Set oSheet = ActiveWorkbook.Worksheets(1)
Set range = oSheet.UsedRange


    
    StartRow = range.Cells(2, 2)
    EndRow = range.Cells(3, 2)



Set objWord = CreateObject("Word.Application")

objWord.Visible = False
objWord.AutomationSecurity = msoAutomationSecurityLow

UserForm1.Show

SBC_Ouput_Path = "C:\ABC\" 'Result Folder

'******************** Added to Create the Log - 12/06 *********************

'To create the log file

Log_File_Path = ActiveWorkbook.Path & "\LOG_FILE.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(Log_File_Path) Then FSO.DeleteFile Log_File_Path, True

Set Log_File = FSO.CreateTextFile(Log_File_Path, True)
Log_File.WriteLine ("******* ABC Execution Log report ******* Created On: " & Now)
Log_File.WriteLine ("---------------------------------------------------------------------------")
Log_File.WriteLine ("MPL|STATUS|ERROR MESSAGE")
Log_File.WriteLine ("                                                                           ")
Log_File.Close

Set Log_File = Nothing
Set FSO = Nothing



'Pass the Log_File_Path as last parameter to the word macro

'*******************************************************************

'runnning loop for each row in the input sheet
For RowIndex = StartRow To EndRow 'Total rows in the input data sheet



    ABC_Template_Path = "C:\Template_Modified_V4.0.doc"
    Set Doc = objWord.Documents.Open    
    sMPC = range.Cells(RowIndex, 1)
    sCC = range.Cells(RowIndex, 3)
    sSegment = range.Cells(RowIndex, 4)
    sSt = range.Cells(RowIndex, 5)
    sPt = range.Cells(RowIndex, 8)
    sSDate = range.Cells(RowIndex, 225)
    sEDate = range.Cells(RowIndex, 226)
    sService = range.Cells(RowIndex, 223)
    sDed = range.Cells(RowIndex, 227)
    sPl = range.Cells(RowIndex, 224)
    
       
    
    'Read all values from the input sheet for a specific row
     objWord.Run "Driver", sMPC, sCC, sSegment, sSt, sPt, sSDate, sEDate, sService, sDed, sPl, Log_File_Path
    
    datNow = Now()
    strDate = Format(datNow, "mm-dd-yyyy hh-mm-ss")
    Doc.SaveAs ABC_Ouput_Path & sMPC & "_" & sSt & "_" & strDate & ".doc"
    Doc.Close

 'Progress Bar   

 For iCnt = StartRow To EndRow

    If StartRow > EndRow Then
       Exit For
   End If
   NewPercent = StartRow / EndRow
   UserForm1.Label4.Width = NewPercent * UserForm1.Label3.Width
Next iCnt

 StartRow = StartRow + 1
Next

objWord.Application.Quit
UserForm1.Hide
Set objWord = Nothing
Set Doc = Nothing

MsgBox "Documents are Ready"



End Sub

Original Userform.JPGUserform Hanging.JPG

[sugaprasad]

Just want to know if anyone has any idea on this issue

[buran]

Hi,
Try to force repaint the user form, using .Repaint method of the UserForm. Also I would suggest to add DoEvents statement if the loop is too long. That should prevent from hang up.

Buran

[sugaprasad]

Thanks Buran. .Repaint is working perfectly