Worksheet_Change() and range question

[Robert Crandal]

On my spreadsheet, I merged the cells N10, O10, and P10
into a single cell.

I then put the following code in my Worksheet_Change()
function:

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox CStr(Target.Address(0, 0))

End Sub


Now, if you enter data into cell N10 on the spreadsheet,
the message box will return with "N10". However, if you
DELETE the contents of cell N10, then the message box
will say "N1010". Why is this?? How can I tell
the "Target" variable to just give me "N10" string???
(I guess I'm too lazy to write code that parses range
strings, haha)

Thanks!

 

[Rick Rothstein]

Try this MsgBox statement instead...

MsgBox CStr(Target(1).Address(0, 0))

Note: The only change I made was to add (1) immediately after Target
keyword.

 

[Robert Crandal]

Thanks....you're the best!

 

[Mike H]

Robert,

It's not that big a job to check if there's a colon

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(Target.Address(0, 0), ":") > 0 Then
MsgBox Left(Target.Address(0, 0), InStr(Target.Address(0, 0), ":") - 1)
Else
MsgBox Target.Address(0, 0)
End If
End Sub

Mike

 

[Robert Crandal]

That works great. I was just hoping to avoid string operations
in my Worksheet_Change() function because string operations
might slow down my current VBA code.

Rather than using the code below, doesn't VBA have any
range functions to test if a Range object only contains ONE
cell address?? Or is there a built in function that returns exactly
how many cell addresses are in a Range object??

 

[Rick Rothstein]

If you are still talking about the merged cells, then you need to get the
count for the number of merged cells for the given cell reference. For
example, assuming we are still in the Worksheet Change event procedure...

MsgBox Target.MergeArea.Count

Otherwise replace Target with the cell reference; for example...

MsgBox Cells(1, "A").MergeArea.Count

 

[Robert Crandal]

It seems like my problem is more complicated than I first realized.
I initially assumed that users would be deleting data from single
cells, but now I realize they could be deleting data from a
huge area that includes merged and un-merged cells.

Is it possible to structure my Worksheet_Change() function
into a "Select Case" statement in the following manner:

Select Case Target ' ???

' Case "Dated edited/changed in single cell"

' Case "Data deleted from single cell"

' Case "Data deleted from merged cells"

' Case "Data deleted from block containing merged & unmerged cells

' Case Else:

End Select:

How would you re-write the above pseudo-code into VBA code?

As always, thank you for your great help.

 

[Robert Crandal]

I think I was able to solve my own problem. I could structure
my Worksheet_Change() function as follows:

Select Case Target.Count

Case 1: ' ONE cell was edited/deleted
'
' Do stuff
'
Case Else: ' MANY cells were edited/deleted
'
' Do stuff
'
End Select

That would probaly work eh?

 

[Rick Rothstein]

I'm about to go to sleep for the night, but just quickly, this may be a more
useful break out for you...

Select Case Target.MergeCells
Case Null
MsgBox "A mixture of merged and non-merged cells " & _
"or multiple-merged areas are in the Target"
Case True
MsgBox "A single merged area is in the Target"
Case False
If Target.Count = 1 Then
MsgBox "A single non-merged cell is in the Target"
Else
MsgBox "Multiple non-merged cells are in the Target"
End If
End Select

For the Null condition, you will probably have to loop through the cells to
differentiate between the normal cells and the merged cells.