clear vba macro

[John Caines]

Hello All.
I'm tring to create a simple (Though not for me)
Clear macro.

All I'm looking to do is just add 1 more line of code/text,,, and I'm not sure how to do it.
So far the VBA I have is as follows;

Sub Clear_All_Data()
If WorksheetFunction.CountA(Range("E24:E274,H24:I274,J23:K274,O24:O274")) = 0 Then
    MsgBox "Sorry, No Data to Clear :-)"
Else
    If MsgBox("Are You Sure You Want To Delete ALL Your Entered Data?", vbYesNo + vbCritical) = vbYes Then
        Range("E24:E274,H24:I274,J23:K274,O24:O274").ClearContents
            MsgBox "All Your Entered Details Have Now Been Cleared",  vbInformation
        End If
    End If
End Sub

The line I would like to add is just after the line that says, " "Are You Sure You Want To Delete ALL Your Entered Data?"
IF they click NO,,, I would like to display another message that just says;
"No problem, nothing will be cleared."

Can anybody show me how this would be inserted please, I've tried a few attempts but just can't seem to get it right. ;-(

I hope the above makes sense.
many Thanks;
John Caines

 

[VoG]

Hi John. Try

Sub Clear_All_Data()
If WorksheetFunction.CountA(Range("E24:E274,H24:I274,J23:K274,O24:O274")) = 0 Then
    MsgBox "Sorry, No Data to Clear :-)"
Else
    If MsgBox("Are You Sure You Want To Delete ALL Your Entered Data?", vbYesNo + vbCritical) = vbYes Then
        Range("E24:E274,H24:I274,J23:K274,O24:O274").ClearContents
        MsgBox "All Your Entered Details Have Now Been Cleared", vbInformation
    Else
        MsgBox "No problem, nothing will be cleared.", vbInformation
    End If
End If
End Sub

 

[repairman615]

<font face=Courier New>    <SPAN style="color:#00007F">If</SPAN> MsgBox("Are You Sure You Want To Delete ALL Your Entered Data?", vbYesNo + vbCritical) = vbYes <SPAN style="color:#00007F">Then</SPAN><br>            Range("E24:E274,H24:I274,J23:K274,O24:O274").ClearContents<br>                MsgBox "All Your Entered Details Have Now Been Cleared", vbInformation<br>    <SPAN style="color:#00007F">Else</SPAN><br>            MsgBox "No problem, nothing will be cleared."<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

 

[John Caines]

Hi VOG!,, & Repairman615,,,,
Great minds think alike!!!

You've replied with both the same answers,, excellent.

Many thanks to you both for this,,, I couldn't get it,,, though now looking at your answers,, they seem very logical.

As a note,, I was trying,, things like;

Sub Clear_All_Data()
If WorksheetFunction.CountA(Range("E24:E274,H24:I274,J23:K274,O24:O274")) = 0 Then
    MsgBox "Sorry, No Data to Clear :-)"
Else
    If MsgBox("Are You Sure You Want To Delete ALL Your Entered Data?", vbYesNo + vbCritical) = vbNo Then
        MsgBox "No problem, nothing will be cleared." = vbYes Then
        Range("E24:E274,H24:I274,J23:K274,O24:O274").ClearContents
            MsgBox "All Your Entered Details Have Now Been Cleared", vbInformation
        End If
    End If
End Sub

Not quite eh,

Just 1 more question if I may that you might have an answer for......
When a pop up does show, (For example,,"No Problem, nothing will be cleared."),, the top left of the pop up box,, (It's FRAME) says,"Microsoft Excel",,,,,,,,,,,,
Is there anyway to alter this so I can make it display say "Answer For No"

Just showing "Microsoft Excel" all the time is so generic,, and not that informative.
Just wondered if it could be altered that was all.
Again,,, many thanks to you both for your replies.

Best regards
john Caines

 

[VoG]

Try like this

Sub Clear_All_Data()
If WorksheetFunction.CountA(Range("E24:E274,H24:I274,J23:K274,O24:O274")) = 0 Then
    MsgBox "Sorry, No Data to Clear :-)", vbInformation, "Nothing to clear"
Else
    If MsgBox("Are You Sure You Want To Delete ALL Your Entered Data?", vbYesNo + vbCritical, "Are you sure") = vbYes Then
        Range("E24:E274,H24:I274,J23:K274,O24:O274").ClearContents
        MsgBox "All Your Entered Details Have Now Been Cleared", vbInformation, "Entered data cleared"
    Else
        MsgBox "No problem, nothing will be cleared.", vbInformation, "Data retained"
    End If
End If
End Sub

 

[John Caines]

Excellent VOG!!!

Exactly what I was after,,,
this I can now implement on other spreadsheets/workbooks I have,,,


Great stuff.

Best regards VOG,,,,,
Mnay Thanks
John Caines