A Problem with Protection!!

Domigos

New Member
Joined
Mar 20, 2002
Messages
11
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. (y)

Domigos
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
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

New Member
Joined
Mar 20, 2002
Messages
11
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

Well-known Member
Joined
Sep 10, 2003
Messages
723
Code:
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

Active Member
Joined
Nov 2, 2005
Messages
258
Office Version
  1. 365
  2. 2010
Domigos said:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,215
Messages
5,594,883
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top