Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

This is a discussion on Is there a way to protect the format of a cell? within the Excel Questions forums, part of the Question Forums category; I've tried conditional formatting. I've tried various Protection techniques. I need my users to input in Yellow, unprotected cells only. ...

  1. #1
    New Member vettetax's Avatar
    Join Date
    Jul 2006
    Location
    Phoenix, AZ
    Posts
    45

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

    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?

  2. #2
    Board Regular
    Join Date
    Jan 2007
    Posts
    231

    Default

    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.
    So close, yet so far away... thank god for these Boards!

  3. #3
    Board Regular
    Join Date
    Jan 2007
    Posts
    231

    Default

    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

  4. #4
    Board Regular
    Join Date
    Jan 2007
    Posts
    231

    Default

    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.
    So close, yet so far away... thank god for these Boards!

  5. #5
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    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

  6. #6
    Board Regular
    Join Date
    Apr 2006
    Posts
    2,140

    Default

    With this code, no matter what format your cells have, it will stay there !
    Exept if a locked cell is dragged to the unlocked cell(s).

  7. #7
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    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

  8. #8
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    about avoiding to drag cells
    http://www.mrexcel.com/board2/viewto...event+dragging

    somebody any idea how to prevent dragging by code?
    I mean *not* disable the feature as in the link, but notice and undo it?
    seems quite impossible to me

  9. #9
    Board Regular
    Join Date
    Apr 2006
    Posts
    2,140

    Default

    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

  10. #10
    Board Regular
    Join Date
    Apr 2006
    Posts
    2,140

    Default

    I see you changed your post: so we agree that your first was not correct
    Not valid since the sheet is protected - would be valid for sheets not protected.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com