text display in textbox in sheet

[Av8tordude]

I have a table (Range B11 - L250). I would like to change this code to do the following...

1. If I select a cell outside B11 - L250, i would like the textbox to display "New Expense"

2. If i select any cell inside B11 - L250 that has data, I would like the textbox to display "Edit Expense".

3. If i select any cell inside B11 - L250 that does NOT have data, I would like the textbox to display "Edit Expense".

Could someone help, please.

Thank you for your help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Set r = Application.Intersect(Target, Range("B11:B250"))
If r Is Nothing Then Exit Sub
If r.Cells.Count <> 1 Then Exit Sub

With ActiveWorkbook.ActiveSheet.Shapes("Register")
If r.Value2 = Empty Then
.TextFrame.Characters.Text = "New Expense"
.TextFrame.Characters(1, 11).Font.Color = 16777215 'white
.Fill.ForeColor.RGB = RGB(0, 0, 255) 'blue
Else
.TextFrame.Characters.Text = "Edit Expense"
.TextFrame.Characters(1, 12).Font.Color = 0 'Black
.Fill.ForeColor.RGB = RGB(255, 0, 0) 'red
End If
End With
End Sub

 

[AlphaFrog]

It looks like your criterias #2 and #3 are the same

2. If i select any cell inside B11 - L250 that has data, I would like the textbox to display "Edit Expense".

3. If i select any cell inside B11 - L250 that does NOT have data, I would like the textbox to display "Edit Expense".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub   [COLOR="Green"]' Ignore multi-cell selection[/COLOR]
    
    With ActiveWorkbook.ActiveSheet.Shapes("Register")
    
        If Not Intersect(Target, Range("B11:L250")) Is Nothing Then
            [COLOR="Green"]' Within range B11:L250[/COLOR]
            If IsEmpty(Target) Then
                [COLOR="Green"]' Within range and empty[/COLOR]
                .TextFrame.Characters.Text = "Edit Expense"
                .TextFrame.Characters(1, 12).Font.Color = 0 'Black
                .Fill.ForeColor.RGB = RGB(255, 0, 0) 'red
            Else
                [COLOR="Green"]' Within range and not empty[/COLOR]
                .TextFrame.Characters.Text = "Edit Expense"
                .TextFrame.Characters(1, 12).Font.Color = 0 'Black
                .Fill.ForeColor.RGB = RGB(255, 0, 0) 'red
            End If

        Else
            [COLOR="Green"]' Outside range B11:L250[/COLOR]
            .TextFrame.Characters.Text = "New Expense"
            .TextFrame.Characters(1, 11).Font.Color = 16777215 'white
            .Fill.ForeColor.RGB = RGB(0, 0, 255) 'blue
        End If
        
    End With
    
End Sub

 

[Av8tordude]

Actually they are not the same.

2. if cell C25 has data, I would like the textbox to display "Edit Expense"

3. If select cell D26, I would like the textbox to display "New Expense"


item 2 & 3 covers me when I'm within the table (B11 - L250)

 

[Av8tordude]

AlphaFrog

I was able to understand your comments in your code and fix it to what I was trying to accomplish.. Thank you for helping me. I really appreciate it.

Here's what I was trying to accomplish..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    'If Target.Count > 1 Then Exit Sub   ' Ignore multi-cell selection
    
    With ActiveWorkbook.ActiveSheet.Shapes("Register")
    
        If Not Intersect(Target, Range("B11:L250")) Is Nothing Then
            ' Within range B11:L250
            If IsEmpty(Target) Then
                ' Within range and empty
            .TextFrame.Characters.Text = "New Expense"
            .TextFrame.Characters(1, 11).Font.Color = 16777215 'white
            .Fill.ForeColor.RGB = RGB(0, 0, 255) 'blue
            Else
                ' Within range and not empty
                .TextFrame.Characters.Text = "Edit Expense"
                .TextFrame.Characters(1, 12).Font.Color = 0 'Black
                .Fill.ForeColor.RGB = RGB(255, 0, 0) 'red
            End If

        Else
            ' Outside range B11:L250
            .TextFrame.Characters.Text = "New Expense"
            .TextFrame.Characters(1, 11).Font.Color = 16777215 'white
            .Fill.ForeColor.RGB = RGB(0, 0, 255) 'blue
        End If
        
    End With
    
End Sub