Can't make VBA act on MsgBox results

[alisser]

Help again: I'm trying to allow a user to decide not to delete a row in a protected table (part of a macro) if they click Cancel in a msgbox that pops up. I was able get the msgbox to pop up when the RemoveRows sub is run, but can't make it exit the RemoveRows sub if the user clicks Cancel.
Here is a private sub with msg, and the sub to delete:

Private Sub cmdMessageBoxDelete_Click()
MsgBox "Are you sure you want to delete the bottom row?" & _
vbCrLf & "You can't undo after clicking OK. ", _
VbMsgBoxStyle.vbOKCancel
End Sub

Sub RemoveRows()
Dim oLst As ListObject
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="myPW"
If ActiveSheet.ListObjects.Count > 0 Then
For Each oLst In ActiveSheet.ListObjects
If oLst.ListRows.Count > 1 Then
' Give user a way out of deleting row
Call cmdMessageBoxDelete_Click ' From private sub in this module
oLst.ListRows(oLst.ListRows.Count).Delete
End If
Next oLst
End If
ActiveSheet.Protect AllowSorting:=True, Password:="myPW"
End Sub

Help appreciated.
Thanks
-Alisser

 

[lancerj017]

Try:

Private Sub cmdMessageBoxDelete_Click()
if MsgBox ("Are you sure you want to delete the bottom row?" & vbCrLf & "You can't undo after clicking OK. ", VbMsgBoxStyle.vbOKCancel) = vbcancel then exit sub
End Sub
 
Sub RemoveRows()
Dim oLst As ListObject
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="myPW"
If ActiveSheet.ListObjects.Count > 0 Then
    For Each oLst In ActiveSheet.ListObjects
        If oLst.ListRows.Count > 1 Then
        ' Give user a way out of deleting row        
        Call cmdMessageBoxDelete_Click ' From private sub in this module
oLst.ListRows(oLst.ListRows.Count).Delete
        End If
    Next oLst
End If
ActiveSheet.Protect AllowSorting:=True, Password:="myPW"
End Sub

 

[GTO]

Greetings Alisser,

Are you wanting to exit the sub outright, or just skip the delete in that particular cycle of the loop?

If wanting to bail outright, maybe skip calling another procedure and toss a line in there. Not tested but something akin to:

Sub RemoveRows()
Dim oLst As ListObject
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="myPW"
    If ActiveSheet.ListObjects.Count > 0 Then
        For Each oLst In ActiveSheet.ListObjects
            If oLst.ListRows.Count > 1 Then
                If Not MsgBox("Are you sure?", vbYesNo Or vbDefaultButton2, "Read Me!") = vbYes Then
                    GoTo JumpOut
                End If
            oLst.ListRows(oLst.ListRows.Count).Delete
            End If
        Next oLst
    End If
        
JumpOut: ActiveSheet.Protect AllowSorting:=True, Password:="myPW"
End Sub

Mark

 

[Marcelo Branco]

Try a Function

Something like

Private Function cmdMessageBoxDelete_Click()
    cmdMessageBoxDelete_Click = MsgBox("Are you sure you want to delete the bottom row?" & _
    vbCrLf & "You can't undo after clicking OK. ", VbMsgBoxStyle.vbOKCancel)
End Function

Sub aTest()
    Dim answer As Long
    
    answer = cmdMessageBoxDelete_Click
    If answer <> 1 Then Exit Sub
    MsgBox "Do Stuff"
End Sub

M.

 

[alisser]

Thanks - I tries that code, but the sub is still not reacting to the msgbox. When I click OK, it deletes a row correctly, but if I click cancel, it still deletes a row. ??

 

[GTO]

Hi There,

Which code did you try?

 

[alisser]

Whoops, I hadn't read all the replies - will try again. This is very helpful!

 

[alisser]

That did it. thx!

 

[GTO]

Well, I am glad you got it working. Cat-killin' curiousity... What did it?

 

[alisser]

Oh, it was putting is all in one as you suggested - copied your code exactly. That is all I really needed and the shorter message was better anyway.
Thanks GTO!