Lock height/width but still be able to edit cell content and other formatting?

[heatherubs]

I have a document that I want to maintain the cell height/width, and when I send it to a colleague for review, he keeps changing the rows and columns to be varying sizes. He needs to be able to make other changes to the cells, such as text or background color, so I don't want to completely protect the document so he can't edit content and some formatting. I only want to protect the height and width of the rows and columns so he won't keep changing them.

Is there a way to do that?

Thanks!

 

[pbornemeier]

You could use the worksheet change event to trigger code to return the fixed rows/columns to their desired dimensions:

Put this code on the code page of the worksheet that should be protected:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'Set range in the next line to cover the cells with the dimensions you want to preserve
    If Not Intersect(Range("A1:Z25"), Target) Is Nothing Then
        'In the section below set the row height and column width for all cells in that section
        Rows("1:25").RowHeight = 29
        Columns("A:F").ColumnWidth = 15
        Columns("G:K").ColumnWidth = 4
        Columns("L:Z").ColumnWidth = 11
    End If
End Sub

 

[heatherubs]

Thank you!

I should also mention that the reason I need this is because when he selects the entire worksheet and modifies all height/width, that action un-hides all the columns and rows I need to keep hidden. When he does change height/width, suddenly he sees a lot of extra things and will randomly delete content, so that's why I'm looking to lock or hide only certain rows and columns.

I've been learning about "very hidden" sheets, too. Is there a way to make rows/columns very hidden instead of just entire sheets?

Thanks for the help!

 

[pbornemeier]

This code will trigger when a cell is changed in the specified range, when the worksheet becomes active, is recalculated or when any cell is selected.

Option Explicit

Private Sub Worksheet_Activate()
     ResetWorksheet
End Sub

Private Sub Worksheet_Calculate()
     ResetWorksheet
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'This event will trigger EVERY time a new cell is selected.  It may cause an undesirable slowdown in processing.
    ResetWorksheet
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    'Set range in the next line to cover the cells with the dimensions you want to preserve
    If Not Intersect(Range("A1:Z25"), Target) Is Nothing Then
        ResetWorksheet
    End If
End Sub

Sub ResetWorksheet()
    'In the section below set the row height and column width for all cells in that section
    'Also specify which rows/columns should be hidden.

    Rows("1:25").RowHeight = 29
    Columns("A:F").ColumnWidth = 15
    Columns("G:K").ColumnWidth = 4
    Columns("L:Z").ColumnWidth = 11
    Range("H:H,M:M,Q:Q,T:T").EntireColumn.Hidden = True
    Range("5:5,12:12,20:20,26:26").EntireRow.Hidden = True
End Sub

Added a few more events to trigger the code more often.

When he clicks in a cell after changing the column widths, the code will trigger and reset columns/rows to hidden.

No way to make a column "very hidden"

You may want to consider redesigning your workbook so the info the other person is messing up is not on the worksheet that he is able to see.

If that is not possible, retraining followed by retraining with threats may be necessary.

 

[Jaafar Tribak]

Have you tried unlocking the worksheet cells via Cell>Format>Protection Tab and then Protect the worksheet after ticking the Format Cells option ?

 

[heatherubs]

Thank you! I'll play with all of this and see if I can manage it with what I'm trying to accomplish.

I would love to coach him on how not to ruin my hard work, but he outranks me and is unfortunately too far gone as far as Excel skills go, that's why I'm trying to find work around methods.