Thanks a lot. It helped and solved my issue. is it possible to make a progress bar while refreshing/updating the records?
Regards,
Hello mfaisal.ce,
Thank you for your feedback. Showing a progress bar while refreshing/updating the records is possible.
Firstly, open the
Visual Basic Editor >> select
Insert tab >> select
UserForm.
Then, change the
Caption,
Height, and
Width of the
UserForm according to your preference from the
Properties window. The
Properties window is located on the left side of the screen.
After that, select
Label from the
Toolbox >> click and drag your mouse cursor where you want to place the label. Then, change the
Caption,
Height, and
Width of
Label1 according to your preference from the
Properties window.
Afterward, insert a
Frame in the same way. Keep the
Caption blank for
Frame1. And, change the
Height, and
Width according to your preference from the
Properties window.
Next, add another
Label and place it on the frame. Change the
BackColor of the label. Here, I kept it
Highlight. And, left the
Caption blank for
Label2.
At this point, the
UserForm will look like the following image.
Right-click on the
UserForm >> select
View Code.
Then, write the following code in
UserForm1.
Code:
Private Sub UserForm_Activate()
Call Update_Record
End Sub
Here,
Update_Record is the sub procedure you used for updating the records. Now, write the following code instead of the code you used before.
Code:
Sub Update_Record()
'Procedure to Consolidate all sheets in a workbook
On Error GoTo IfError
'1. Variables declaration
Dim Sht As Worksheet, DstSht As Worksheet
Dim DstRow As Long, v As Integer
Dim prog_compl As Single
'2. Disable Screen Updating - stop screen flickering
' And Disable Events to avoid inturupted dialogs / popups
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set DstSht = Worksheets("Summary")
DstRow = 3
i = 1
'5. Loop through each WorkSheet in the workbook and copy the data to the 'Consolidate_Data' WorkSheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> DstSht.Name Then
'5.1: Find the last row on the 'Consolidate_Data' sheet
DstRow = DstRow + 1
If Sht.Range("B5").Value = DstSht.Range("B" & DstRow).Value Then
GoTo txt
End If
Sht.Range("B5").Copy
DstSht.Range("B" & DstRow).PasteSpecial Paste:=xlPasteValues
Sht.Range("D3").Copy
DstSht.Range("C" & DstRow).PasteSpecial Paste:=xlPasteValues
Sht.Range("G3").Copy
DstSht.Range("D" & DstRow).PasteSpecial Paste:=xlPasteValues
Sht.Range("F5").Copy
DstSht.Range("E" & DstRow).PasteSpecial Paste:=xlPasteValues
Sht.Range("F6").Copy
DstSht.Range("F" & DstRow).PasteSpecial Paste:=xlPasteValues
Sht.Range("F7").Copy
DstSht.Range("G" & DstRow).PasteSpecial Paste:=xlPasteValues
Sht.Range("F8").Copy
DstSht.Range("H" & DstRow).PasteSpecial Paste:=xlPasteValues
ActiveSheet.Hyperlinks.Add Anchor:=DstSht.Range("C" & DstRow), Address:="", SubAddress:="'" & Sht.Name & "'" & "!A1"
End If
txt:
prog_compl = (i / ThisWorkbook.Sheets.Count) * 100
progress_bar prog_compl
i = i + 1
Next Sht
IfError:
'6. Enable Screen Updating and Events
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Unload UserForm1
End Sub
Sub progress_bar(prog_compl As Single)
UserForm1.Label1.Caption = prog_compl & "% Completed"
UserForm1.Label2.Width = prog_compl * 2
DoEvents
End Sub
Here, I have modified the code a little bit. I have added the marked portions you can see in the following image.
Now, open another module >> write the following code.
Code:
Sub show_userform()
UserForm1.Show
End Sub
After that, assign the macro named
show_userform to your
CommandButton.
Finally, you will be able to see the progress bar whenever you click on the
Update button. You can close the progress bar manually by pressing the cross button when it says
“100% Completed”.
If you want to close the progress bar automatically once the whole record is updated then you can add the following line in the code.
I hope this will solve your problem. I have also attached the excel file for your convenience. Please let us know if you face any other problems.
Regards
Mashhura jahan
