VBA Worksheet_Change event, how to detect if multiple cells were selected...

[jmwbowen]

I have a worksheet (user data entry form type) in which I'm trying to catch individual changes in certain cells so that another worksheet holding the entire data table can be updated with those changes. Each of these cells require different changes so I have a Select Case catching which cell is being changed and running the appropriate code.

However, currently, If a user was to Ctrl-Click multiple cells and then Clear Content those cells, the Worksheet_Change event only catches the change for the highlighted cell. The other cells that also had their content cleared are not caught and the corresponding changes for the data table are not made.

How do I start the Worksheet_Change event Sub so that the first thing it does is test to see if multiple cells were selected, and then cancel / undo the change / msgbox the user to select only one cell?

 

[jmwbowen]

Solved.

If Target.Count > 1 Then...

 

[bondre]

Target.Count covers the whole sheet. How to detect multiple cell changes in a particular column. I tried following code but its giving run time error 91 object variable or with block variable not set.
Set Rng = Range("B:B").Cells If Intersect(Target, Rng).Cells.Count > 1 Then
Msgbox "Column B has multiple changes"
Exit Sub
End If

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Intersect(Target, Range("B:B"))
    If Not Rng Is Nothing Then
        If Rng.Cells.Count > 1 Then
            MsgBox "Column B has multiple changes"
            Exit Sub
        End If
    End If
End Sub

 

[bondre]

Thanks..Its working..But when I try to modify the data in multiple cells, excel stops working and restarts.

 

[Andrew Poulsom]

If you are going to modify cells you need to disable events beforehand and reenable when done, eg:

Application.EnableEvents = False
'Code that modifies cells
Application.EnableEvents = True

If you don't the Worksheet_Change event will fire at each modification and may cause and endless loop.

 

[bondre]

Its worked..Thanks..I will try to learn more about this Application.EnableEvents...Thanks very much..