Delete key casue a litte trouble

[Guest]

Hi all,

in change event, I can use trim(target.value) ="" to tell a user is erasing
a cell content until the Delete was pressed. How can I handle it?

Clara

 

[Guest]

why would you think using the delete key would not pass that test?

Do you mean the user had deleted multiple cells?

If target.count > 1 then exit sub

at the start of your routine or figure out how you want to handle when
Target includes more than one cell.

 

[Guest]

hi Tom,

I have a merged cell and using with a data validation. I use the following
function to check whether the cell's content is empty:

Public Function IsErasingCell(Previous As Variant, target As Range) As Boolean
If Trim(Previous) <> "" And Trim(target.Value) = "" Then IsErasingCell =
True
End Function

previous is the value of the cell before change. I test with Spacebar,
Backspace and the empty value in data validation, they all work well, but
when I press Delete key, i got error message: Type mismatch. why?

Clara



thank you so much for your help

 

[Dave Peterson]

Mergecells are a pain.

If you change put something into a merged cell, then the single cell is passed
to the event.

If you clear the merge area, then the whole mergearea is passed to the event.

Do yourself a big favor and stop using merged cells <vbg>.

Maybe you can pick something out of this:

Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)

Dim RealCellCount As Long
Dim myCell As Range
Dim FirstCell As Range

RealCellCount = 0
Set FirstCell = Nothing
For Each myCell In target.Cells
If myCell.MergeCells Then
If myCell.MergeArea.Cells(1).Address = myCell.Address Then
RealCellCount = RealCellCount + 1
If FirstCell Is Nothing Then
Set FirstCell = myCell
End If
End If
Else
RealCellCount = RealCellCount + 1
If FirstCell Is Nothing Then
Set FirstCell = myCell
End If
End If
Next myCell

If RealCellCount > 1 Then
'what to do, more than one cell updated
Else
MsgBox FirstCell.Address
Call IsErasingCell("a", FirstCell)
End If

End Sub
Public Function IsErasingCell(Previous As Variant, target As Range) As Boolean
IsErasingCell = False
If Trim(Previous) <> "" _
And Trim(target.Value) = "" Then
IsErasingCell = True
End If
End Function

 

[Guest]

Hi Dave,

Thank you very much for your code. One question is when the RealCellCount > 1


Clara

 

[Guest]

Hi Dave,

I got answer to my last question.

Thank you very much!!

Clara

 

[Dave Peterson]

RealCellCount wasn't a real <bg> good variable name.

I meant it to represent the number of cells that are actually changing--when
each mergearea is counted as a single cell.

But it was the best I could think of yesterday!

Hi Dave,

Thank you very much for your code. One question is when the RealCellCount > 1

Clara