teknopath
--
Regards
Frank Kabel
Frankfurt, Germany
<tekn...@yahoo.com> schrieb im Newsbeitrag
news:1102537803.9...@f14g2000cwb.googlegroups.com...
--
Dave Peterson
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
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.
--
Dave Peterson
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
#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.
--
Dave Peterson