Tweak Delete Row VBA code to ask if the user is sure.

[bsnow]

Hi guys

I need to tweak the code I have to delete a row in a table to display a dialogue box with a message asking if the user is sure they want to delete the row.

'VBA Delete the First Row from the Table
Sub Delete_First_Row_from_Table()

'Declare Variables
Dim oSheetName As Worksheet
Dim sTableName As String
Dim loTable As ListObject

'Define Variable
sTableName = "Table1"

'Define WorkSheet object
Set oSheetName = Sheets("Main")

'Define Table Object
Set loTable = oSheetName.ListObjects(sTableName)

If loTable.ListRows.Count > 1 Then
'Delete a Row from the table
loTable.ListRows(1).Delete
End If
End Sub


I'd like it so when the button is pressed, a message appears "Are you sure you want to delete the last batch? and offer a yes or no option. If 'yes' is selected, the row will be deleted, if 'no' is selected, the row won't delete. It will hopefully reduce the number of users clicking delete by mistake!

Thanks guys!

 

[dmt32]

Hi,
try

'VBA Delete the First Row from the Table
Sub Delete_First_Row_from_Table()
    
    'Declare Variables
    Dim oSheetName  As Worksheet
    Dim sTableName  As String
    Dim loTable     As ListObject
    Dim Response    As VbMsgBoxResult
    
    'Define Variable
    sTableName = "Table1"
    
    'Define WorkSheet object
    Set oSheetName = Worksheets("Main")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    If loTable.ListRows.Count > 1 Then
        Response = MsgBox("Are you sure you want To delete the last batch?", 292, "Delete Row")
        If Response = vbNo Then Exit Sub
        'Delete a Row from the table
        loTable.ListRows(1).Delete
    End If
End Sub

Dave

 

[bsnow]

Works beautifully - thank you! ?

 

[dmt32]

Works beautifully - thank you! ?

welcome & appreciate feedback

Dave