Having trouble using a nested IF with VBA msgbox

Hello,

I am looking to have my VBA code trigger a message box upon opening the file which asks the user if they'd like to clear certain cells. I want the "Yes" on the first question to trigger another message box asking them if they are sure they want to go through with it. I need the "Yes" on the second message box to clear the cells. Thank you for the help, here is my code:

Private Sub Workbook_Open()

Dim answer As Integer
Dim answer1 As Integer

answer = MsgBox("Please note that this file is a template for company use. No manual updates to this file should be saved on the public drive. Please select Yes to clear all manual pricing overrides (blue columns) to avoid formula errors. If you have saved a copy of this file to your own records, please disregard this message and select No to keep the information you have entered.", vbYesNo + vbQuestion, "Please Read")

If answer = vbYes Then

answer1 = MsgBox("Are you sure you want to clear the contents of the manual override columns?", vbYesNo + vbQuestion, "Please Read")

If answer1 = vbYes Then
    
    Range("AC7:AC5000").Select
    Selection.ClearContents
    
End If

Else

End If

End Sub

Your code works just fine

I shortened the event trigger just to make it simpler, but turns out that was the issue. Here is my actual code with the actual ranges that need to be cleared. This is giving me the error of "Application-defined or object-defined error".

Private Sub Workbook_Open()

Dim answer As Integer
Dim answer1 As Integer

answer = MsgBox("Please note that this file is a template for company use. No manual updates to this file should be saved on the public drive. Please select Yes to clear all manual pricing overrides (blue columns) to avoid formula errors. If you have saved a copy of this file to your own records, please disregard this message and select No to keep the information you have entered.", vbYesNo + vbQuestion, "Please Read")

If answer = vbYes Then

answer1 = MsgBox("Are you sure you want to clear the contents of the manual override columns?", vbYesNo + vbQuestion, "Please Read")

If answer1 = vbYes Then
    
    ThisWorkbook.Sheets("BAPX CALCULATIONS").Range("AC7:AC5000,AF7:AF5000,AI7:AI5000,AL7:AL5000,AO7:AO5000,AR7:AR5000,AU7:AU5000,AX7:AX5000,BA7:BA5000,BD7:BD5000,BG7:BG5000,BJ7:BJ5000,BM7:BM5000,BP7:BP5000,BS7:BS5000,BV7:BV5000,BY7:BY5000,CB7:CB5000").Select
    Selection.ClearContents
    
    ThisWorkbook.Sheets("BAPX CALCULATIONS").Range("CB7:CB5000,CD7:CD5000,CF7:CF5000,CI7:CI5000,CL7:CL5000,CN7:CN5000,CP7:CP5000,CS7:CS5000,CU7:CU5000,CW7:CW5000,CZ7:CZ5000,DB7:DB5000,DD7:DD5000,DG7:DG5000,DI7:DI5000,DK7:DK5000,DN7:DN5000").Select
    Selection.ClearContents

    ThisWorkbook.Sheets("BAPX CALCULATIONS").Range("DP7:DP5000,DR7:DR5000,DU7:DU5000,DX7:DX5000,EA7:EA5000,ED7:ED50000").Select
    Selection.ClearContents
    
End If

Else

End If

End Sub

If you want to specify each range like that you are going to need to use the Union property. Very tedious but it will work

If answer1 = vbYes Then
    Dim ws As Worksheet:    Set ws = ThisWorkbook.Sheets("BAPX CALCULATIONS")
    Dim rng As Range
    With ws
        Set rng = Union(.Range("AC7:AC5000"), .Range("AF7:AF5000"), .Range("AI7, AI5000")) 'all your ranges go here
        rng.ClearContents
    End With
End If

Another option would be to incorporate a looping structure if there is consistency amongst your columns. I have given you an example, up to you to make sure it works for your data set

If answer1 = vbYes Then
    For i = 29 To 80 Step 3 'Column AC to CB every 3 columns
        ThisWorkbook.Sheets("BAPX CALCULATIONS").Cells(7, i).Resize(4993, 1).ClearContents
    Next i
    
    For i = 80 To 84 Step 2 'Column CB to CF every 2 columns
        ThisWorkbook.Sheets("BAPX CALCULATIONS").Cells(7, i).Resize(4993, 1).ClearContents
    Next i
    
    For i = 87 To 134 Step 3 'Column CI to ED every 3 columns
        ThisWorkbook.Sheets("BAPX CALCULATIONS").Cells(7, i).Resize(4993, 1).ClearContents
    Next i
End If

Thank you very much. I used the first one since there isn't much consistency in the worksheet. Can you only loop if there aren't consistent columns or is there a way around that? Thanks again.

You can loop through each column but there needs to be a mechanism in place to tell the code which columns to apply the code to. This can be done many ways, but at this point it might be best just to work with the first option I showed you. In retrospect, we could have created an array of each column that you need the code applied then looped through the array. Next time.