#Value Error with protected sheet with UserInterFaceOnly True

  • In my workbook i am using some code i found online to allow users to overwrite the result of a formula if needed but not overwrite the formula. the problem is that when the sheets are protected all the cells that are using this formula get a #VALUE error as soon as any change is made to the workbook. I have code that protects all sheets with UserInterfaceOnly enabled when the workbook is opened. I also tried protecting and unprotecting the sheet when the code is ran, but it didnt seem to work and it slows excel down. I have looked high and low for a solution, but cant find one. can anyone help me

    In an unportected cell i am using:

    In ThisWorkbook

    In A Module:

  • Re: #Value Error with protected sheet with UserInterFaceOnly True

    I read through that but dont seem to understand why im having a pproblem. I forgot to mention that it works flawlessly when the sheet is unprotected. The UDF is not modifying the book, just saves the formula if someone enters a value into the cell, if the entered value is deleted, the formulas value will be put back in. Forgive me if im missign something.

  • Re: #Value Error with protected sheet with UserInterFaceOnly True

    I wrote that UDF/ Change event code a few years ago. It utterly fails if the sheet is protected. The only solution that I can find is to not protect the sheet. Also, if you have any validated cells in the sheet, that will cause problems.

    in addition to returning a Value, a UDF can change a cell's .Validation and .Comment object. AND it can add ranges to a Public Collection.
    Since the Calculate event runs after the UDF, the Calculate event can look at that collection and alter the worksheet accordingly.
    The UDF/Calculate combo can do a wide variety of "imposible" things.

    Although this particular routine uses .Validation rather than a collection, the concept is the same. UDF (which can't do some things) passes data to an event, which then does those things.

  • Re: #Value Error with protected sheet with UserInterFaceOnly True

    I just had a further thought.

    You mentioned trying to unprotect/re-protect. Did you do that to both the UDF and the SelectionChange event?

Participate now!

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