SOLVED

VBA in a Worksheet_Change Event (instead of a IFERROR/INDEX/MATCH formula)

New Contributor

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?

3 Replies
best response confirmed by xoxo-23 (New Contributor)
Solution

@xoxo-23 

See 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.

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.

@xoxo-23 


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.