0

Update: If I defined range as input_range from A2:P29. Then If Enter in row 30, then it works. However, the Input_range is not automatically extended and I would like to shift the whole row down not only last row in the range. How could I achieve these two things?

Previous question

Starting with the help of Siddharth Rout, I am able to make this work on new Excel file. However, no matter what I tried on this file

Sample Excel File - it just does not work. I guess there is some kind of structure to prevent from automating adding row. If so, how can I am able to make the code work?

Community
  • 1
  • 1
NCC
  • 819
  • 12
  • 25
  • 43

1 Answers1

2

When you're detecting the Worksheet_Change() event, you need to have your code in the worksheet object, not in a module.

Here's how to do it:

  1. Alt-F11 to open the VBA editor
  2. Copy the Worksheet_Change() sub from Module1
  3. Double-click on Sheet6(itemmaster)
  4. Paste the code in there

Make sure you see Worksheet and Change in the two drop-downs at the top of the editor.

Note: you should always have Option Explicit turned on to require variable declaration. Read this answer to see how to do it: https://stackoverflow.com/a/10653204/138938

Screen-shot of code in worksheet instead of module

Community
  • 1
  • 1
Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
  • I did copy the code to Sheet6(itemmaster) but it did not work. – NCC May 19 '12 at 16:43
  • 1
    Can you upload the new file? I'll let you know what went wrong. – Jon Crowell May 19 '12 at 17:12
  • 1
    Did you follow the steps in my answer? Because the file you posted had the code in module1, not in the code behind for sheet 6. – Jon Crowell May 19 '12 at 20:15
  • 1
    Can you post the new file? I'm sure I can fix it in just a few minutes. – Jon Crowell May 20 '12 at 06:03
  • Okay - If I defined range as input_range from A2:P29. Then If Enter in row 30, then it works. However, the Input_range is not automatically extended and I would like to shift the whole row down not only last row in the range. How could I achieve these two things? – NCC May 20 '12 at 06:08
  • Thank you for your help. Still not as I expected but I am trying to improve it. – NCC May 20 '12 at 06:19
  • You might want to post another question as we are solving a new problem. I can show you how to redefine the input_range every time you add a row. – Jon Crowell May 20 '12 at 06:38