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

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


.

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


.

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" wrote:

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


.

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 Singmin wrote:

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

--

Dave Peterson

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 wrote:

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 Singmin wrote:

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