vba Code For ‘Are You Sure…’ (XL2003)

Topic

New Reply

I’ve deleted about 90% of the code to reduce the size of the post and because it really has little to do with the question…

Question:
I have this code attached to a button on a worksheet. Primarily, it removes the previous months data from several forms and readies them for a new month. The problem is, the button is unforgiving. Click it once and boom! Everything’s gone. Which is fine if that was the intention. I would like to know what I can add to the front of the code that will stop and say: “Are you sure?”. And then require me to click “yes” to continue or “no” to quit the code.

Sub StartNewMonth()
'
' StartNewMonth Macro
' Macro recorded 10/2/2006 by Ricky
'

'
    Range("C10").Select
    Selection.ClearContents
    Range("D10").Select
    Selection.ClearContents
    Range("E10").Select
    Selection.ClearContents
    ...............................................................
Deleted Some Code For Brevity
...................................................................

    Range("C9").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("D9").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("E9").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("G9").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("H9").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("C13").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("D13").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("E13").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("G13").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("H13").Select
    ActiveCell.FormulaR1C1 = "=+R[2]C"
    Range("C9:H9").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C13:H13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C11:H11").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C15:H15").Select
    Selection.ClearContents
    Range("C10").Select
    Sheets("2 - Manual Entries").Select
    Range("C10").Select
End Sub

Reply | Quote

Replies

Add this code at the beginning:

If MsgBox(“You are about to delete data. Are you sure?”, vbYesNo + vbQuestion) = vbNo Then
Exit Sub
End If

If you want to make “No” the default button, use vbYesNo + vbQuestion + vbDefaultButton2

Reply | Quote

Thanks.

Reply | Quote

Incidentally, that code can be shortened to this:

    Range("C10:E10").ClearContents
...
    With Range("C9:H9")
        .FormulaR1C1 = "=R[2]C"
        .Formula = .Value
    End With
    With Range("C13:H13")
        .FormulaR1C1 = "=R[2]C"
        .Formula = .Value
    End With
    Range("C11:H11").ClearContents
    Range("C15:H15").ClearContents
    Sheets("2 - Manual Entries").Select
    Range("C10").Select

FWIW.

Reply | Quote

Hi Rory,

I’ve attached a txt file with the entire routine. There are several places in the code where it changes sheets, deletes more, changes sheets again, deletes more, etc. Obviously, I used the recorder to get what I wanted.

Before using a line like: Range(“C10:E10”).ClearContents ,
Could I precede that with: Sheets(“name”).Select if I needed to change sheets before deleting?

I’m guessing that if a sheet is not selected before deleting contents, then the code will do its deleting on the active or current page (the page with the button)??

If I’m on the right track, then It looks like I could shorten the code considerably. If not…

Thanks,

Reply | Quote

You can use:


Sheets("name").Range("C10:E10").ClearContents


In most cases, you don’t have to select the sheet or the range and it is always better not to if you don’t have to.

Reply | Quote

Thanks Legare,

I’m betting the two seconds of “flickering” that goes on when I run the current code would probably stop if I did not select the sheets before deleting content. I can see how the example you’ve provided can be used to dramatically shorten my monster code.

Reply | Quote

In addition, the line during the start of the code
Application.Screenupdating = false

will stop any redrawing of the screen duiring code execution and then add at the end:
Application.Screenupdating = true

to turn it back on. This also can save some time

Steve

Reply | Quote

See if the attached does what you wanted – I think I got the ranges right and I guessed sheet “2 – Manual Entries” was your start point.

Reply | Quote

That’s Incredible – And you guessed correctly.

Reply | Quote

Glad to help!

Reply | Quote