Testing a cell

  • Thread starter Thread starter Michael Singmin
  • Start date Start date
M

Michael Singmin

Hello group,

I am trying to detect if a cell is blank.
The Empty case always triggers correctly
but never the Full case when a cell has something in it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B1:B20")) Is Nothing Then
Select Case Target
Case IsEmpty("Target"): [D1] = "Empty"
Case Len("Target") > 0: [D2] = "Full"
End Select
End If
End Sub

Any insight ?

Thanks,

Michael Singmin
 
Michael,

the problem is the quotes: you are testing the length of
the string "Target", rather than the length of the value
in the cell Target. Use:

Len(Target) or Len(Target.Value)

Alternatively, why not just use:

Case Else

Cheers, Pete
 
Does this work??

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
If Not Application.Intersect(Target, Range("B1:B20")) Is
Nothing Then
Select Case Target
Case IsEmpty("Target"): [D1] = "Empty"
Case Not IsEmpty("Target"): [D2] = "Full"
End Select
End If
End Sub
 
Hello Lars,

Unfortunately your code did not work.

Peter: Only Case Else works.

There seems to something strange with Target, it seems to degrade
after certain testing. In the Immediate window, I can validate all
the cells and their lengths. However in the VBE window, these values
seem to be become non existent.

Well, I can always fall back to Case Else.

Thanks for your suggestions,

Michael
============================================================
Lars Kofod said:
Does this work??

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
If Not Application.Intersect(Target, Range("B1:B20")) Is
Nothing Then
Select Case Target
Case IsEmpty("Target"): [D1] = "Empty"
Case Not IsEmpty("Target"): [D2] = "Full"
End Select
End If
End Sub
-----Original Message-----
Hello group,

I am trying to detect if a cell is blank.
The Empty case always triggers correctly
but never the Full case when a cell has something in it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B1:B20")) Is Nothing Then
Select Case Target
Case IsEmpty("Target"): [D1] = "Empty"
Case Len("Target") > 0: [D2] = "Full"
End Select
End If
End Sub

Any insight ?

Thanks,

Michael Singmin


.
Click to expand...
Click to expand...
 
How about:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target(1), Range("B1:B20")) Is Nothing Then
Select Case True
Case IsEmpty(Target(1).Value): Range("D1").Value = "Empty"
Case Len(Target(1).Value) > 0: Range("d2").Value = "Full"
End Select
End If
End Sub

Watch your double quotes (mentioned before). But is there a reason you used
"select case" instead of a simple if/then?

And I used just the first cell in the Target.


Michael said:
Hello group,

I am trying to detect if a cell is blank.
The Empty case always triggers correctly
but never the Full case when a cell has something in it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B1:B20")) Is Nothing Then
Select Case Target
Case IsEmpty("Target"): [D1] = "Empty"
Case Len("Target") > 0: [D2] = "Full"
End Select
End If
End Sub

Any insight ?

Thanks,

Michael Singmin
Click to expand...
 
Well done Dave,

Something new to learn.

I am using Select Case because my application could have 12 different
values and types of values.

Thanks,

Michael

Dave Peterson said:
How about:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target(1), Range("B1:B20")) Is Nothing Then
Select Case True
Case IsEmpty(Target(1).Value): Range("D1").Value = "Empty"
Case Len(Target(1).Value) > 0: Range("d2").Value = "Full"
End Select
End If
End Sub

Watch your double quotes (mentioned before). But is there a reason you used
"select case" instead of a simple if/then?

And I used just the first cell in the Target.


Michael said:
Hello group,

I am trying to detect if a cell is blank.
The Empty case always triggers correctly
but never the Full case when a cell has something in it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B1:B20")) Is Nothing Then
Select Case Target
Case IsEmpty("Target"): [D1] = "Empty"
Case Len("Target") > 0: [D2] = "Full"
End Select
End If
End Sub

Any insight ?

Thanks,

Michael Singmin
Click to expand...
Click to expand...
 
Back
Top