Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Code Adjustment/Run Time Error

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following code that Mark provided for me works quite well...until I protect the worksheets. When protected, I receive the following message: Run-time error 1004 - Unable to set the Bold property for the font class.

    Here's the formula
    Private Sub Worksheet_Change(ByVal Update As Excel.Range)
    If Intersect(Update, Range("q13:z137")) Is Nothing Then Exit Sub
    With Update
    .Font.Bold = True
    .Font.Italic = True
    With .Interior
    .ColorIndex = 36
    .Pattern = xlSolid
    End With
    End With
    End Sub


    Any suggestions?

    Thanks,

    Anthony

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A quick guess....

    You are trying to alter something on a protected sheet... I thought the whole point of protecting a sheet was that you couldnt alter stuff in it.

    RET79

  3. #3
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could try this:


    Private Sub Worksheet_Change(ByVal Update As Excel.Range)
    Range("q13:z137").Select
    Selection.Locked = False
    If Intersect(Update, Range("q13:z137")) Is Nothing Then Exit Sub
    With Update

    Range("q13:z137").Select
    Selection.Locked = False

    .Font.Bold = True
    .Font.Italic = True
    With .Interior
    .ColorIndex = 36
    .Pattern = xlSolid

    Range("q13:z137").Select
    Selection.Locked = True

    End With
    End With
    End Sub



  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have a look at what was posted earlier toay
    "Cell Protection"

    James

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I applied the adjusted formula and it returned the following message:

    Unable to set the locked property of the range class.

    Any other suggestions?

    Thanks,

    Anthony

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try something along thes lines:

    Private Sub CommandButton1_Click()
    With Selection
    Sheets("YOUR SHEET NAME HERE").Unprotect password:="YOUR PASSWORD"
    YOUR CODE HERE
    End With
    Sheets("YOUR SHEET NAME HERE").Protect
    password:="YOUR PASSWORD"
    End Sub

    James

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I attempted to adjust the following formula according to instructions provided to me by James. I do not know what I'm doing wrong for I continue to receive the following error message: Complie error - Syntax Error, which is refering to the second to the last line of the code.

    Here's the formula:

    Private Sub CommandButton1_Click()
    With Selection
    Sheets("Oklahoma").Unprotect password:="SWNB"
    Private Sub Worksheet_Change(ByVal Update As Excel.Range)
    Range("q13:z137").Select
    Selection.Locked = False
    If Intersect(Update, Range("q13:z137")) Is Nothing Then Exit Sub
    With Update

    Range("q13:z137").Select
    Selection.Locked = False

    .Font.Bold = True
    .Font.Italic = True
    With .Interior
    .ColorIndex = 36
    .Pattern = xlSolid

    Range("q13:z137").Select
    Selection.Locked = True

    End With
    End With
    Sheets("Oklahoma").Protect
    password:="SWNB"
    End Sub

    Any suggestions?

    Thanks,

    Anthony

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Anthony,

    Here's the code, copy and paste in your sheet1, change sheet name & cell address to suit:

    Private Sub Worksheet_Change(ByVal Update As Range)

    Sheets("Sheet1").Unprotect Password:="Anthony"

    If Intersect(Update, Range("A1:A5")) Is Nothing Then Exit Sub

    With Update
    Range("A1:A5").Select

    .Font.Bold = True
    .Font.Italic = True
    With.Interior
    .ColorIndex = 38
    .Pattern = xlSolid
    End With
    End With
    Sheets("Sheet1").Protect Password:="Anthony"
    End Sub

    James

    _________________


    [ This Message was edited by: James on 2002-05-09 13:30 ]

    [ This Message was edited by: James on 2002-05-09 13:47 ]

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey James!!! Thanks again for the response. It works very well, just one more thing though: when I apply the formula, protect the sheet and attempt to make a change, it does in fact work, however, upon formatting that one specific cell as per the code, the entire range becomes highlighted. Is that supposed to happen?

    All the Best

    Anthony

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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