Protecting Partial Rows and Columns

[shauste]

Hello, I would like to know if there is a way to protect partial rows and columns. As an example, I have a spreadsheet that has data in Columns A - F and through Row 3. I don't want that data to be editable except via a password. However, I would like a user to be able to add more columns after F and more rows after 3. Is this possible?

Thank you

SHAUSTE

 

[Yongle]

One way is to use some simple VBA

Test on a copy of your workbook

Logic
Cell F3 is given name MARKER
F3 is the last cell in the range to be protected
If a (row\column) is (added\deleted) (above\to left of F3) then MARKER moves
The VBA validates (the refers to range of) MARKER
If MARKER is no longer F3 then the last action is undone

What you need to do
1 Give cell F3 a name. Name it MARKER
2 Put code below in the sheet module
3 Save workbook as macro enabled
4 Try adding and deleting rows
- permitted to insert\delete rows 4 onwards
- permitted to add columns to right of F

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("[COLOR=#006400]MARKER[/COLOR]").Address <> "$F$3" Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
    End If
End Sub

To name a cell
- select the cell and type MARKER in the Name Box and hit {ENTER}
(the Name Box is the input box above cell A1)

Place code in sheet module
right-click on sheet tab\ select View Code \ paste code into the code window that appears \ {ALT}{F11} to go back to Excel

 

[shauste]

Thank you for those directions. They worked fine. What I also need is for data in ranges A1:F1, A2:F2, and A3:F3 to be locked/protected from editing. The ranged contain directions within a textbox, column headers, and sample data. Is this possible to configure?

 

[Yongle]

How about...
- unock all cells in the worksheet
- lock cells in A1:F3
- protect the worksheet and allow user to do everything
- contents of A1:F3 would then be protected

You could protect the Textbox with some simple VBA - what kind of textbox is it (how was it created)?

 

[Yongle]

You could prevent the textbox value being amended like this
-required value re-instatement triggered when any cell value is edited (could get cleverer but is it necessary?)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Range("MARKER").Address <> "$F$3" Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
    End If
[I][COLOR=#006400]'form control textbox[/COLOR][/I]
    Me.Shapes("Textbox 1").TextFrame.Characters.Text = "XXXX"
[I][COLOR=#006400]'active-x textbox[/COLOR][/I]
    TextBox1.Value = "XXXX"
End Sub

 

[shauste]

This seems like the simplest solution. Thank you for your help.

 

[Yongle]

thanks for feedback