Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Southern Highlands, New South Wales, Australia
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Protection (Excel 2000/Win98SE)

    Is there a way to visually indicate to a user the cells that data should not be entered into. For example, in row 1 of a sheet I want data to be entered into col b only so I want to "black out" col a, c, d, etc Currently, I have "greyed out" the cells not to be used by applying a grey fill colour but this grey area also prints which I don't want it to do. I know I could protect the whole worksheet and then unprotect each cell but this does not provide an immediate visual indication to the user that "data for this row is to be entered in this cell".
    Cheers
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Protection (Excel 2000/Win98SE)

    You could use Conditional Formatting to set the fill color to gray if one particular cell is a particular value. Then set that cell to some other value before printing. I would do this in combination with worksheet protection to keep them from entering where they are not supposed to.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cell Protection (Excel 2000/Win98SE)

    You could use the BeforePrint event to trap the Print operation, remove the cell coloe, print and replace the cell color. To do this follow these steps :-

    (This assumes you only want it to apply to Sheet1 - change the reference to Sheet1 below if necessary)

    1. Select the range that you do not want entries in, apply the color and then give the range (it does not need to be contigious) a name, say NoEntry.

    2 .Next select a single cell, away from the print area, and enter a value of 0, and name it say PrintOK

    3. Put the following code in the ThisWorkBook object of your project.

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.Name = "Sheet1" Then
    If Range("PrintOK") = False Then Cancel = True
    Call PrintSheet
    End If
    End Sub

    4. Put the following code in a general module

    Sub PrintSheet()
    With ActiveSheet
    <font color=red> .Unprotect</font color=red>
    .Range("PrintOk") = True
    .Range("NoEntry").Interior.ColorIndex = xlNone
    .PrintOut
    .Range("NoEntry").Interior.ColorIndex = 15
    .Range("PrintOK") = False
    <font color=red> .Protect</font color=red>
    End With
    End Sub

    Omit the red lines if you do not have the cells proteced. If you have a protection password you will need to include that.

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Protection (Excel 2000/Win98SE)

    Depending on the type of end-user you are dealing with, couldn't you also try using Data Validation, and simply make it so that when any of those cells are clicked, they will get the message box "don't input data here" (or some such statement). Very visual for the very elementary.
    Pribb

Posting Permissions

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