Cell Change Event (Excel 2003 & VBA)

Topic

New Reply

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

Reply | Quote

Replies

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”?

Reply | Quote

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

–cat

Reply | Quote

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”

Reply | Quote

Thank you Hans and Legare!

I’m not sure what I would do without this forum!!!
–Cindy

Reply | Quote

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:


ActiveSheet.Unprotect "MyPassword"
'Code to make changes
ActiveSheet.Protect "MyPassword"


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:


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


Reply | Quote