Macro to Preserve Data, Based on User's Checkmark

    • New

    Hi All,


    I have a 100-row spreadsheet (w/o password) consisting of:


    A. Unlocked/non-formula cells in columns B through D

    B. Locked/formula cells in columns E-K (values derived from user’s text entries in columns B through D)

    C. Data Validation at column N (referenced to a range list containing a checkmark and a blank)


    I need a button-based macro that will poll column N for checkmarks, and for each row having one in column N, do the following:


    A. Lock that row’s cells in columns B through D

    B. Replace the formulas in the column E through K cells with their values (and maintain their locked condition)


    The purpose of this macro is to prevent future changes to the row's entries and calculations, once the user placed a checkmark in column N.


    I've attempted to duplicate similar coding found on the web, but being a non-expert at VBA, the frustration has beat me. Any assistance would be greatly appreciated . . .

    • New

    Example attached. Sheet 1 is protected (password - abc)


    It also has additional conditional formatting to give locked cells a light grey background just to highlight changes to the locked status in Cols B-D.


    Updates are run manually. Make changes as needed in Col N and click the 'Update' button. This runs down column N, cells 1 to 10 (it's just an example, after all), and whereever it finds a 1/True value makes the changes in B-D & E-K.


    The first time you click the Update button you will be prompted for the password. Not sure if you are more concerned with security or just preventing accidential overwrites so went for the secure option. You will only be prompted once per session.

    • New

    Hi cytop,


    Wow, I could never have envisioned such a quick and professional-looking response - it's a bit overwhelming. This will give me a reason to study your comments to get a better understanding of the flow and logic.


    I do have a couple follow-up question, if you don't mind.


    Q1. Does the variable 'c' represent any character found in column N, and if so, what would I need to change to specifically only find the checkmark (Chr 2714)?


    Q2. What is the function of variable 'C1' - other than being declared, I found it used in no other location.


    Q3. Your comment re: "No comments . . . what to do with the 'check mark' . . . " prompts me to ask: What additional coding would be needed to terminate the sub's processes after one pass through all 100 rows?

    • New
    1. 'c' represents a cell in a range. It loops in the range N1:N100 checking each individual cell. If its value is a 1 (or True) then that row is updated. I assumed the value for the cell in Col N. If it is not 1/0/True/False then just change the code that does the check to suit. Can't comment any more as I don't know what is actually in the cell. (It would have been helpful if you had attached an example workbook - it's still not too late).
    2. It's a historical artefact. Perhaps the British Museum or The Center for Computing History might be interested. (In other words, I didn't tidy up after myself).
    3. It simply passes through each cell in the defined range once and then stops. No need to check anything.

    Edit: After reading - you mention " ... specifically only find the checkmark (Chr 2714)". The example code is looking for a True/False value. It will not find anything if the cells contain a string Chr(WhateverItIs). It'll work - it just needs to know what to check for,

    Edited 2 times, last by cytop ().

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login