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


excel vba getsaveasfilenamehow to sort numbers in excel in ascending orderfrequently asked interview questions in sqlhow to start macro in excelhow to use sum if formulavba userform initializeunhide worksheetstranspose vbaexcel macro to compare two columnslistbox rowsourceshortcut key for change sheet in excelvba oracle connection stringhow to pull data from another workbook in excelvba active workbookhow to do a pivot table in excel 2007excel vba ado recordsetconvert excel sheet to xmlworkbooks.close vbaaverageif functionexcel vlookup function exampleswhat does vlookup meanmacros programming in exceldelete function in excelexcel advanced tutorialvba booleantutorial on pivot tables in excel 2010interior vbaexcel 3d surfacehow to hide excel rowsunprotect password exceluser defined functions in vbacombobox in excel vbabasic excel tests for interviewsworkbooks.open vbavba excel sheetsremove hyperlinks excelexcel rngconditional formatting excel 2007 if statementsumif in excelelseif vbafind duplicate rows in excel 2007excel how to unhide allwhat are ddl statementsvba select cellvlook up tutorialsimple vba code exampleshow to create pivot table in excel 2007excel macro to hide columnsvba countavba excel xmlduplicate sheet excelvba case exampleembed forms.combobox.1excel delete row vbahow to use average function in excel 2007combining worksheets in excelhow to combine multiple excel sheets into onemicrosoft excel match functionisna excel 2010excel vba calculatemultiple if then statements in excel 2010excel vba hide columnsexcel vba cut and paste rangeexcel 2010 developer modeexcel remove macroproject management excel spreadsheetcombining excel worksheetsexcel macro basichow to edit protected excel fileexcel macro activate workbookremove duplicate in excel 2003macro recording excelexcel hidden columns