Skip Formula Columns in Sheet when Userform active for editing entries

  • Hi,


    I have a workbook with 116 Columns in Excel 2013. The first 38 Cols have strings or dates which the User can initialise a Userform to edit the existing strings or dates or can add new row of data to the sheet.
    My problem, I have several columns with formula to calculate days between date entered and current date (today) and a countdown of days alongside in the next column. E.g. Col K is User input, Col L and M have formula pre-entered in the sheet.


    My userform loop code (thanks to OzGrid) allows the User to obtain the entry to edit by selecting a unique row ID#. Works perfectly replacing / overwriting the current cell contents as required, but I need to "skip" the cells/columns containing formula when the User writes back to the sheet to leave the formula cells "untouched" by the module. There are 20 columns with the formula contained in each row cell of the respective column. The are not numerically sequential.
    E.g Col K User Data, Col L Formula, Col M, then Col N User Data, Col O Formula, Col P Formula, Col Q User Data R Formula, Col S Formula and so on


    I have tried including the formula with the VBA Userform code, without success, as the module uses uses textbox reference to get the date and replace base on Userform content (unless cancelled !)
    I have tried hidden textboxes on the Userform to collect formula and cancel any change event for the respective textbox to cell value without success.


    So Questions:


    1. Is there a way with my self-taught/learning VBA "skills", to have VBA skip these columns within the loop control, or
    2. Is there a way to exclude through a change event and leave the formula intact or
    3. Can the loop be adjusted to skip or not overwrite the formulas (perhaps by using a range reference?)


    Open to other suggestions in how to achieve this of course, being mindful my limited VBA knowledge - no doubt making life too complex and there is a sensible and easier solution available :)


    Have placed relevant get and edit module code below for reference:


    The first part looks up ID# User place sin Textbox 1 and populates Userform




    This part allows any editing / changes to be placed back in workbook (sheet 1) - including my formulas !



    Screen shot of workbook area of issues is hopefully attached also.



    [ATTACH=CONFIG]70282[/ATTACH]



    My extended thanks in advance,

  • Re: Skip Formula Columns in Sheet when Userform active for editing entries


    Using a phone, so comments only, and I may not have understood correctly but you could try setting the Locked property of those textboxes containing formula results to True. That'll prevent the user changing those .


    Then, when writing back to the worksheet check if a textbox is locked before writing its value to the worksheet.

  • Re: Skip Formula Columns in Sheet when Userform active for editing entries


    Quote from cytop;778005

    Using a phone, so comments only, and I may not have understood correctly but you could try setting the Locked property of those textboxes containing formula results to True. That'll prevent the user changing those .


    Then, when writing back to the worksheet check if a textbox is locked before writing its value to the worksheet.


    Thanks Cytop and for your timely response - appreciated.


    I have tried (I believe correctly) in locking the textbox in object properties without success and within the Userform code under each textbox trying variation under textbox12_change event, textbox12_beforeupdate and textbox12_afterupdate events - all without success so far, using a simple line "TextBox12.Locked = True"


    The formula resides within the workbook, that the userform "gets data" from and not within the Userform Textbox itself. My userform pulls data (values or strings) from the workbook to allow the User to make changes to a specific selected row ID# and then return any modifications back to the worksheet, in it simplest form.


    Your suggestion and other things I have tried, continually replaces the worksheet cell containing the formula. Because the Userform has collected the value of that cell (the formula result string) into the Userform via the module code. Hence, when writing back to the worksheet, the Userform has only the textbox value obtained, thus overwriting the worksheet cell formula with the textbox value from the earlier "get data" module (as you would expect).


    What I need is to somehow skip these cells containing the formulas, so the userform has nothing to write back into the worksheet leaving the formula intact and operational. I need all text boxes on userform as the module looks up all predetermined columns (2-38) to get the row data. If I remove these columns, my module of course will find error at the first column not being available to populate a textbox. E.g Col 0 =Textbox_1, Col 1 = Textbox_2 Col 2 = Textbox_3 etc.


    I cannot currently identify a way to stop this "overwrite", to leave the worksheet cell formula intact.


    Thanks for any additional suggestions,

  • Re: Skip Formula Columns in Sheet when Userform active for editing entries


    If the texboxes are locked, then I would have thought


    Code
    For j = 2 To 38 
                        If DataEditUserForm.Controls("TextBox" & j).Locked = False then 
                            Cells(i + 5, j).Value = DataEditUserForm.Controls("TextBox" & j).Value 
                        End If
                    Next j


    Would have skipped those textboxes which are locked when updating the worksheet...


    But, like I said, I don't really understand what you're trying to do and pictures are useless to illustrate a problem like this. Really need a copy of the workbook.

  • Re: Skip Formula Columns in Sheet when Userform active for editing entries


    cytop,


    May I extend my sincerest thanks - I was not looking at placing an if statement for locked textboxes in the module itself which is why I was having issues - simple and effective - now I see it I understand your earlier reference and voila - my formulas are left intact on the worksheet.


    I added your suggestion into both the get data and the edit return function of the module with total success, having crossed checked again my required textbox properties being locked = False for sanity sake.


    Thank you so much for your time - works like a dream! :cheers:

Participate now!

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