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

 

[Chewyhairball]

Thank you both. You have given me lots of ideas to try as well as some things to try and speed the code up.

Bobsan..i managed to get post 4 to work and i quite like it...gives me some ideas now for other things I am doing. Im thinking now it might be better to have it as a percentage so ill have a play with that. I have the basics now.

Gokhan...I will give the userform a try, although i am going to have a break just now as my brain hurts. Im currently doing this on my laptop on my knee so i will wait until i get in front of my 3 monitors ( could never go back to one now ). I will update you with how I get on.

 

[Gokhan Aycan]

Reading skills ftw! As bobsan also reminded, you should have ScreenUpdating=False/True outside the loop. Those take time as well (approx. 10-20 ms each).
Shape.Visible should be outside as well.

Anything that doesn't need to happen each iteration should be outside really.

 

[Chewyhairball]

@bobsan42 It may be due to ScreenUpdating = False

Insert a new userform, and a label on it. Change ShowModal property to False.
View attachment 47064
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.

Hi

I am getting this error

 

[Gokhan Aycan]

You have 2 equal signs in there.

 

[Chewyhairball]

H

You have 2 equal signs in there.

Hi. I already tried that but the error then shifted to the word row?

 

[Gokhan Aycan]

Aww, I posted the code wrong, sorry for that. Wrote it here instead of VBA Editor.

Userform1.Label1.Caption = "Please wait while the rows are being deleted!" & chr(10) & "Row " & i & " of " & Range("A15").Value

 

[Chewyhairball]

Aww, I posted the code wrong, sorry for that. Wrote it here instead of VBA Editor.

Userform1.Label1.Caption = "Please wait while the rows are being deleted!" & chr(10) & "Row " & i & " of " & Range("A15").Value

Cool. Got it working now.

Had to change me.repaint to userform1.repaint but its working well.

Im learning lots of cool stuff today!

Just of to firgure out how to display % now

thank you

 

[Gokhan Aycan]

Had to change me.repaint to userform1.repaint but its working well.

Yes, if you are calling it from outside Userform's module, need to reference with its name.

Display % in numbers? Like:

Userform1.Label1.Caption = "Please wait while the rows are being deleted!" & chr(10) & "Percent completed: " & Format (i / Range("A15").Value *100, "##0.00")

or percent left as:

Userform1.Label1.Caption = "Please wait while the rows are being deleted!" & chr(10) & "Percent left: " & Format (1-i / Range("A15").Value *100, "##0.00")