Worksheet_Change_Code Problem

[jim may]

Nothing seems to work properly with this code... Any ideas? TIA, Jim

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B12:I15")) Is Nothing Then
    Select Case Target.Value
        Case Application.WorksheetFunction.IsText(Target)
            MsgBox "You have text"
            'allow formatting
        Case Is = ""
            MsgBox "You have a blank"
            'disallow formatting
        Case IsNumeric(Target)
            MsgBox "You have a number"
            'not sure
        Case Else
     End Select
End If
End Sub

 

[VoG]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B12:I15")) Is Nothing Then
    Select Case True
        Case Application.WorksheetFunction.IsText(Target)
            MsgBox "You have text"
            'allow formatting
        Case Is = Target.Value = ""
            MsgBox "You have a blank"
            'disallow formatting
        Case IsNumeric(Target)
            MsgBox "You have a number"
            'not sure
        Case Else
     End Select
End If
End Sub

 

[Jon von der Heyden]

Try;

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B12:I15")) Is Nothing Then
        Select Case [B][COLOR="Red"]True[/COLOR][/B]
            Case Application.WorksheetFunction.IsText(Target)
                MsgBox "You have text"
                'allow formatting
            Case [B][COLOR="red"]Len(Target) = 0[/COLOR][/B]
                MsgBox "You have a blank"
                'disallow formatting
            Case IsNumeric(Target)
                MsgBox "You have a number"
                'not sure
            Case Else
         End Select
    End If
End Sub

 

[jim may]

Thanks Guys!!
Jim