lock a cell and insert formula in it based on value in another cell using vba in excel
I am having an issue on Excel. I have a drop down menu in cell D5, with three options, A, B, and C.
If A is to be selected, cell content in D6 should become zero/clear-out [i.e., available for manual input by user], and cells D7, D8 and D9 should be locked while having formulas assigned to them as follows: for D7, =D6*G9, for D8 = D6*G10, for D9 = D6*G11
Similarly, if B is to be selected, cell content in D7 should become zero or clear-out, and cells D6, D8 and D9 should be locked while having the following formulas assigned to them for D6 = D7*G8, for D8 = D6*G10, for D9 = D6*G11
And lastly, if C is to be selected, cell content in D8 and D9 to be zeroed out and available for user input, while cells D6 and D7 are locked out and have following formula assigned to them: D6=(D8+D9)*I9, D7= D6*G9
I am completely novice on VBA in Excel and clueless as to how to solve this issue.
I got a code by googling which helps me to lock out cells, but I have no idea how to blank out cells and assign formulas.
The issue is, if I just type in the formula, lets say in D6, and user selects option A in dropdown, he will have option to overwrite value in D6 and later on if option B or C is chosen, D6 will remain static instead of moving in line with the values input in D7-D9 when options B/C are chosen.
Can someone please help
worksheet_Change
I'll start you off...
in VBA you can pickup worksheet Change events... look at the module for the sheet, and pick worksheet_Change
Target = the cell that's changing, so you can add a line like this: If Target.address = Range("A1").address then' the cell changed is A1
'do something
else
'do nothing
endif
To change a cell's formaula, just record a macro, and it will give you the code...
If you need help on VBA, take a look here:
http://excelexperts.com/Free-VBA-Training-Videos