Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro error when Sheet is protected (Excel 2000)

    Dear Excel Experts,

    I have a macro that will fill a cell with a specific color when a certain number is typed into the cell. All cells that do not need an input are protected. The macro below works fine when the sheet is unprotected but as soon as I protect the worksheet (even though these cells are not protected) I get this error message:

    Run-time error

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

    Re: Macro error when Sheet is protected (Excel 2000)

    Cell formatting is disabled on protected worksheets. The macro will have to unprotect the worksheet before changing the cell color. Like this:

    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer

    If Not Intersect(Target, Range("F8:AI13")) Is Nothing Then
    Select Case Target
    Case 1
    icolor = 1
    Case 2
    icolor = 8
    Case 3
    icolor = 4
    Case 4
    icolor = 15
    Case 5
    icolor = 6
    Case 6
    icolor = 26
    Case 7
    icolor = 3
    Case Else
    End Select
    Me.Unprotect "MyPassword"
    Target.Interior.ColorIndex = icolor
    Me.Protect "MyPassword"
    End If
    End Sub
    </code>

    If the protection is password protected, change the "MyPassword" in both places to whatever the password is. If the protection is not password protected, then delete "MyPassword".
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro error when Sheet is protected (Excel 200

    Many thanks for your quick reply Legare. i am thankful for your assistance. It worked like a charm

    aussiehans

Posting Permissions

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