Worksheet_Change for multiple cells

[tiredofit]

This displays the value of a changed cell on the worksheet:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    
    MsgBox Target.Value
    
End Sub

However if multiple cells are selected, an error message pops up:

Run-time error '13':

Type mismatch

I can suppress this by disallowing multiple cells to be selected, such as:

If Selection.Count > 1 Then

but is there a way to actually make the code work when multiple cells are selected?


Thanks

 

[Tetra201]

Try

    MsgBox Target[COLOR=#ff0000](1)[/COLOR].Value

 

[tiredofit]

Try

    MsgBox Target[COLOR=#ff0000](1)[/COLOR].Value

Thanks but that only displays the first value.

For example, if I cells A1 and B1 contain the values a and b and then I copy both and paste into cell C1, the message box only displays a and not b.

I suppose adapting your code, I would have to somehow note how many cells were selected, then loop to display each one.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   Dim Msg As String
   
   For Each Cl In Target
      Msg = Msg & vbLf & Cl.Value
   Next Cl
   MsgBox Msg
End Sub

 

[Tetra201]

Similar to Fluff's solution but without the initial Line Feed:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Msg, i As Long
    Msg = Target(1).Value
    For i = 2 To Target.Count
        Msg = Msg & vbCrLf & Target(i).Value
    Next
    MsgBox Msg
End Sub

 

[tiredofit]

Thanks for the suggestions.

 

[Fluff]

You're welcome