VBA SheetChange_Lock_Formula.xlsm

Home/VBA SheetChange_Lock_Formula.xlsm

VBA SheetChange_Lock_Formula.xlsm

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Requirement:

[wpdm_file id=60]

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.



Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |November 1st, 2014|0 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

Leave A Comment


Related pages


vba userform listboxcool excel tablesbreak excel password protectionvba activeworksheetexcel worksheet activateexamples of excel chartsexcel macro sortexamples of macros in excelhow to paste special in excelexcel highlight duplicateshortcut formula in excelsumif excel 2007excel basics for beginnersswot analysis template powerpointhow to remove duplicates in excel 2007microsoft excel programming tutorialdouble vbasleep command in vbaactivate sheet vbameeting agenda template powerpointdeveloping macros in excelduplicate finder in excelhiding cells in excelddl statements in sqlvba return value from userformvba cell formulacreate userform in excel 2007using vlookup function in excelcomment excel vbamicrosoft excel developer tablooping excelenable macros in excel 2007 automaticallybubble chart template exceladvanced excel formulas with examples in excel sheetvba adodb recordset exampleexcel 3d surface chartconcatenate in excelvba dir functionnested conditional statementsvba code open filevba print functiongrouped by sqlmatch excel formulaexcel vba protect worksheettask timeline templateadding developer tab in excel 2007define macros in excelhow to use choose function in excelexport data from access to excel using vbavlookup excel 2007 tutorialschedule in excel templatetranspose vbaexcel macro to open files in a folderactive worksheet vbavba excel sorthow to generate charts in excelhr questions for freshers with answers pdfprotected worksheetexcel function syntaxexcel vba 2d arrayformula for vlookup in excel 2007vba excel sort columnupper case excelhow to unprotect cells in exceluserform propertiesvba select case statementunhide rows in excel 2013filter vbamicrosoft estimate templatespss sqlhow to enable developer in excelexcel 2010 data validation list