How to automatically insert formatted style row or column in a locked Excel sheet saved as xlsm?

Question

To insert a row or column is generally easy with vba. However, do it automatically in a locked cell according to user input is not easy. My question is How to automatically insert formatted style row or column in a locked Excel sheet saved as xlsm?

Explain:

I have a sheet called "Sheet1" is a locked with password, "ABCD", user can only input the data in the colored area. However, sometimes the list is short, while others are long. Automatically insert row and column is a good idea like data-grid in Visual Studio.

Please see the picture, if user enters anything in row 9 in the colored area, then it automatically create a formatted-style row 10 (for Components List 1). If user enter anything to empty row 14, then it inserts row 15 in Components List 2.

Same thing if user enters anything in column H, then I create column I

enter image description here

I do not know how to write the syntax but here is my thoughts on the design:

I must define a name for area in components List 1 (Com1), Components List 2 (Com2), and the blue area (Part_quality) so the program knows which area if should check for user input

Checking if user input data in defined names range. If yes, check if user enter any data in the last row and last column. If yes, unlock the file with predetermined password stored in memory, insert row/column, then locked it

`I do not know how to write the syntax` Well then this is your lucky day as you would try to learn the syntax and then post back the code you tested so that we can help you ;) The event that you need to work with is called the `Worksheet_Change` event. In this event you will see which cell is being edited and then act accordingly :) You can use the names method or even better, you can check for the `cell color` and then take the appropriate action :) I would recommend searching SO, you will find plenty of examples on how to use `Worksheet_Change` event. — Siddharth Rout, May 10 '12 at 08:36
I stuck at very beginning. Please provide a hint that how to check if a cell in a defined name is being edited and how to keep track of if this cell is in the last row of a defined name? — NCC, May 12 '12 at 01:12
Update: I figured out how to determine if the data in a defined range is changed and what is the last row in the range. I need help on how to determine if the editing happened on last row of the defined name range? — NCC, May 12 '12 at 03:18

score 1 · Accepted Answer · edited May 23 '17 at 11:48

enter link description here

Is this what you are trying?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MRange As Range, rng As Range

    On Error GoTo Whoa

    Set MRange = Range("InputRange")

    '~~> Get the last Row of the range
    Set rng = MRange.Range(MRange.Cells(MRange.Rows.Count, 1), _
    MRange.Cells(MRange.Rows.Count, MRange.Columns.Count))

    '~~> Trap changes in the last row of the range
    If Not Intersect(Target, rng) Is Nothing Then
        'If Application.WorksheetFunction.CountA(rng) > 0 Then
            'Application.EnableEvents = False

            'rng.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        'End If
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

The code is created by Siddharth Rout

How to automatically insert formatted style row or column in a locked Excel sheet saved as xlsm?

1 Answers1