Worksheet_Change and EnableEvents

[ieJasonW]

Hi all, having trouble with a user interface I'm creating.

I have many cells in my UI that need to be changed by the user, but when they change a particular 'trigger' cell I want some formatting done off to the side. The problem is, as I have it currently coded, the formatting occurs whenever the user changes any cell as opposed to the target cell. Any suggestions on how to make the formatting occur only when the target cell value is changed? Running 2007.
Thanks!
ieJasonW

Here's the code:

'***Layer Property
'***Dynamic Formatting
If Cells(13, 4).Select = True Then
Application.EnableEvents = False 'Must turn this off to alter the worksheet
nl = Cells(13, 4).Value 'Number of Layers

'clears the cells gives them plain formatting
For i = 1 To 50 'upto 50 layers of data
For j = 1 To 11 'for each of 11 layer properties
Cells(14 + i, 5 + j).Borders.ColorIndex = 2
Cells(14 + i, 5 + j).Interior.ColorIndex = 2
Cells(14 + i, 5 + j).ClearContents
Next j 'Next row
Next i 'Next column

'Writes in the 'layer' label for the first column of the Layer Properties table
For i = 1 To nl 'upto nl number of layers
Cells(14 + i, 7).Value = i
Next i 'Next layer

'Formats the inner cells of the Layer Properties Table
For i = 1 To nl 'for nl nomuber of layers
For j = 1 To 10 'for 10 layer properties
Cells(14 + i, 7 + j).Interior.Color = RGB(255, 255, 204) 'Set interior color to light grey
Cells(14 + i, 7 + j).Select 'select new cell

With Selection.Borders 'give selection a border
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(192, 192, 192) 'set color to peach
End With
Next j 'Next row
Next i 'Next column

Cells(14, 4).Select
Application.EnableEvents = True
End If 'End of Dynamic Formatting

 

[HalfAce]

Hi ieJasonW,
What is your target range?
If you have only one cell as the target (say A1) then simply add this line at the very top of your existing code:
If Not Target.Address = "$A$1" Then Exit Sub

If you have a range of target cells (say A1 to D10) then use something like this instead:
If Intersect(Target, Range("A1:D10")) Is Nothing Then Exit Sub

In either case, I would add the line(s):
Application.ScreenUpdating = False (and)
Application.ScreenUpdating = True
right above your lines to turn EnableEvents off & on. (Your code will run quicker and smoother.)

Hope it helps.

 

[ieJasonW]

Wow, awesome!

First, I really needed that syntax for the "target.address" line you gave me. I actually have two portions of the UI that get formatted 'dynamically' based on one of two target cells getting changed. Target.Address allows me to signifiy which is which -- exactly what I needed!

Also, thanks for the ScreenUpdating detail too. Its so much better when the screen isn't bouncing around like that. Much better!

Thanks.

ieJasonW

 

[lenze]

Can't you replace this

For i = 1 To 50 'upto 50 layers of data
For j = 1 To 11 'for each of 11 layer properties
Cells(14 + i, 5 + j).Borders.ColorIndex = 2
Cells(14 + i, 5 + j).Interior.ColorIndex = 2
Cells(14 + i, 5 + j).ClearContents
Next j 'Next row
Next i 'Next column

with

With Cells(14, 5).Resize(50, 11)
  .Borders.ColorIndex = 2
  .Interior.ColorIndex = 2
  .ClearContents
End With

 

[ieJasonW]

Maybe. Your version is definitely shorter. My only programming experience is in C -- which can be quite long winded in comparison to VBA. Haha. Thanks for the suggestion.

ieJasonW

 

[lenze]

Play around with it. I'm not really sure what you need, so it might should be

With Cells(14, 5).Resize(11, 50)
or
With Cells(15, 6).Resize(50, 11)
or
With Cells(15, 6).Resize(11, 50)

OR maybe another version. If your range is static, you could just use it

With Range("$E$14:$P$64")
     .ClearContents
     'etc
End With

lenze

 

[HalfAce]

Yes... by all means play around with lenze's suggestions. They are examples of how he or I would likely write it.
The only reason I (mostly) try to stick with the code people post here is because of exactly what you mentioned. I like to assume that they might have an understanding of how their existing code works so any (relatively minor) changes might be a little easier to comprehend.