+ Reply to Thread
Results 1 to 16 of 16

Prevent overwriting cells with formulas

  1. #1
    Registered User
    Join Date
    10-17-2013
    Location
    Ardooie
    MS-Off Ver
    Excel 2007
    Posts
    67

    Unhappy Prevent overwriting cells with formulas

    Hello all,

    I want users to stop overwriting (with regular text or numbers) formulas in my sheet.

    To keep it easy:
    Range A1:I10 have formulas.

    I've allready found a code to prevent them deleting the formula:

    Please Login or Register  to view this content.
    Problem is that overwriting the cell is still possible, so the sheet still gets f*cked up (pardon my language).

    Any ideas please?

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,589

    Re: Prevent overwriting cells with formulas

    The textbook method here is to lock cells with formulas, unlock cells where user may enter data, and protect the sheet.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-17-2013
    Location
    Ardooie
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Prevent overwriting cells with formulas

    Quote Originally Posted by 6StringJazzer View Post
    The textbook method here is to lock cells with formulas, unlock cells where user may enter data, and protect the sheet.
    I was hoping for VBA code and not sheet protection + password

  4. #4
    Registered User
    Join Date
    10-17-2013
    Location
    Ardooie
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Prevent overwriting cells with formulas

    Or maybe preventing users to select cells (or edit collumns) ...
    Last edited by louvaek; 11-28-2018 at 11:00 PM.

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Prevent overwriting cells with formulas

    Hello louvaek,

    Please this this alternative method which does not require Sheet Protection. Please bear in mind that VBA is only to help honest users, and you may find some Excel savvy users who knows how to proceed without activating VBA and still mess up your formulas!

    In a Standard Module:

    Please Login or Register  to view this content.
    And in the Worksheet Event Code:

    Please Login or Register  to view this content.
    Try the attached sample Workbook. - As soon as I manage to get it to upload. Securi problems

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  6. #6
    Registered User
    Join Date
    10-17-2013
    Location
    Ardooie
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Prevent overwriting cells with formulas

    Hi Winon, it works in a blank workbook but somehow I can't seem to fit the code in where it has to be (excisting workbook that allready has a SelectionChange event).
    I'll attach the workbook so maybe you can have a look to merge both codes.

    Don't worry about the names, it's nothing private, just random names

    The range that needs the protection is K9:O177

    Thanks in advance, appreciate it !
    Attached Files Attached Files
    Last edited by louvaek; 11-28-2018 at 11:34 PM.

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Prevent overwriting cells with formulas

    Hi louvaek,

    Gee my bietjie tyd asseblief.

    Dankie

  8. #8
    Registered User
    Join Date
    10-17-2013
    Location
    Ardooie
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Prevent overwriting cells with formulas

    Quote Originally Posted by Winon View Post
    Hi louvaek,

    Gee my bietjie tyd asseblief.

    Dankie
    Sure, no pressure not urgent anyway

    BTW your dutch is horrible hahaha

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Prevent overwriting cells with formulas

    Hi louvaek,

    Probeer die een!

    Dankie
    Attached Files Attached Files

  10. #10
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Prevent overwriting cells with formulas

    Hi louvaek,

    This sample Workbook will suit you much better!

    Enjoy
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-17-2013
    Location
    Ardooie
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Prevent overwriting cells with formulas

    Yes !!!!

    Thanks man, works perfect !!

    Ik ben je dankbaar

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Prevent overwriting cells with formulas

    Jy's welcome,

    Bly ek could help!

    Dankie vir which Reputasie!

    And you don't even try Afrikaans.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Thanks.

    Have an awesome day!!!

  13. #13
    Registered User
    Join Date
    10-17-2013
    Location
    Ardooie
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Prevent overwriting cells with formulas

    Quote Originally Posted by Winon View Post

    And you don't even try Afrikaans.

    Ek is dankbaar vir jou

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Prevent overwriting cells with formulas

    And you don't even try Afrikaans....
    You my friend, Rock!

    Google translation has it about 90% correct. But then again within the context of your "Ik ben je dankbaar", it is not wrong. In Afrikaans, the correct gratitude would translate to "Ek is grateful vir jou help" in Dutch.

    No matter how it comes either way, I truly believe we could at least understand each other... hahaha

    Thank you so much for your response!

    Geniet van de dag

  15. #15
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Prevent overwriting cells with formulas

    Ik ben je dankbaar vir jou help = "Ek is dankbaar vir jou hulp" in Afrikaans.

  16. #16
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Prevent overwriting cells with formulas

    Hi louvaek,

    I forgot to tell you that you can use the same code on any Sheet of your choice. All you need to do is to change the Sheet # and the Range/s you wish to target.

    Please see the attached Workbook for example, and if you have any questions, please just ask.

    Kind regards.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Prevent overwriting when saving as pdf
    By Jacc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-26-2017, 01:30 PM
  2. How do I prevent Macro from overwriting data
    By MooseAUH in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-05-2013, 09:04 PM
  3. lock Subtotals to prevent overwriting.
    By gruf1968 in forum Excel General
    Replies: 1
    Last Post: 03-12-2010, 07:01 PM
  4. Prevent overwriting cells
    By MGT2000 in forum Excel General
    Replies: 0
    Last Post: 10-29-2008, 02:11 PM
  5. Prevent overwriting file...
    By hutch@edge.net in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2008, 11:47 AM
  6. How to prevent overwriting of file ?
    By asitagrawal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-25-2008, 10:23 AM
  7. Prevent user from overwriting file
    By peter.thompson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-14-2006, 08:35 AM

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