On deadline-help! Insert row below when data validation selected "Add"

[FiservEFTSalesOps]

Hi experts: I have a worksheet with many rows and cols of data. In all cells in Col "M" I have a data validation picklist. Each time the user selects "ADD" from the picklist, I want to insert a blank row immediately below the current row.

I've cobbled together some code, but it's not working. Can someone lend a hand. I'm on deadline and need some help.

 

[Smitty]

How's this:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("M:M")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Value = "Add" <SPAN style="color:#00007F">Then</SPAN> Target.Offset(1).EntireRow.Insert<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[FiservEFTSalesOps]

Hi Smitty: Thanks for the reply (the clock's ticking) I copied the VB as is into the worksheet specific module, but I get nothing. I'm using Excel 2007 and saved as a .xlsm file.

Suggestions?

Say hi to th bay area for me, I moved to Portland last year.

 

[Smitty]

Is your Validation in column M, and in fact have the word "Add" in it?

Be happy you're not here right now, it's supposed to be in the 100's this weekend...Uggh.

 

[FiservEFTSalesOps]

Found the problem.... Target.value is case sensitive... DUH!

Thanks again for the assist here. Hope I can return the favor.

Thi is the best forumn out there!

 

[Smitty]

Found the problem.... Target.value is case sensitive... DUH!

You can fix that with UCase(Target.Value) = "ADD" or LCase(Target.Value) = "add".