Code to ask confirmation before Delete pictures-CodePudding

Goodnight,

I have a code that allows me to delete all the images that are in the column "A1:A40". I would like to add another code so that confirmation is asked before the photographs are deleted. The idea was to prevent a mistake on the button from immediately deleting all the photographs in this column. It is this situation that I have at the moment and that I would like to improve in the file. I left the 2 codes that work but I don't know how to combine one with the other.

I already tried to insert the second code in the first one but I must be putting it in the wrong place of the code because when the text box appears, clicking on the "Yes" or "No" button, the code always deletes all the photos. Can anyone help me?

The firs code is:

Sub DeletePic()
    Dim xPicRg As Range
    Dim xPic As Picture
    Dim xRg As Range
    Application.ScreenUpdating = False
    Set xRg = Range("A1:A40")
    For Each xPic In ActiveSheet.Pictures
        Set xPicRg = Range(xPic.TopLeftCell.Address & ":" & xPic.BottomRightCell.Address)
        If Not Intersect(xRg, xPicRg) Is Nothing Then xPic.Delete      
        
    Next
    Application.ScreenUpdating = True


    Range("A2:A36").Select
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Selection.ClearContents
End Sub

This final part of the code (ActiveWindows.ScrollRow = A2:A36) is a litle bigger because i made it recording a macro for this part of the code.

The second code is that i want to insert in the first is:

Dim Msg, Style, Title, Response, MyString
Msg = "Deseja continuar ?" 
Style = vbYesNo 
Title = "Esta operação apagará todas as fotografias"  
        
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then   
    MyString = "Yes"  
Else    
    MyString = "No" 
End If

CodePudding user response：

If you want to ask the user to confirm that they want to run DeletePic you need to add your second block of code at the top of DeletePic (anywhere before the For Each loop)

Your second block of code currently doesn't really do anything with the result. You can simplify it to something like the following:

Dim Result As VbMsgBoxResult
' ask if they want to continue
Result = MsgBox("Delete Pictures?", vbYesNo, "Confirm")
' if they do not, then exit out otherwise continue
If Result = vbNo Then Exit Sub