Cell format protection (Excel 97 SR2)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Private Sub Workbook_Activate()
    Application.CellDragAndDrop = False
End Sub
	
Private Sub Workbook_Deactivate()
    Application.CellDragAndDrop = True
End Sub

Andrew C

Reply | Quote

What is the best way to disable Fill? I’ve been messing with it and can’t find a solution.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Method 1 in your post is the second method I mentioned here and method 2 will have no effect.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

No problem, just had me worried i had posted a load of duff advice, making a of myself!! I admit I didn’t see the bit about 10 days .
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

Reply | Quote

I guess you could use conditional formatting in combination with the NetWorkdays function.

Reply | Quote