VBA if row 3 contains text then insert a new row

  • Hello everyone,


    I am new to this forum and a beginner with VBA's.

    I have the following problem to solve:


    My document contains 4 columns and multiple rows. It is a datasheet that keeps track of all information about a customer.

    In the first column it has the date, the second the user, the third the customer number, the fourth the itemname.

    Every time that someone works in this sheet he/she starts with the date in row 3/column A. So you have to insert a new row in 3.

    You can imagine after time the list is very long. For that reason I want to add new information at the beginning of the list and not at the end.


    And this I wanted to do automaticly.


    So if row 3 contains text than insert a row above automaticly.


    I hope it is clear what I want to achieve. 8o


    Example:

    DateUserCustomerItem
    01-09-16ESTK000000Received Order
    15-08-16APJK000000Telephone call
    05-08-16APJK000000Send offer
  • Hello and Welcome to the Forum :)


    You could test


    Code
    1. Private Sub Worksheet_Activate()
    2. Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    3. End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Great ... Thanks a lot for your file ...


    Do you need that whenever you place your cursor in cell A3 ... to have a new row inserted ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi again,


    Yes ... it is possible ...


    But you do need to insert two instructions in your macro :


    At the very beginning : ActiveSheet.Unprotect

    and

    At the very end : ActiveSheet.Protect


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim,


    I guess the position, I choose for adding the information to secure, the worksheet is wrong. Since I get an error. Sorry to ask you, but would it be possible for you to insert it?


    I am a nono when it comes to VBA:rolleyes:

  • Dear Carim,


    Sorry for my late reply but I did not find the time before to reply. Thanx it works but unfortunately it is not possible now to add information in cel 3B, 3C and 3D. Only 3A is rewritable.


    Is it possible to unlock these too?


    Thx.

  • Hello,


    You can test following :

    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. If Target.Address <> "$A$3" Then Exit Sub
    3. ' Make sure to use your own password and replace "leroy"
    4. Me.Unprotect "leroy"
    5. Application.EnableEvents = False
    6. Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    7. Target.Offset(-1, 0) = Date
    8. Target.Offset(-1, 1).Select
    9. Application.EnableEvents = True
    10. End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)