tracking changes

[viper]

I've gotten this far with this, and it works great. In the code it shows a userform, I only want to have the userform show if any the cells in the range that have a value is changed.

So if someone deletes information in one of the cells the userform will show.

 
If Not Intersect(Target, Range("C11:AG11,C22:AG22,C33:AG33,C44:AG44,C55:AG55,C66:AG66, _
C77:AG77,C88:AG88,C99:AG99,C110:AG110,C121:AG121,C132:AG132")) _
Is Nothing Then
UserForm1.Show
End If

Thank you,

[Grade4.2]

One way to make the userform only show if any of the cells in the specified range have a value is to add an additional check to your If statement. You can use the WorksheetFunction.CountA function to count the number of non-empty cells in the range, and only show the userform if the count is greater than 0.


Here's an example of how you could modify your code to do this:

If Not Intersect(Target, Range("C11:AG11,C22:AG22,C33:AG33,C44:AG44,C55:AG55,C66:AG66, _
C77:AG77,C88:AG88,C99:AG99,C110:AG110,C121:AG121,C132:AG132")) _
Is Nothing And WorksheetFunction.CountA(Range("C11:AG11,C22:AG22,C33:AG33,C44:AG44,C55:AG55,C66:AG66, _
C77:AG77,C88:AG88,C99:AG99,C110:AG110,C121:AG121,C132:AG132")) > 0 Then
  UserForm1.Show
End If

This code will check if the target range intersects with the specified range, and if it does, it will check if there are any non-empty cells in the specified range. If both of these conditions are met, the Userform will be shown.

Dim rng as Range


Set rng = Range("C11:AG11,C22:AG22,C33:AG33,C44:AG44,C55:AG55,C66:AG66, _
C77:AG77,C88:AG88,C99:AG99,C110:AG110,C121:AG121,C132:AG132")


If Not Intersect(Target, rng) Is Nothing And WorksheetFunction.CountA(rng) > 0 Then
  UserForm1.Show
End If

In this example, we define a range object called rng and set it to the specified range. We then use this object in both the Intersect and CountA functions, which makes the code a bit more concise and easier to read.


This is just one way to make the code more efficient. There may be other ways to do it, depending on your specific needs.

[arnelgp]

that was 18 years ago.
the girl is already a woman.

[Grade4.2]

Sorry, I sorted by posts that had very little responses. Still learning how to forum