A Problem with Protection!!

[Domigos]

I am trying to 'Highlight' various single row ranges to indicate shift patterns across a worksheet. Columns are headed as days of the week.

My code essentially colours in a series of cells and indicates the hours to be worked each day.

Sub Highlight5daysE()
Dim MyRange As Range
Application.ScreenUpdating = False
MyCell = ActiveCell.Address
Range(MyCell).Select
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + 4).Select '5Days on Early Shift
Dim c As Range
For Each c In Selection
c.Value = 7.5
Next c
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Worksheets(1).Calculate
Range(MyCell).Select
Application.ScreenUpdating = True
End Sub

All this works fine until I try to protect the rest of the sheet (which holds all sorts of information); when I apply protection, I get an error (large Red cross) with the number 400 next to it. I am at a loss as to why this occurs and would appreciate any help anyone can give me regarding this.

I am colouring the cells using various colours to represent differing shift patterns and then using a UDF to count the various coloured cells so that I can see how many people are rostered for each particular shift.

If I can't protect the rest of the sheet, it is all too easy for anyone using the spreadsheet to 'wipe-out' many of the analysis areas of the sheet.

Can anyone help me with this problem at all? Your help would really be appreciated.

Domigos

 

[TryingToLearn]

Don't understand what you are seeing, but my experince with protection and working with dynamic ranges has taugt me to unprotect the sheet at the beginning of the code and reprotect it at the end.

activesheet.unprotect "password"

code


activesheet.protect "password"

 

[Domigos]

I take your point about using the code to unprotect the sheet at the beginning of the code and re-protect it at the end, but in this case, if the active cell is in an area which I want protected, your solution would unprotect the very things I am trying to protect?

Or am I seeing this all wrong?

Domigos

 

[TryingToLearn]

Sub Highlight5daysE()
    ActiveSheet.Unprotect "YOURPASSWORDHERE"
    Dim MyRange As Range
    Application.ScreenUpdating = False
    MyCell = ActiveCell.Address
    Range(MyCell).Select
    numRows = Selection.Rows.Count
    numColumns = Selection.Columns.Count
    Selection.Resize(numRows + 0, numColumns + 4).Select    '5Days on Early Shift
    Dim c As Range
    For Each c In Selection
        c.Value = 7.5
    Next c
    With Selection.Interior
        .ColorIndex = 34
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Worksheets(1).Calculate
    Range(MyCell).Select
    ActiveSheet.protect "YOURPASSWORDHERE"
    Application.ScreenUpdating = True
End Sub

... leaves the sheet unprotected only during the course of this code running.

 

[kcmuppet]

All this works fine until I try to protect the rest of the sheet (which holds all sorts of information); when I apply protection, I get an error (large Red cross) with the number 400 next to it. I am at a loss as to why this occurs and would appreciate any help anyone can give me regarding this.

I get the same symptoms with one of my workbooks, but not on every PC it runs on, even though they're all running Excel 2003.

Can anyone tell me what typically causes this?