Progress bar variable assigning

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
Code:
       Call IntProgressBar
        With Sheet2.Range("D7:D" & lr)
            Set rngFind = .Find(what:=mySearch, After:=Sheet2.Range("D" & lr), _
            LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
            If Not rngFind Is Nothing Then
                strFirstFind = rngFind.Address
                Do
                   ListBox1.lstView.AddItem Trim(rngFind.Offset(, -2).Text)
                        For i = 1 To 137
                            ListBox1.lstView.List(ListBox1.lstView.ListCount - 1, i) = _
                            Trim(rngFind.Offset(, i - 2).Text)
        
        ‘ This is where I placed the progress code before, and I ran for a long time
                        Next i
                    Set rngFind = .FindNext(rngFind)
                    
                    Dim currentProgress#, progressPercentage#, BarWidth&
    
                    currentProgress = i / 137
                    BarWidth = Progress.Border.Width * currentProgress
                    progressPercentage = Round(currentProgress * 100, 0)
        
                    Progress.Bar.Width = BarWidth
                    Progress.Text.Caption = progressPercentage & "% complete"
        
                    DoEvents
                Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
            End If
        End With
        Unload Progress

Hello,
I am here again – the challenge gets tougher and interesting. I am trying my skills on the progress bar and I got stacked with this one. I want to show the progress while each row of data is loaded or filtered. I don’t know what to write at where. Can someone pull me out?

Thanks in advance
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
Bump

Still need help with this. Can someone help?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
The best approach (imo) in such a case is to write code in seperate subs for each individual task. E.g. one sub for doing the search / loading / filtering (in a regular module) and one (or more) subs or functions for initializing and updating your progress bar (obviously in the module of a Userform). Those individual tasks can inform each other with the use of global variables, declared as Public in a regular module.

Splitting up your code in as small as possible subs or functions makes it more easy and more clear. In an other thread I've posted this example (post #29). Although it isn't "just a progress bar" (thus not on a modeless Userform), it could give you an impression how to handle things.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
btw for an explanation see post #35 of the same thread
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option - use StatusBar
- replace Application.Wait with valid code doing something

VBA Code:
    Application.ScreenUpdating = False
   
    Application.StatusBar = "job 1"
        'code for job 1
            Application.Wait (Now + TimeValue("00:00:01"))
   
    Application.StatusBar = "job 2"
        'code for job 2
            Application.Wait (Now + TimeValue("00:00:01"))
   
    Application.StatusBar = "job 3"
        'code for job 3
            Application.Wait (Now + TimeValue("00:00:01"))
   
   
    Application.StatusBar = ""

    Application.ScreenUpdating = True

Note
- there are ways to make the StatusBar look like a progress bar
VBA Code:
    With Application
    .ScreenUpdating = False
    
    .StatusBar = "|"
        .Wait (Now + TimeValue("00:00:01"))
    .StatusBar = "|||"
        .Wait (Now + TimeValue("00:00:01"))
    .StatusBar = "|||||"
        .Wait (Now + TimeValue("00:00:01"))
    .StatusBar = "||||||||"
        .Wait (Now + TimeValue("00:00:01"))
    .StatusBar = "|||||||||||"
        .Wait (Now + TimeValue("00:00:01"))
          
    .StatusBar = ""
    .ScreenUpdating = True
    End With
 
Last edited:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
The best approach (imo) in such a case is to write code in seperate subs for each individual task. E.g. one sub for doing the search / loading / filtering (in a regular module) and one (or more) subs or functions for initializing and updating your progress bar (obviously in the module of a Userform). Those individual tasks can inform each other with the use of global variables, declared as Public in a regular module.

Splitting up your code in as small as possible subs or functions makes it more easy and more clear. In an other thread I've posted this example (post #29). Although it isn't "just a progress bar" (thus not on a modeless Userform), it could give you an impression how to handle things.
The best approach (imo) in such a case is to write code in seperate subs for each individual task. E.g. one sub for doing the search / loading / filtering (in a regular module) and one (or more) subs or functions for initializing and updating your progress bar (obviously in the module of a Userform). Those individual tasks can inform each other with the use of global variables, declared as Public in a regular module.

Splitting up your code in as small as possible subs or functions makes it more easy and more clear. In an other thread I've posted this example (post #29). Although it isn't "just a progress bar" (thus not on a modeless Userform), it could give you an impression how to handle things.


If I understand what you are saying correctly, it means what I am asking for is very possible.

But my issue here is that I don't have that level of knowledge to be able use those separate subs and functions as you are suggesting.

I will love it, if you can get that done for me.

Thanks
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@Yongle,

Can you show me, in the code I posted, where to place those lines of yours?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Firstly run this code so that you can see the StatusBar updating
- the String function repeats character | the number of times determined by Round(i / 10, 0)

VBA Code:
Sub TestStatusBar()
    Application.ScreenUpdating = False
    Dim i As Long, j As Long
    For i = 1 To 137
        For j = 1 To 10
            Debug.Print i       '(slows the code to allow statusbar update to be visible)
        Next j
        Application.StatusBar = String(Round(i / 10, 0), "|")
    Next
    Application.StatusBar = ""
End Sub

Choosing where to place it in your code depends on what you are trying to demonstrate and what is slowing your code down
- here is one option for you to test
VBA Code:
    Application.ScreenUpdating = False             'HERE

    With Sheet2.Range("D7:D" & lr)
        Set rngFind = .Find(what:=mySearch, After:=Sheet2.Range("D" & lr), LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            Do
                ListBox1.lstView.AddItem Trim(rngFind.Offset(, -2).Text)
                For i = 1 To 137
                    ListBox1.lstView.List(ListBox1.lstView.ListCount - 1, i) = Trim(rngFind.Offset(, i - 2).Text)
                    Application.StatusBar = String(Round(i / 10, 0), "|")                  'HERE
                Next i
                Set rngFind = .FindNext(rngFind)
                         
                DoEvents
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With

    Application.StatusBar = ""                 'HERE
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
If I understand what you are saying correctly, it means what I am asking for is very possible.
Yes it is!

But my issue here is that I don't have that level of knowledge to be able use those separate subs and functions as you are suggesting.
I'm afraid that there's no way to circumvent the hassle of those seperate subs and functions.

I will love it, if you can get that done for me.
I can imagine, but I can't take over your project. You better take a look at this blog: a step-by-step-guide by Ryan Wells
On his website AJP Excel Information Andy Pope provides a few examples for download.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,885
Office Version
  1. 2016
Platform
  1. Windows
Firstly run this code so that you can see the StatusBar updating


Cool, I am playing around it and it is nice.

Meanwhile, I was able to use a count function to count for the MySearch variable and using that to get the job done.
Code:
Call IntProgressBar
        With Sheet2.Range("D7:D" & lr)
            Set rngFind = .Find(what:=mySearch, After:=Sheet2.Range("D" & lr), _
            LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
            
Dim e&, TotCount&
TotCount = Application. CountIf(Sheet2.Range("D7:D" & lr), mySearch)

If Not rngFind Is Nothing Then
                strFirstFind = rngFind.Address
                Do
                  e = e + 1
                   ListBox1.lstView.AddItem Trim(rngFind.Offset(, -2).Text)
                        For i = 1 To 137
                            ListBox1.lstView.List(ListBox1.lstView.ListCount - 1, i) = _
                            Trim(rngFind.Offset(, i - 2).Text)
                        Next i
                    Set rngFind = .FindNext(rngFind)
                    
                    Dim currentProgress#, progressPercentage#, BarWidth&
    
                    currentProgress = e / TotCount
                    BarWidth = Progress.Border.Width * currentProgress
                    progressPercentage = Round(currentProgress * 100, 0)
        
                    Progress.Bar.Width = BarWidth
                    Progress.Text.Caption = progressPercentage & "% complete"
        
                    DoEvents
                Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
            End If
        End With
        Unload Progress
 

Watch MrExcel Video

Forum statistics

Threads
1,127,462
Messages
5,624,871
Members
416,063
Latest member
chaulon199

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top