Apr 28 2022 12:16 PM
Hello, I am looking to use a VBA in a worksheet_change event instead of formula, so I can leave the cell empty ready for user interface/when the conditions are met the value can be put in the cell automatically and protected at the same time. This was the formula I was using to auto-populate data that's already entered =IFERROR(INDEX($C$7:C7,MATCH($A$7:$A$6001,$A$7:$A$6001,0))," ")
I have a Unique Client Identifier in column A, Client's date of birth in column B, as well as age (Column C), gender (column D) and sex (column E). I would like for the Client's Date of Birth, Age, Gender and Sex to auto-populate once it has already been entered into a previous row in the spreadsheet, so it doesn't need to be entered manually again however it is imperative to protect the spreadsheet. This formula works, however if the client's data has not been entered in a previous row, I need to delete/override the formula with the client's date of birth. When a worksheet is protected, I am not allowed to remove the formula and enter in the date of birth. I am not familiar with VBA/worksheet_change. Does anyone have a code/work around?
May 01 2022 12:08 PM
SolutionSee if you can use the attached workbook as inspiration.
Right-click the sheet tab and select View Code from the context menu to see the code.
May 03 2022 08:24 AM - edited May 03 2022 11:17 AM
Thank you so much for your help! I tried to tweak the code, however no luck. I am using the below table. Column D (Age) populates using a formula. I am struggling to have the unique identifier auto-generate columns with Client DOB, Gender, Sex and Client Type if possible.
May 03 2022 08:43 AM
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
May 01 2022 12:08 PM
SolutionSee if you can use the attached workbook as inspiration.
Right-click the sheet tab and select View Code from the context menu to see the code.