Is there a way to protect the format of a cell?

vettetax

New Member
Joined
Jul 5, 2006
Messages
47
I've tried conditional formatting. I've tried various Protection techniques.

I need my users to input in Yellow, unprotected cells only. The worksheet is protected. But the input cells are not. However, some users have managed to copy protected cells into unprocted cells screwing up the process for everyone else. I want to allow my users to use the copy function, therefore I want them to be able to copy protected cells into the yellow, unprotected cells, but without messing with the unprotected cell's formatting. Simply explaining the copy/paste special/values function is just not enough. Is there a way to 'lock' a cell's format?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You could do a worksheet change event, which would take the range pasted as the target (your unprotected cells) and then make the interior cell color yellow, Bold to = T or F, etc.
 
Upvote 0
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents=False

'this would turn it yellow:
Selection.Interior.ColorIndex = 6


'this sets every property imaginable back to the state you've probably got it in now,
'in case your users are re-formatting the heck out of your yellow cells since the
'protected cells they cut/paste from are probably formatted completly different from your
'yellowed cells (they may be of different Font Type, Size, centering, border, etc).
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    
    Selection.Font.Bold = True 'make false if you dont want Bold
    
    With Selection  'right click in the VBE to get properties/methods of these: (i.e. xlCenter, xlBottom, etc for more choices to make it how you want if this is not
                    'matching your curren format
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

Application.EnableEvents=True
End Sub
 
Upvote 0
Should have also mentioned to click on the sheet object this is relating to in the VBE, and right click on it and select, View Code. Then paste this into it.
 
Upvote 0
Hi,

With this code, no matter what format your cells have, it will stay there !
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Erik Van Geit
'only allow PasteValues

If Application.CutCopyMode = False Then Exit Sub

        With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Undo
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        .EnableEvents = True
        .ScreenUpdating = True
        End With

End Sub
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE


kind regards,
Erik
 
Upvote 0
With this code, no matter what format your cells have, it will stay there !

Exept if
1) The paste is to a range of more than one cell which includes one or more locked cells, but the active cell is not an locked cell, or
2) One or more cells are dragged to the locked cell(s).
my focus was on copy, regarding the initial post, but you're rigth about dragging !
your first comment though is not correct to my sense

EDIT:
I see you changed your post: so we agree that your first was not correct
 
Upvote 0
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, [B2:B3])
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    If .CutCopyMode = False Then
        If Not rng Is Nothing Then
            If Target.Cells.Count <> rng.Cells.Count Then Application.Undo
        End If
    Else
        .Undo
        rng.PasteSpecial Paste:=xlPasteValues
    End If
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top