Delete key casue a litte trouble
 

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

Delete key casue a litte trouble
 

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

Delete key casue a litte trouble
 

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

Delete key casue a litte trouble
 

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

Delete key casue a litte trouble
 

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

Delete key casue a litte trouble
 

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

Delete key casue a litte trouble
 

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