Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting Protected Cells (Excel 97 on Windows NT)

    A user in Finance wants to create a protected form that allows formatting changes. I can't attach the file as it is too big.

    UNlocked cells allow users to enter requisite data. The guy creating the form wants to allow people using the form to be able to add color to certain lines. But, when the worksheet is protected, all formatting options become unavailable to both locked and UNlocked cells.

    Is there a way to protect the worksheet/book while continuing to allow certain cells to accept formatting changes? Specifically changing the background color of the cell?

    Thank you for your help!

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Formatting Protected Cells (Excel 97 on Windows NT)

    Meredith,

    With a little programing code you can.

    Paste the following code into a workbook module ( Alt + F11).
    Change the "xxx" password in the code to the actual password.
    On the spreadsheet, use the View | Toolbars | Forms menu to add a button and assign the procedure (code) to it.
    The code unprotects the sheet, shows a formatting dialog box and then protects the sheet.
    If a problem were to occur with the code then a message is displayed that says to contact you.

    'Code follows----------------------------------------------------------------------------------------------------------

    Sub MakeItPretty()
    On Error GoTo GotUgly
    ActiveSheet.Unprotect password:="xxx"
    Application.Dialogs(xlDialogPatterns).Show
    ActiveSheet.Protect password:="xxx"
    Exit Sub
    GotUgly:
    Beep
    MsgBox "Error " & Err.Number & " " & Err.Description & vbCr & _
    "Please see Meredith if the problem persists. ", _
    vbExclamation, " Format Error Alert"
    On Error Resume Next
    ActiveSheet.Protect password:="xxx"
    End Sub
    '---------------------------------------------------------------

    Regards,

    Jim Cone
    San Francisco, CA

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Protected Cells (Excel 97 on Windows NT)

    Thanks for the idea, Jim, but I'd rather not create a macro for this situation. Our users are not particularly experienced and if something went wrong, they'd be clueless.

    Does this mean that a protected workbook/worksheet is not able to have the formatting changed? Again, I appreciate the assistance.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting Protected Cells (Excel 97 on Windows NT)

    Yes, if you protect a worksheet you will lose some "functionality" even in the unprotected section/cells. Changing color is one of them. Autofilter (in XL97, at least) is also disabled.

    The way around this is via a macro.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formatting Protected Cells (Excel 97 on Windows NT)

    Hi Meredith,
    If it's acceptable for the colours of a range of cells to change to one of up to three pre-defined colours automatically, depending on the value entered in one or more of those cells, you could use conditional formatting to make the colour changes - or to change other formatting. This can be used to change anything from a single cell to a whole row, column or matrix or a series of them.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Protected Cells (Excel 97 on Windows NT)

    Please teach me how to use conditional formatting for an entire row! I have only been able to use conditional formatting on a single cell, but I found that very limiting!

    Thank you, thank you, thank you!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting Protected Cells (Excel 97 on Windows NT)

    Select the entire row by clicking the row number. Now you can set conditional formatting in one fell swoop. If you need conditional formatting based on a formula, enter the formula for the first cell in the row (in column A). Excel will automatically adjust it for the other cells in the row.

  8. #8
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Protected Cells (Excel 97 on Windows NT)

    Oh Hans, I'm disappointed! Hopefully I'm doing something wrong.

    I selected the entire row and set the conditional formatting. However, when I enter the value associated with the conditional formatting, only the cell with that value changes formatting. Is there a way for one cell to change the formatting of the whole row (without getting into macros?)?

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting Protected Cells (Excel 97 on Windows NT)

    Sorry, I misunderstood your question. If you want to have conditional formatting for all cells in an entire row depend on the value of a single cell, you must use a formula with the absolute address of that cell.

    Say that you want row 4 to have a red background if cell A4 is negative:
    Select row 4.
    Select Format/Conditional Formatting...
    From the first dropdown list, select Formula.
    In the formula box, enter =$A$4<0
    The $ signs indicate that this is an absolute cell address, not to be adjusted for the other cells in the row.
    Set the format you wish
    Click OK.

  10. #10
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Protected Cells (Excel 97 on Windows NT)

    Works like a charm!

    You rock! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

Posting Permissions

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