Problem with IF coding

[neildex]

I am having a problem when a macro runs. IF I don't want to delete order data, it should do nothing and return to the front sheet. IF I do want to delete data, it runs the correct process. At the end of my coding under ELSE, it will show a dialog box advising a user to re-enter a year, but it shows this if I choose the option not to delete data. Where have I gone wrong? I babe even tried placing END IF before ELSE, but this does not work and presents an error message.

Here is my code:

Dim Answer As String
Dim MyNote As String
Dim vbNo As String


'Place your text here
MyNote = "that you want to DELETE ALL orders logged for the year?"


'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Please confirm...")


If Answer = vbNo Then
'Code for No button Press
Sheets("Front").Select


Else
'Code for Yes button Press


Sheets("Jan").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Feb").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Mar").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Apr").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("May").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Jun").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Jul").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Aug").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Sep").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Oct").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Nov").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Dec").Select
Range("B11:H38").ClearContents
Range("J11:M38").ClearContents
Range("O11:Q28").ClearContents
Sheets("Front").Select
Range("L22:L33").ClearContents
Sheets("Front").Select
'Reset Budget
Range("Q22").Select
ActiveCell.Value = 0
Range("Q23").Select
ActiveCell.Value = 0
Range("Q24").Select
ActiveCell.Value = 0
Range("Q25").Select
ActiveCell.Value = 0
Range("Q26").Select
ActiveCell.Value = 0
Range("Q27").Select
ActiveCell.Value = 0
Range("Q28").Select
ActiveCell.Value = 0
Range("Q29").Select
ActiveCell.Value = 0
Range("Q30").Select
ActiveCell.Value = 0
Range("Q31").Select
ActiveCell.Value = 0
Range("Q32").Select
ActiveCell.Value = 0
Range("Q33").Select
ActiveCell.Value = 0


'Reset year
Range("B8:D8").Select
ActiveCell.FormulaR1C1 = ""
Range("B8:D8").Select
MsgBox "Please enter year again, in the top left of the worksheet."
End If




End Sub

 

[mole999]

I have to wonder why you want the macro to run to then decide you don't want the action. How about you create only the clear sheet macro, and give yourself a second chance before you commit to the action.

Not elegant, but your clear routines could be


with Sheets("Jan")
.Range("B11:H38,J11:M38,O11:Q28").ClearContents
end with

 

[neildex]

It is not just me that uses this spreadsheet, others use it on their systems and I just wanted confirmation as to whether a user really did want to delete data, just in case they accidentally clicked on the button to action this.

Would the With coding bring up a dialog box confirming a user wanted to really delete data?

 

[mole999]

ah I hadn't realised you were only going to call this from a button

 

[neildex]

ah I hadn't realised you were only going to call this from a button

Sorry, I should have made this clear in my original post.