Results 1 to 5 of 5
  1. #1
    Super Moderator bbearren's Avatar
    Join Date
    Dec 2009
    Location
    Polk County, Florida
    Posts
    2,450
    Thanks
    14
    Thanked 251 Times in 199 Posts

    Merging adjacent unlocked cells in a protected sheet

    I use an Excel spreadsheet for daily reporting of lithology in a strip mining site. The sheet has fields used as checkboxes, with accompanying labeling. The top of the sheet is just date and location (using coordinates) fields, and the right half of the sheet consists of labeled checkboxes. The left half of the sheet is used for describing the lithology at the time of the inspection.

    I have all the fields used for labeling locked, and the sheet protected, so that the only fields that can be selected are the fields that actually need input. The problem I've encountered is in the open field for lithology. Depending on the lithology, this field is usually divided into from 3 to 5 strata, with descriptions.

    Ideally, I want to select the depth of the field (in rows), merge all those cells and type in a description centered horizontally and vertically, and delineate the lower boundary of the field with a thick border. However, when I select the desired area and try to format, the "Merge cells" checkbox is filled with a black rectangle, greyed out, and can't be selected. I can mark the lower boundary with a thick border, but I can't merge the cells.

    If I unprotect the sheet, I can merge the cells. But protected, even though all those cells are unlocked, I can't merge them. What am I overlooking?
    Create a new drive image before making system changes, in case you need to start over!

    "Let them that don't want it have memories of not gettin' any." "Gratitude is riches and complaint is poverty and the worst I ever had was wonderful." Brother Dave Gardner "Experience is what you get when you're looking for something else." Sir Thomas Robert Deware. "The problem is not the problem. The problem is your attitude about the problem. Do you understand?" Captain Jack Sparrow.
    Unleash Windows

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,499
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Bruce,

    I think that's just one of those things you can't do if the sheet is protected. What I'd do is write a macro and assign a shortcut to it as follows:
    Unprotect sheet
    Merge selected cells
    Protect sheet

    Code:
    Option Explicit
    
    Sub MergeCells()
        
        ActiveSheet.Unprotect
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        
    End Sub
    HTH
    Last edited by RetiredGeek; 2014-08-16 at 09:55.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    bbearren (2014-08-16)

  4. #3
    Super Moderator bbearren's Avatar
    Join Date
    Dec 2009
    Location
    Polk County, Florida
    Posts
    2,450
    Thanks
    14
    Thanked 251 Times in 199 Posts
    Doh!

    Turns out to have been extremely simple. In the "Format Cells" Number tab, I changed the Category from "General" to "Text". Works like a charm, now.

    Reminds me of the words of wisdom I received from my first superintendent after I had been promoted to supervisor. "When you're troubleshooting, start with the simple and proceed to the complex."
    Last edited by bbearren; 2014-08-16 at 09:57. Reason: clarity
    Create a new drive image before making system changes, in case you need to start over!

    "Let them that don't want it have memories of not gettin' any." "Gratitude is riches and complaint is poverty and the worst I ever had was wonderful." Brother Dave Gardner "Experience is what you get when you're looking for something else." Sir Thomas Robert Deware. "The problem is not the problem. The problem is your attitude about the problem. Do you understand?" Captain Jack Sparrow.
    Unleash Windows

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,499
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Doh!

    Is right who would have thunk!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    Super Moderator bbearren's Avatar
    Join Date
    Dec 2009
    Location
    Polk County, Florida
    Posts
    2,450
    Thanks
    14
    Thanked 251 Times in 199 Posts
    Double Doh!!

    I had the sheet unprotected without realizing it. Protected the sheet, and it did not work!

    I started over with your macro, Bruce, and did some editing to get the look I want.

    Code:
    Sub MergeCells()
    '
    ' Descriptions Macro
    ' Lithology Description
    '
    ' Keyboard Shortcut: Ctrl+k
    '
      
        ActiveSheet.Unprotect
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThick
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThick
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        
    End Sub
    Then, using your macro again as a start, I wrote a second macro to leave the bottom of the field open.

    Code:
    Sub Bottom()
    '
    ' Bottom Macro
    ' Bottom Description
    '
    ' Keyboard Shortcut: Ctrl+b
    '
     
        ActiveSheet.Unprotect
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThick
        End With
        With Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        
    End Sub
    I may neaten that up a little bit, but the sheet is doing what I want it to do now. Thanks Bruce!
    Create a new drive image before making system changes, in case you need to start over!

    "Let them that don't want it have memories of not gettin' any." "Gratitude is riches and complaint is poverty and the worst I ever had was wonderful." Brother Dave Gardner "Experience is what you get when you're looking for something else." Sir Thomas Robert Deware. "The problem is not the problem. The problem is your attitude about the problem. Do you understand?" Captain Jack Sparrow.
    Unleash Windows

Posting Permissions

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