Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Location
    Australia
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell format protection (Excel 97 SR2)

    I have developed a roster for staff. Weekends and public holidays are defined by heavy vertical lines. Each cell has a drop down selection for rostered on, rostered off, etc. However, users are dragging cell entries to fill in up to ten days at a time. When cell contents are dragged, the cell borders (denoting weekends, etc) are also dragged to inappropriate locations.

    Is there a way to protect cell borders while allowing cell contents to be dragged to new locations?

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

    Re: Cell format protection (Excel 97 SR2)

    You could disable drag and drop operations by going to Tools, Options, select the Edit tab and uncheck Allow Drag and Drop.

    Application.CellDragAndDrop = False achieves the same result via VBA. However as it is an application property, it will be disabled for all you workbooks until such time as you reset it with Application.CellDragAndDrop = True, in VBA or checking the appropriate Edit option.

    You could use the Workbook Activate/Deactivate events so that drag and drop is disabled only for a particular workbook. The following procedures should be placed in the appropriate ThisWorkbook object in the VBE.<pre>Private Sub Workbook_Activate()
    Application.CellDragAndDrop = False
    End Sub

    Private Sub Workbook_Deactivate()
    Application.CellDragAndDrop = True
    End Sub</pre>


    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format protection (Excel 97 SR2)

    I don't believe there is a way to protect just cell formats and not values.

    You could ask your users to fill cell values using the right mouse button instead of the left one and choose 'Fill Values' from the context menu.

    Another way would be to use the Worksheet_Change event and write VBA which applies the correct formatting to any changed cells, but that might be a little involved. How involved depends on the complexity of your sheets.

  4. #4
    Lounger
    Join Date
    Aug 2001
    Location
    Shropshire, UK
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format protection (Excel 97 SR2)

    Apart from the right-button drag technique there are a couple of other options that spring to mind:
    1) Define a style for the formatting of the weekend and public holidays, then use the worksheet or workbook events (e.g. workbook_close) to trigger a short VBA routine. This routine re-applies your defined styles to the appropriate areas.
    2) Assuming no-one should enter data for w/ends and holidays: ensure all cells that can be changed by a user are UNLOCKED. Then turn on worksheet protection.
    Ewan.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cell format protection (Excel 97 SR2)

    What is the best way to disable Fill? I've been messing with it and can't find a solution.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Cell format protection (Excel 97 SR2)

    John,

    I think the best way to that would be to run some code in the workbook open event, and remove the Fill menu item. Also re-direct the Shortcut Key strokes such as Ctrl-D etc.

    Sorry but thats the best suggestion I can make, but perhaps somebody else can provide a better solution.

    Andrew

  7. #7
    Lounger
    Join Date
    Aug 2001
    Location
    Shropshire, UK
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format protection (Excel 97 SR2)

    Assuming dates are along the top, you could set up the basic formatting for the normal days then use conditional formatting to shade weekends and holidays. The only problem with conditional formatting is you can't specify heavy weight lines.
    In the attatched workbook i have a named range of public holidays.
    The first condition for each cell in the rota is set to shade itself if the date at the top of the column is a saturday or sunday.
    The second tries to match the date at the top with a list of holidays and shades a different colour.

    This way users can drag and fill to their hearts content.
    Attached Files Attached Files

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format protection (Excel 97 SR2)

    I guess you could use conditional formatting in combination with the NetWorkdays function.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format protection (Excel 97 SR2)

    Method 1 in your post is the second method I mentioned <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=83236&page=&v iew=&sb=&o=&vc=1#Post83236>here</A> and method 2 will have no effect.

  10. #10
    Lounger
    Join Date
    Aug 2001
    Location
    Shropshire, UK
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format protection (Excel 97 SR2)

    Why would turning on worksheet protection have have no effect? (true if people have to work during w/ends and hols)
    Ewan.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format protection (Excel 97 SR2)

    Sorry, I didn't read your post carefully enough, you stated that 'assuming people don't work weekends and holidays'. I was simply referring to the fact that protecting a worksheet doesn't stop formats being applied on autofill. However, if you look at the original problem, it does state that people fill 10 days at a time, so they do fill accross weekends and holidays, but I think you've solved this in another thread.

  12. #12
    Lounger
    Join Date
    Aug 2001
    Location
    Shropshire, UK
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format protection (Excel 97 SR2)

    No problem, just had me worried i had posted a load of duff advice, making a <img src=/S/clown.gif border=0 alt=clown width=15 height=15> of myself!! I admit I didn't see the bit about 10 days <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    BTW I realised the I was looking at the conditional formatting from the wrong direction. Instead of conditionally applying formatting for w/ends and holidays it should be the other way around...
    Format all cells as if they were weekends and holidays, i.e. thick borders. Then set the conditional formatting to apply the thin borders (or whatever) to normal weekdays. In my previous attatchment the conditional formula would be :

    =AND(NOT(MATCH(B$9,PublicHolidays)),OR(WEEKDAY(B$9 )>1,WEEKDAY(B$9)<7))

    So the formula says "if it isn't a holiday AND it's monday to friday apply the format"
    Ewan

Posting Permissions

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