Lock Formatting of a Column

[teknopath]

Anybody know of a good way to lock the formatting of a column in Excel
97? I have a column formatted as Text in order to allow trailing
zero's and I want to make sure nobody tries to change the format and
wipe out any trailing zero's... Thanks


teknopath

 

[Frank Kabel]

Hi
protect your sheet ('Tools - Protection') But then the user can't enter
values in these cells

 

[teknopath]

That's the problem. I need them to be able to enter data still. Just
don't want them to change the format. =) Thanks.

 

[Dave Peterson]

If you unlock the cell(s) and protect the worksheet, then the user won't be able
to change the format of that cell via Format|cells.

 

[teknopath]

Excellent! That worked. You're the best Dave. Thanks for the help!
teknopath

 

[teknopath]

Spoke too soon... Ran into a small bug, or maybe feature. =)

If you copy cells from another spreadsheet that is formatted
differently and paste them into another spreadsheet that I have
protected to disallow format changes, it will change the format of the
cells on the protected sheet to whatever the format was of the copied
cells.

Anybody know how to prevent this?


teknopath

 

[Dave Peterson]

That was my sneaky way of writing this:

If you unlock the cell(s) and protect the worksheet, then the user won't be able
to change the format of that cell via Format|cells.

I don't know of any good way to prevent this, but you may be able to change the
format back later.

You could set up a mirrored (hidden) worksheet that contains the just the
formatting for that sheet. (Copy|paste special formats).

You could have this macro run whenever you think is necessary. (Maybe right
before the workbook is saved or each time the user selects a different cell on
that sheet.)

I chose the second.

If you want to experiment with this, you could copy the sheet (even erase all
the contents--you're never going to use that part) to a new worksheet. I named
mine: formatbackup. I hid that sheet.

Then rightclick on the worksheet that shouldn't be modified. Select view code
and paste this in.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect Password:="hi"

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Worksheets("formatbackup").Cells.Copy
Me.Range("a1").PasteSpecial Paste:=xlPasteFormats
Target.Select

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Me.Protect Password:="hi"

End Sub

(Less sneaking this time!) Be aware that running macros usually kill the undo
stack. So Edit|Undo will be lost.

 

[teknopath]

Thanks, but that won't help me for two reasons.

1.) The users can disable macros and everything is shot to hell.

2.) By the time I copy to the hidden backup, truncation due to a
General format will already have occurred, i.e. 1.10 would have become
1.1 which would be what is copied to the hidden sheet.
Thanks for the info


teknopath

 

[Dave Peterson]

#1. You could have a helper workbook that opens the real workbook. If macros
are disabled, then this helper workbook won't be able to open the real
workbook. (But even then the user could disable events.) I don't have a
workaround for that.

#2. It copies and pastes the formatting from the the hidden worksheet to the
real worksheet. If the user pastes a general 1.1 into a cell, when they move to
another cell, the old format on that hidden sheet is pasted into real worksheet
(and sets it to the way it was).

Another option if you can get by that macro stuff.

You could catch the change, save the new formula/value, do an undo and then
reapply the formula/value.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub


But that breaks if macros are disabled or events are disabled.