+ Reply to Thread
Results 1 to 8 of 8

running macro deletes non designated cells

  1. #1
    Registered User
    Join Date
    08-20-2007
    Posts
    4

    Smile running macro deletes non designated cells

    Hello, maybe someone can help me. I've recorded a macro that goes through 5 sheets and deletes blocks of cells, but when i run it, sometimes it deletes cells that I did not delete while recording the macro... I've tried recording the macro many times, deleting all macros and starting over.

    I don't know how to program the macro, just how to record one.

    Also, does anyone know how to protect cells from being altered? there are cells that calculate totals and never need to have data entered by users. I'd like these cells to contain formulas and display the calculations, but if by accident a user clicks on one of these cells, they cannot change the contents.

    Anyone have any tips???

    thanks,

    chris

  2. #2
    Registered User
    Join Date
    08-20-2007
    Posts
    24

    Reply

    Chris,

    Could you post the VBA code? This will help to determine what it is that may be affecting your macro.

    From Excel, choose Tools --> Macro --> Macros click your macro once, then hit edit and copy/paste the code, using the board formats (
    Please Login or Register  to view this content.
    ).

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi Chris,

    Protecting cell contents is quite easy. Select the cells you want to protect then use Format > Cells > Protection > Locked.

    This sets the "Locked" property of the cells, but this doesn't take effect until the worksheet is "Protected". Do this by using Tools > Protection > Protect Sheet. You'll be given an option to insert a password (which is optional) so just click on "OK" if you don't want to use a password.

    If you need to edit the cells after that use Tools > Protection > Unprotect Sheet, edit the cells & then protect the worksheet again.

    Note: By default cells are normally set as "Locked", so you'll probably need to select all the cells on the worksheet, unset their "Locked" property & then lock only the cells you want to protect.

    Hope this helps - please let me know how you get on.

    Best regards,

    Greg

  4. #4
    Registered User
    Join Date
    08-20-2007
    Posts
    4
    Protecting the sheets did the trick, thank you!

    Here's the code for the macro. the b3:g17 are the blocks to be deleted, I click on the b18 to move the cursor out of the way on each sheet after the macro runs, the first section is delete cells scattered around the first sheet.
    for some reason, some cells in I3:I17 on some worksheets get deleted after i run the macro, these cells are now protected, so the error comes up saying that the macro is trying to access some protected cells.
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 08/20/2007 by Citibank
    '
    ' Keyboard Shortcut: Ctrl+d
    '
    Range("I5:J8").Select
    Selection.ClearContents
    Range("I12:J12").Select
    Selection.ClearContents
    Range("I13:I15").Select
    Selection.ClearContents
    Range("J18").Select
    Selection.ClearContents
    Range("G19").Select
    Sheets("Week 1").Select
    Range("B3:G17").Select
    Selection.ClearContents
    Range("B18").Select
    Sheets("Week 2").Select
    Range("B3:G17").Select
    Selection.ClearContents
    Range("B18").Select
    Sheets("Week 3").Select
    Range("B3:G17").Select
    Selection.ClearContents
    Range("B18").Select
    Sheets("Week 4").Select
    Range("B3:G17").Select
    Selection.ClearContents
    Range("B18").Select
    Sheets("Week 5").Select
    Range("B3:G17").Select
    Selection.ClearContents
    Range("B18").Select
    Sheets("Monthly Totals").Select
    Range("G19").Select
    End Sub

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi Chris,

    The first thing I notice is that there's no worksheet specified for the first series of "ClearContents" operations - this means that the macro will begin clearing in whatever worksheet is selected at the time you run the macro. This sort of thing often happens when you use a macro which has been recorded, because you don't think of selecting the first sheet DURING the macro recoding operation.

    So, the very first command in your VBA routine should be like this:
    Please Login or Register  to view this content.
    BUT make sure you replace "Monthly Totals" with the actual name of the first worksheet on which you wish to operate.

    Try this & please let me know how you get on.

    Best regards,

    Greg M

  6. #6
    Valued Forum Contributor
    Join Date
    04-11-2006
    Posts
    407
    This code should work a little faster as well. Just change the blue text to whatever worksheet name you want the macro to start in (i.e. - "Monthly Totals") as Greg mentioned.
    Please Login or Register  to view this content.
    Also, remember when you're posting on the forums to wrap CODE tags around any code that you post or the mods may close your thread.

  7. #7
    Registered User
    Join Date
    08-20-2007
    Posts
    4
    hey everyone, everything is working well, nothing's being deleted that shouldn't be.

    I have another question now though. can I add a button that activates the macro instead of the user having to hit ctrl-d?

  8. #8
    Valued Forum Contributor
    Join Date
    04-11-2006
    Posts
    407
    If you create a button using the "Forms" toolbar, it should prompt you as to which macro to assign to the button.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1