Progress Indicator for addition/subtraction of table rows

[Chewyhairball]

Hi

I have this code that when run, either adds or removes a specific number of rows from table1 depending on cell value in A14
I am looking for way to show (as the code is running) a countdown(or countup) of how many rows are left to go.
It can be a cell on the sheet or a shape linked to a cell or pretty much anything like that.
It sometimes takes a while if a few rows are added and thought it might be better to show the progress.
The middle part of the code just shows a box with a statement about rows currently being added.
thanks

Rory

 Dim i As Integer
 If Range("A14").Value > 0 Then
 For i = 1 To Range("A15").Value
 
 Application.ScreenUpdating = True
 ActiveSheet.Shapes("Rectangle: Rounded Corners 1").Visible = True
  Application.ScreenUpdating = False
ActiveSheet.ListObjects("Table1").ListRows.Add

Range("A20").Select
 ActiveSheet.Unprotect
    Selection.Insert Shift:=xlDown
    Next i

 

[bobsan42]

You can use the statusbar:

Application.Statusbar = "row " & i & " of " & Range("A15").Value

Although you would do better to assign the total number of rows to a variable.

It seems you are also adding single cells @ A20. And showing a rectangle with each loop. and selecting Range A20 with every loop. And changing App variables with each loop ...
Consider very well what needs to stay in the loop.
You may get some speedup by removing the range SELECT method like this:

Range("A20").Insert Shift:=xlDown

 

[Chewyhairball]

T

You can use the statusbar:

Application.Statusbar = "row " & i & " of " & Range("A15").Value

Although you would do better to assign the total number of rows to a variable.

It seems you are also adding single cells @ A20. And showing a rectangle with each loop. and selecting Range A20 with every loop. And changing App variables with each loop ...
Consider very well what needs to stay in the loop.
You may get some speedup by removing the range SELECT method like this:

Range("A20").Insert Shift:=xlDown

Thanks. Is there a way to get what is displayed in the status bar to be displayed in a shape or textbox. Something a bit more prominent than the bottom of the screen.

The shape i currently have basically displays some text to say rows are being added and to please wait...I didnt want people to be clicking like mad thinking nothing was happening

 

[bobsan42]

Try this:

ActiveSheet.Shapes("Rectangle: Rounded Corners 1").TextFrame2.TextRange.Characters.Text ="row " & i & " of " & Range("A15").Value

 

[Gokhan Aycan]

You are turning off screen updating, so I am not sure if it's possible.
However, you can just change the text and color right before and revert back afterwards perhaps.

Edit: Another note, it is a terrible idea to slow down the code to update progress on each iteration. Maybe tie it to a Modula function.

Edit 2: With all the computation going, you may have to use DoEvents to let the update happen on the screen. On a userform you would use Me.Repaint.

Edit 3: Maybe you can just open a modeless userform with only a label that gives the information.

If i Mod 10 = 0 then update the progress

 

[Chewyhairball]

T

You can use the statusbar:

Application.Statusbar = "row " & i & " of " & Range("A15").Value

Although you would do better to assign the total number of rows to a variable.

It seems you are also adding single cells @ A20. And showing a rectangle with each loop. and selecting Range A20 with every loop. And changing App variables with each loop ...
Consider very well what needs to stay in the loop.
You may get some speedup by removing the range SELECT method like this:

Range("A20").Insert Shift:=xlDown

Thanks. Is there a way to get what is displayed in the status bar to be displayed in a shape or textbox. Something a bit more prominent than the bottom of the screen.

The shape i currently have basically displays some text to say rows are being added and to please wait...I didnt want people to be clicking like mad thinking nothing was happening

You are turning off screen updating, so I am not sure if it's possible.
However, you can just change the text and color right before and revert back afterwards perhaps.

Edit: Another note, it is a terrible idea to slow down the code to update progress on each iteration. Maybe tie it to a Modula function.

Edit 2: With all the computation going, you may have to use DoEvents to let the update happen on the screen. On a userform you would use Me.Repaint.

Edit 3: Maybe you can just open a modeless userform with only a label that gives the information.

If i Mod 10 = 0 then update the progress

thanks for the info, however i have no idea how to do any of that. I have used userforms in the past but im not an expert with vba.

 

[bobsan42]

T


Thanks. Is there a way to get what is displayed in the status bar to be displayed in a shape or textbox. Something a bit more prominent than the bottom of the screen.

The shape i currently have basically displays some text to say rows are being added and to please wait...I didnt want people to be clicking like mad thinking nothing was happening

thanks for the info, however i have no idea how to do any of that. I have used userforms in the past but im not an expert with vba.

Did you try the line from post #4 ?

 

[Chewyhairball]

Did you try the line from post #4 ?

hi

i have, and im still trying but nothing seems to happen. I am working through the rest of my code to see if something else is effecting it.

 

[Gokhan Aycan]

@bobsan42 It may be due to ScreenUpdating = False

Insert a new userform, and a label on it. Change ShowModal property to False.

Put this in Userform module so people can't close it with X:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Cancel = True

End Sub

Instead of changing the shape text:

Userform1.Show
Userform1.Label1.Caption = ="Please wait while the rows are being deleted! & chr(10) & "Row " & i & " of " & Range("A15").Value
Me.Repaint      ' might be needed, try without first
Userform1.Hide

Adjust the userform design to your liking. Also, best is to give userform and label more meaningful names.

 

[Gokhan Aycan]

If you want to go the userform route, also change the "StartUpPosition" property to Manual (just 2 lines below in the image I posted). And in userform module:

Private Sub UserForm_Activate()

    Me.Top = Application.Top + (Application.Height - Me.Height) / 2
    Me.Left = Application.Left + (Application.Width - Me.Width) / 2

End Sub

This will place the userform right in the center of Excel window. Userforms tend to show up in irrelevant places otherwise especially with multiple displays.