Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Change Event (Excel 2003 & VBA)

    Hi All,

    I am using the Worksheet Change for the first time! If a specific cell on a specific worksheet changes (from "No" to "Yes"), I want to make certain cells unlocked and formatted gray.
    I found the specific worksheet, right-clicked, selected "View Code" and then selected the "Change" ... However, when I changed B7 to "Yes", nothing happens!

    Here is my code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if user changed B7
    If Not Intersect(Target, Range("B7")) Is Nothing Then
    ' Temporarily disable other events
    Application.EnableEvents = False
    ' Code to change other cells goes here
    Range("A10:A17").Select
    With Selection.Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    End With
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("A10").Select
    ' Enable other events again
    Application.EnableEvents = True
    End If
    End Sub

    I will be using this for multiple cells on this sheet -- but, I wanted to get at least one cell working before continuing.

    As always, any help is appreciated ...
    --Cindy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Change Event (Excel 2003 & VBA)

    Do you also want to change the cells A10:A17 back to locked, no background color, formulas hidden if the user changes B7 from "Yes" to "No"?

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Change Event (Excel 2003 & VBA)

    Yes ... and make sure A10:A17 are blank, too.

    --cat

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Change Event (Excel 2003 & VBA)

    Here is some modified code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if user changed B7
    If Not Intersect(Target, Range("B7")) Is Nothing Then
    ' Temporarily disable other events
    Application.EnableEvents = False
    ' Unprotect sheet
    Me.Unprotect
    With Range("A10:A17")
    ' Test value of cell B7
    Select Case Range("B7")
    Case "Yes"
    With .Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    End With
    .Locked = False
    .FormulaHidden = False
    Range("A10").Select
    Case "No"
    With .Interior
    .ColorIndex = xlColorIndexNone
    End With
    .ClearContents
    .Locked = True
    .FormulaHidden = True
    Case Else
    ' Do nothing
    End Select
    End With
    ' Protect sheet again
    Me.Protect
    ' Enable other events again
    Application.EnableEvents = True
    End If
    End Sub

    If you have protected the sheet with a password, add it (between quotes) after both the Unprotect and Protect instructions, e.g.

    Me.Unprotect "Secret"

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

    Re: Cell Change Event (Excel 2003 & VBA)

    I copied that code and pasted it into a workbook and it does seem to work. However, I do see a number of problems, some of which could make it seem that it doesn't work:

    1- If the sheet is protected (and it should be otherwise locking the cells will have no effect), then the code can't make the changes without unlocking the worksheet. You would need to add something like this:

    <code>
    ActiveSheet.Unprotect "MyPassword"
    'Code to make changes
    ActiveSheet.Protect "MyPassword"
    </code>

    2- Your code is setting Application.EnableEvents = False as it should before making changes. However, your code has no error recovery in it. So, if you were testing this code and an error occurred while events were disabled, there is nothing to enable them again. From then on, changing the sheet will not cause the change event to fire. If this has happened, you will need to execute a Application.EnableEvents = True in the VBE Immediate window to re-enable events.

    3- Your code does not check to see if B7 is Yes before making the changes. What should happen if the cell was changed to No or something else?

    4- It is not necessary to select cells to change them and doing so is much slower and causes screen flashing.

    I would change that code to something like this:

    <code>
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if user changed B7
    If Not Intersect(Target, Range("B7")) Is Nothing Then
    ' Temporarily disable other events
    On Error GoTo ErHandler
    Application.EnableEvents = False
    ' Code to change other cells goes here
    Select Case Range("B7").Value
    Case "Yes"
    With Range("A10:A17")
    .Interior.ColorIndex = 15
    .Interior.Pattern = xlSolid
    .Locked = False
    .FormulaHidden = False
    End With
    Range("A10").Select
    ' Enable other events again
    Application.EnableEvents = True
    Case "No"
    'Code for No Here
    Case Else
    'Code for anything else here
    End Select
    End If
    Exit Sub
    ErHandler:
    Application.EnableEvents = True
    Resume ErXit
    ErXit:
    End Sub
    </code>
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Change Event (Excel 2003 & VBA)

    Thank you Hans and Legare!

    I'm not sure what I would do without this forum!!!
    --Cindy

Posting Permissions

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