Solved: Display text & background color in sheet textbox (variable)

I have this code that I want to display a text in a textbox if any cell I select in column B is either blank or not.

If the selected cell is blank, display in the textbox "New Expense" and change the text to "WHITE" and the textbox to "BLUE". If the cell is NOT blank, display in the textbox "Edit Expense" and change the text to "BLACK" and change the textbox to "RED".

Can someone assist. Thank you for your help.

[vba]If Not (Nothing Is Application.Intersect(Target, Range("B11:B250"))) = 0 Then
Worksheets("Calculator").Shapes("Register").TextFrame.Characters.Text = "New Expense"
Else
Worksheets("Calculator").Shapes("Register").TextFrame.Characters.Text = "Edit Expense"
End If[/vba]

[VBA]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 Register
If r.Value2 = Empty Then
.Text = "New Expense"
.ForeColor = 16777215 'white
.BackColor = 16711680 'blue
Else
.Text = "Edit Expense"
.ForeColor = 0 'black
.BackColor = 255 'red
End If
End With
End Sub[/VBA]

Hi Kenneth,

The code I provided partially works as it does change from New Entry to Edit entry if it detects data in the selected, but the words changes on any cell that has data, not just B11 - B250. I only want it to work in column B11 - B250. Also I tried to apply your code, but it errors saying Object required; highlighting .Text = "New Expense"

Could you use the code I provided and make the changes to it. I really appreciate it.

Thank you for you help.

I can not use your code and get it to meet your goals. I have to start from your code and use my methods to meet your goals. Since you did not provide a workbook, I simply named an ActiveX textbox control as Register. If you right click the sheet's tab, View Code, and paste my code, it should work as it did in mine. It did not work in your workbook because you have no textbox control named Register?

If you wanted to work in a shape rather than a textbox control, post an example workbook. That is one of the best things about this forum. It saves a lot of mis-communication.

My apologies. I attached a simple workbook with b11- b15 having data in it.

I renamed your shape to Register. You can do that or replace with the name of your shape.

[VBA]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[/VBA]

Kenneth, it works perfect. Thank you very much!!!:friends:

I have a table (Range B11 - L250). I would like to accomplish 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. : pray2:

Thank you for your help.

That is not much different than what you asked before.
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Set r = Application.Intersect(Target, Range("B11:L250"))

With ActiveWorkbook.ActiveSheet.Shapes("TextBox 3")
If r Is Nothing 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[/VBA]

Hi Kenneth,

While the code changes the textbox when selecting outside of B11-L250 (which is what I'm looking for), It doesn't change the textbox when selecting a cell within the table B11-L250 if the cell does not have any data. Essentially, I wanted the textbox to change if I select a cell that has data within B11-L250 to change to "Edit Expense", but If the cell within B11-L250 does not have data, change to "Register Expense". But, Also change to "Register Expense" if the user selects a cell outside B11-L250.

You might want to reconsider what you ask for. We can only code for what you ask. Reread your previous post and compare to your last post. There seems to be a conflict in what you are asking.

I know it seems conflicting (sorry for be confusing). Your last code accomplishes what I'm asking for, but I also want it to apply when I select a cell within B11-L250 and if the cell does not have data, change the text box to Blue & Register Expense.

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Set r = Application.Intersect(Target, Range("B11:L250"))

With ActiveWorkbook.ActiveSheet.Shapes("TextBox 3")
If r Is Nothing 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[/VBA]

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Set r = Application.Intersect(Target, Range("B11:L250"))

With ActiveWorkbook.ActiveSheet.Shapes("TextBox 3")
Select Case True
Case r Is Nothing
.TextFrame.Characters.Text = "New Expense"
.TextFrame.Characters(1, 11).Font.Color = 16777215 'white
.Fill.ForeColor.RGB = RGB(0, 0, 255) 'blue
Case r.Value2 = Empty
.TextFrame.Characters.Text = "Register Expense"
.TextFrame.Characters(1).Font.Color = 16777215 'white
.Fill.ForeColor.RGB = RGB(0, 0, 255) 'blue
Case Else
.TextFrame.Characters.Text = "Edit Expense"
.TextFrame.Characters(1, 12).Font.Color = 0 'Black
.Fill.ForeColor.RGB = RGB(255, 0, 0) 'red
End Select
End With
End Sub[/VBA]