Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting Preservation

    Is there a way to lock the formatting on a worksheet or workbook and still be able to enter or change data into the cells?

    We have a large spreadsheet, in which we do a lot of dragging and dropping of information. The problem is the formatting also gets replaced, which we don

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Preservation

    I don't know of a direct method, but you can use the workbook_sheetchange event. The code below needs to be adjusted for your specific needs, but it points you in a possible direction:

    The code below will only preserve the formatting in the range A1:A10 of the active worksheet. Change this according to your needs. There are other restrictions to this code, you have to try it out with your dragging and dropping. The idea is to put the contents of a cell in a variable, then undo the last action, copy the old contents, paste the formats of the old contents and then put the contents of the variable (which is the new data) in the cell. The events should be disabled before undoing otherwise you will end up in a unlimited loop of events. At the end of the code, the events need to be enabled again.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim R As Range
    Dim RR As Range
    Dim x As Variant
    Set R = ActiveSheet.Range("A1:A10")
    Set RR = Application.Intersect(R, Target)
    If Not RR Is Nothing Then
    x = RR.Value
    Application.EnableEvents = False
    Application.Undo
    RR.Copy
    RR.PasteSpecial xlFormats
    RR.Value = x
    Application.CutCopyMode = False
    Application.EnableEvents = True
    End If
    End Sub

    It's just an idea, hope it helps.

  3. #3
    New Lounger
    Join Date
    Mar 2001
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Preservation

    Thank you for your help! I don't work with VBA often and I'm having trouble getting this code to work.

    I went into the VB editor and created a new module. I pasted your sample code into the module. I

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Preservation

    This code cannot be placed in a general module. It shuld be placed in the ThisWorkbook module. In the VBA project, under the Microsoft Excel Objects, double click "ThisWorkbook". Then a module with two dropdown boxes will appear. In the left dropdown box, you will find General and Workbook. Choose Workbook and then you can select the Workbook_ChangeSheet event in the right dropdown box. Choosing this event will place the Sub ... End Sub in the module. The code I posted should be placed there.

Posting Permissions

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