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
>
|
Similar Threads
