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
--
thank you so much for your help

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.


--
Regards,
Tom Ogilvy


"clara" wrote:

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
--
thank you so much for your help

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


"Tom Ogilvy" wrote:

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.


--
Regards,
Tom Ogilvy


"clara" wrote:

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
--
thank you so much for your help

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




clara wrote:

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

"Tom Ogilvy" wrote:

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.


--
Regards,
Tom Ogilvy


"clara" wrote:

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
--
thank you so much for your help

--

Dave Peterson

Hi Dave,

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


Clara
--
thank you so much for your help


"Dave Peterson" wrote:

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




clara wrote:

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

"Tom Ogilvy" wrote:

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.


--
Regards,
Tom Ogilvy


"clara" wrote:

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
--
thank you so much for your help

--

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!

clara wrote:

Hi Dave,

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

Clara
--
thank you so much for your help

"Dave Peterson" wrote:

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




clara wrote:

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

"Tom Ogilvy" wrote:

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.


--
Regards,
Tom Ogilvy


"clara" wrote:

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
--
thank you so much for your help

--

Dave Peterson


--

Dave Peterson

Hi Dave,

I got answer to my last question.

Thank you very much!!

Clara


--
thank you so much for your help


"Dave Peterson" wrote:

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




clara wrote:

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

"Tom Ogilvy" wrote:

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.


--
Regards,
Tom Ogilvy


"clara" wrote:

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
--
thank you so much for your help

--

Dave Peterson