Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Oct 2002
    Location
    Mornington Peninsula
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Angry

    Hi All

    In my sheet I have unlocked cells for data entry, and protected sheet with the exception of users being able to "insert rows".

    However, if insert row in between existing rows, i can only enter data in columns where cells above were unlocked (ie. Cols B, C, D). I do need to keep existing headings in Col A locked/protected, but want to add more in between.

    See my attached file: (btw, I realise this is an excel 2003 file, but the question is actually for 2007 - the problem exists in both versions, but am looking for answer specifically for 2007 )

    For example, I have headings down column A from A2, Apples, Oranges and Pears. If user wants to insert row between Apples (A2) and Oranges (A3) to insert Bananas in new A3; cannot enter the heading Bananas in A3 as it has continued the 'locked' format from row above.

    Any help here please?

    Many thanks.
    Attached Files Attached Files
    Many thanks for any help, much appreciated.
    Have a great day!
    WebMistress

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I think you'll have to provide a macro to insert a row and add a row header:

    Code:
    Sub InsertRow()
      Dim strHeader As String
      Dim lngRow As Long
      lngRow = ActiveCell.Row
      If lngRow = 1 Then
    	MsgBox "You can't insert a row above row 1!", vbExclamation
    	Exit Sub
      End If
      strHeader = InputBox("Enter a row header for the new row")
      ActiveSheet.Unprotect
      ActiveCell.EntireRow.Insert
      Range("A" & lngRow) = strHeader
      Range("B" & lngRow & ":D" & lngRow).Locked = False
      ActiveSheet.Protect
    End Sub
    You could assign this macro to a custom toolbar button and/or keyboard shortcut.

  3. #3
    Lounger
    Join Date
    Oct 2002
    Location
    Mornington Peninsula
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Post

    That worked very well,

    Thank you for your prompt response. Have a great day.

    [quote name='HansV' post='794629' date='23-Sep-2009 21:44']I think you'll have to provide a macro to insert a row and add a row header:

    Code:
    Sub InsertRow()
      Dim strHeader As String
      Dim lngRow As Long
      lngRow = ActiveCell.Row
      If lngRow = 1 Then
    	MsgBox "You can't insert a row above row 1!", vbExclamation
    	Exit Sub
      End If
      strHeader = InputBox("Enter a row header for the new row")
      ActiveSheet.Unprotect
      ActiveCell.EntireRow.Insert
      Range("A" & lngRow) = strHeader
      Range("B" & lngRow & ":D" & lngRow).Locked = False
      ActiveSheet.Protect
    End Sub
    You could assign this macro to a custom toolbar button and/or keyboard shortcut.[/quote]
    Many thanks for any help, much appreciated.
    Have a great day!
    WebMistress

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •