Button works for a little while then stops working

[david.paton]

I have a table added to my spreadsheet and I have two buttons. One to add a row to the table and one to delete a row from the table. When I put them in, they both will work but then I go and do some other formatting on the spreadsheet and I come back to the buttons and I click them and only the add button works, the delete button doesn't do anything.

It is really strange and I have no idea what to do as I don't know how to code, the code behind the buttons I found on the net.

This is the code I have:

Private Sub CommandButton3_Click()

Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("table3")

'add a row at the end of the table
tbl.ListRows.Add
End Sub




Private Sub CommandButton4_Click()

Dim oLst As ListObject

Application.ScreenUpdating = False

If ActiveSheet.ListObjects.Count > 1 Then
For Each oLst In ActiveSheet.ListObjects
With oLst
If .Name = "Table3" Then
If oLst.ListRows.Count > 1 Then
number_of_columns = .ListColumns.Count
oLst.ListRows(oLst.ListRows.Count).Delete
End If
End If
End With
Next

End If
End Sub


Can anyone see what is wrong with this code or maybe a better way to code it?

Thanks,
Dave

 

[Galaxiom]

You have not reenabled ScreenUpdating in the delete procedure and it won't do anything if there is only one table.

 

[david.paton]

Please explain. What is it, why do I need it and how did I get rid of it?

 

[Galaxiom]

ScreenUpdating is the repainting of the screen. With it off the screen changes from the original to the finished without going through the steps in between. If there is a lot going on then it can make processing faster too.

Switch it back on at the end or don't switch it off in the first place by leaving out the line.

 

[david.paton]

How do I reenable screen updating?

 

[Galaxiom]

You disable like this:

Application.ScreenUpdating = False

Maybe take a wild guess on how to re-enable it.

 

[david.paton]

Would it be best to take the line of code out or change it to =true?

 

[Gasman]

Would it be best to take the line of code out or change it to =true?

For what you are doing, I'd comment it out.
Put a ' in the beginning of the line.

You can also issue it in the immediate window of the debugger.

 

[david.paton]

Thanks for your help. In what situation would you need it?

 

[Gasman]

Multiple updates to cells and not want to see the screen flickering.
EG I download a file from a system each day and code then brings in new data into my workbook and applies various formulae, a lot like you are doing.

Rather than see the sheets moving like a madman, I switch off the display update and switch back on at the end. I use the Application.StatusBar function to show the progress of the code running, like

Application.StatusBar = "Saving Date and Timestamped file"

 

[david.paton]

Thanks, much appreciated!