Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    vba Code For 'Are You Sure...' (XL2003)

    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:
    <font color=blue> <font face="Georgia">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.</font face=georgia> </font color=blue>



    <pre>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
    </pre>

    - Ricky

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba Code For 'Are You Sure...' (XL2003)

    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

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank You Very Much

    Thanks.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: vba Code For 'Are You Sure...' (XL2003)

    Incidentally, that code can be shortened to this:
    <pre> 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
    </pre>

    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vba Code For 'Are You Sure...' (XL2003)

    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: <font color=blue>Range("C10:E10").ClearContents</font color=blue> ,
    Could I precede that with: <font color=blue>Sheets("name").Select</font color=blue> 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,
    - Ricky

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba Code For 'Are You Sure...' (XL2003)

    You can use:

    <code>
    Sheets("name").Range("C10:E10").ClearContents
    </code>

    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.
    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vba Code For 'Are You Sure...' (XL2003)

    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.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vba Code For 'Are You Sure...' (XL2003)

    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

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: vba Code For 'Are You Sure...' (XL2003)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank You.

    That's Incredible - And you guessed correctly. <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    - Ricky

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Thank You.

    Glad to help! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •