Allow Input Into Formula Cell While Retaining Formula

    Hi all,


    I have some Vlookups in a spreadsheet that I would like to allow users to enter there own values in the field while maintaining the formula.


    I found a thread on this site:


    http://www.ozgrid.com/forum/showthread.php?t=91310


    I folowed the directions, however I get a run time error when it runs. It a states that there is a application-defined or object-defined error. The debug points me to this line of code in the ThisWookbook section:


    If InStr(LCase(.Validation.ErrorMessage), functionName) = 0 Then


    I don't know very much about VBA, so any help would be appreciated.


    Cheers,


    Dana

    Re: Allow Input Into Formula Cell While Retaining Formula


    Hello Dana,


    Would you mind attaching your sample workbook ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Allow Input Into Formula Cell While Retaining Formula


    Hello again,


    Thanks for your sample workbook ...


    Merged Cells should be totally forbidden ... since they are your worst enemy ...:rambo:


    In cell B9 ... do you want to allow manual input AND retain your Lookup() formula ...???

    Re: Allow Input Into Formula Cell While Retaining Formula


    Apologies, the formula is =IF(CellEntry() ="",VLOOKUP(G10,Contact,2,FALSE ),CellEntry()), I must have taken it out when I was getting it ready for public viewing.


    As for the Merged cells, I am coping an existing invoice it order to make it as uniform as I could.

    Re: Allow Input Into Formula Cell While Retaining Formula


    Does it mean you need either a manual input for email ... or the Vlookup() formula ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Allow Input Into Formula Cell While Retaining Formula


    The goal is to allow the user to enter data manually if they want while retaining the vlookup for when the cell is cleared.

    Re: Allow Input Into Formula Cell While Retaining Formula


    I wrote the CellEntry function in the link.
    I've just reviewed it and it used un-documented features of the .Validation object. Those features are no longer present. (One can no longer manipulate the Validation.InputMessage of a cell unless there is validation on that cell).


    I'm at work right now and can't address a workaround or different approach.

    Re: Allow Input Into Formula Cell While Retaining Formula


    Hello,


    Attached is a proposal ...


    In cell B9, just double-click either to insert formula or to clear contents to allow for user input ...


    Hope this will help

    Re: Allow Input Into Formula Cell While Retaining Formula


    This is great, it will certainly do what I need, thanks for getting this out!


    However, I do like the way the initial code operated. The fact that it could work in a merged cell, and it would have the code there at all times could cut down on errors and/or confusion by the end user. If Mike is still willing to take a crack at his code it would be greatly appreciated!


    Cheers,


    Dana

    Re: Allow Input Into Formula Cell While Retaining Formula


    Glad this could help you out ...:wink:


    Cheers

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login