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.Fi leSystemOb ject")
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
Dim objFSO As Object
Dim objFolder As Object
Dim count As Long
Dim Path As String
Dim ws As Worksheet
Application.ScreenUpdating
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.Fi
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
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:
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks sir , it is best solution for me . once again thanks for all Experts
ASKER
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.Fi leSystemOb ject")
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.SelectedSheet s.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
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
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
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
Application.ScreenUpdating
Set objFSO = CreateObject("Scripting.Fi
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.SelectedSheet
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
ASKER
Is this a further question?
The attached file is password protected.
The attached file is password protected.
ASKER
Ok sir password is 0000
ASKER
unprotected file attached
Search-ENG.xlsm
Search-ENG.xlsm
I reallyy do not know what you want. Do you want me to add my code to this workbook?
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