PDA

View Full Version : Locking Cell Format



CoachG
05-01-2019, 12:43 PM
Is there a way to lock the height and widths of the cells without protecting the sheet? I am looking to be able to keep the print area consistent and not have to reformat everything every time I go to print an updated copy of a document. Alternatively, is there a way to just turn off the auto-width of cells if this would be a simpler way to go about it? It seems like a simple concept to me but can find no information on if it is even possible. Thank you very much for any help I can get on the matter.

p45cal
05-01-2019, 02:44 PM
No, I doubt very much you can.
However, you can do things with vba:
1. Record a macro of your adjusting the column widths and row heights, then tidy up and condense the result to the likes of:
Sub blah()
Range("A1,C1,E1,G1,J1").ColumnWidth = 2.29
Range("B1,D1,F1,H1").ColumnWidth = 12.57
Columns("M").ColumnWidth = 6
Columns("N").ColumnWidth = 6.57
Columns("O").ColumnWidth = 2.86
Columns("P").ColumnWidth = 9.71

Rows(1).RowHeight = 27.75
Rows(4).RowHeight = 24
Range("6:14,17:22").RowHeight = 18.75
Rows("15:16").RowHeight = 36
End Sub

This macro is in a standard code-module so will apply to the active sheet when it's run. You could call it from a Workbook_BeforePrint event handler.

Another way is to prepare a blank worksheet (let's call it TemplateSheet) and have all the correct column widths and row heights (you can do this by copying a sheet with typical data in, adjusting the widths and height, then clearing all the data out, then call that sheet TemplateSheet). You can make that sheet hidden (or xlVeryHidden so that it's not seen if your typical user tries to unhide sheets) then run:
Sub blah1()
'copy columnwidths from hidden sheet:
Sheets("TemplateSheet").Range(ActiveSheet.UsedRange.Address).Rows(1).Copy
ActiveSheet.UsedRange.Cells(1).PasteSpecial Paste:=xlPasteColumnWidths
' copy row heights (there is no equivalent xlPasteRowHeights):
For Each cll In Sheets("TemplateSheet").Range(ActiveSheet.UsedRange.Address).Columns(1).Cells
ActiveSheet.Range(cll.Address).RowHeight = cll.RowHeight
Next cll
End Sub
or you could restrict row height and column width adjustment to just the selected range with:
Sub blah2()
'copy columnwidths of selection from hidden sheet:
Sheets("TemplateSheet").Range(Selection.Address).Rows(1).Copy
Selection.PasteSpecial Paste:=xlPasteColumnWidths
' copy row heights of selection (there is no equivalent xlPasteRowHeights):
For Each cll In Sheets("TemplateSheet").Range(Selection.Address).Columns(1).Cells
ActiveSheet.Range(cll.Address).RowHeight = cll.RowHeight
Next cll
End Sub