Link to home
Start Free TrialLog in
Avatar of Saeed ali khan
Saeed ali khan

asked on

how i can add a progress bar with this code ?, anybody can help me?

Private Sub UserForm_Activate()
Dim objFSO As Object
Dim objFolder As Object
Dim count As Long
Dim Path As String
Dim ws As Worksheet
Application.ScreenUpdating = False

ActiveWorkbook.Unprotect
Set ws = ThisWorkbook.Sheets("DATA")
ws.Visible = True

ws.Select
ws.Unprotect
Range("D4:E2000").Select
Selection.Clear

Path = Sheet1.Range("H3")
If Sheet1.Range("H3") = "" Then
MsgBox "No Room or Path ware Founded"
On Error GoTo 0
Exit Sub
End If



Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Path)

count = 4
For Each objFSO In objFolder.Files


ws.Cells(count, 4).Value = objFSO.Name
ws.Cells(count, 5).Value = objFSO.Path

count = count + 1
Next

ws.Protect

ActiveWorkbook.Protect
MsgBox "Proses completed successfully thank for Waiting"
Sheet1.Select
Sheet1.Protect

End Sub
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Andy Pope has one of the best collections of Progress Bars here

Why do you need a Progress bar, I always find it better to make sure that the code is running as efficiently as possible and never use them. Unless you have thousands of files then I can't see why you need one
As Roy mentioned you don't have long data to use Progress Bar, if I were you I would use Progress Percentage on Status Bar at the left bottom.
Try below code:
Private Sub UserForm_Activate()
Dim objFSO As Object
Dim objFolder As Object
Dim count As Long
Dim Path As String
Dim ws As Worksheet
Call FunctionalityOff

ActiveWorkbook.Unprotect
Set ws = ThisWorkbook.Sheets("DATA")
ws.Visible = True

ws.Select
ws.Unprotect
Range("D4:E2000").Select
Selection.Clear

Path = Sheet1.Range("H3")
If Sheet1.Range("H3") = "" Then
MsgBox "No Room or Path ware Founded"
On Error GoTo 0
Exit Sub
End If



Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Path)

count = 4
For Each objFSO In objFolder.Files


ws.Cells(count, 4).Value = objFSO.Name
ws.Cells(count, 5).Value = objFSO.Path

count = count + 1
Next

ws.Protect

ActiveWorkbook.Protect
MsgBox "Proses completed successfully thank for Waiting"
Sheet1.Select
Sheet1.Protect
Call FunctionalityOn
End Sub
Sub FunctionalityOff()
Dim i As Long
Dim Status As String
Dim Percent As Double
Percent = Percent * 100
Status = "!!! Please Be Patient...Updating Records !!!" & Percent & "% "
For i = 0 To Percent
    Status = Status & "|"
Next
    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = True
        .StatusBar = Status
        .EnableEvents = False
        .Calculation = xlManual
    End With
End Sub
Sub FunctionalityOn()
    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .StatusBar = False
        .EnableEvents = True
        .Calculation = xlAutomatic
    End With
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Saeed ali khan
Saeed ali khan

ASKER

Thanks sir , it is best solution for me . once again thanks for all Experts
Dear Sir how  can populate this Progress bar with the fallowing Code
Private Sub UserForm_Activate()
Dim objFSO As Object
Dim objFolder As Object
Dim count As Long
Dim Path As String
Dim ws As Worksheet
Application.ScreenUpdating = False
For i = 1 To 500
Progressbar.Label2.Width = Progressbar.Label2.Width + 1
If i Mod 5 = 0 Then
r = r + 1
Progressbar.Caption = r & "%"
Progressbar.Label2.Caption = r & "%"
End If
For J = 1 To 5000 Step 10
   
DoEvents

Next J
Next i


ActiveWorkbook.Unprotect
Set ws = ThisWorkbook.Sheets("DATA")
ws.Visible = True

ws.Select
ws.Unprotect
Range("D4:E2000").Select
Selection.Clear

Path = Sheet1.Range("H3")
If Sheet1.Range("H3") = "" Then
MsgBox "No Room or Path ware Founded"
On Error GoTo 0
Exit Sub
End If
Application.ScreenUpdating = False

Application.ScreenUpdating = False
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Path)

count = 4
For Each objFSO In objFolder.Files


ws.Cells(count, 4).Value = objFSO.Name
ws.Cells(count, 5).Value = objFSO.Path

count = count + 1
Next

ws.Protect
ActiveWindow.SelectedSheets.Visible = False
ActiveWorkbook.Protect
MsgBox "Proses completed successfully thank for Waiting"
Sheet1.Select
Sheet1.Protect
Unload Progressbar
End Sub



i have two sheet (sheet1=Main Search) sheet2=Data ---- UserForme =progressbar
Thanks
Is this a further question?

The attached file is password protected.
Ok sir password is 0000
unprotected file attached
Search-ENG.xlsm
I reallyy do not know what you want. Do you want me to add my code to this workbook?