How do I make my command button work by calling procedures that work


Well-known Member
I have tried to call my procedures to copy lines and clear cells that work if I run them individually but if I try and click on my red button, "Copy to relevant sheet and clear lines" on the Costing_tool sheet, one of the procedure names is highlighted and the error message reads: Expected procedure, not variable. Can someone help me fix this please?

I have uploaded my spreadsheet. tool 13.9.xlsm?dl=0



Well-known Member

cmdDeleteAll actually refers to the button, if you want to call the buttons Click event try this.
    Call cmdDeleteAll_Click
Mind you I wouldn't recommend calling an event like that, instead you could put the code for the Click event in a separate sub and that could be called from elsewhere.

For example if you created this sub in Module1,
Sub DeleteAll()
'Deleting The Data In A Table
Dim tbl As ListObject
Dim cell As Range

    Set tbl = ThisWorkbook.Sheets("Costing_tool").ListObjects("tblCosting")
    'Delete all table rows except first row
    With tbl.DataBodyRange
        If .Rows.Count > 1 Then
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        End If
        'Clear the contents, but not delete the formulas
        For Each cell In tbl.ListRows(1).Range.Cells
            If Not cell.HasFormula Then
                cell.Value = ""
            End If
    End With
End Sub
you could then have this for the Click event of the button,
Sub cmdDeleteAll_Click()
    Call DeleteAll
End Sub
and for the 'Copy to relevant sheet and clear lines' button you could have this.
Sub cmdCopyLineBlank_Click()

    Call cmdCopy

    Call DeleteAll

    Worksheets("Costing_tool").Unprotect Password:="costings"
    'Worksheets("Costing_tool").Protect Password:="costings"
End Sub
Last edited:

Some videos you may like

This Week's Hot Topics