PDA

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



av8tordude
03-11-2011, 11:29 AM
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.

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

Kenneth Hobs
03-11-2011, 01:16 PM
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

av8tordude
03-11-2011, 01:33 PM
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.

Kenneth Hobs
03-11-2011, 02:56 PM
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.

av8tordude
03-11-2011, 09:43 PM
My apologies. I attached a simple workbook with b11- b15 having data in it.

Kenneth Hobs
03-12-2011, 08:23 AM
I renamed your shape to Register. You can do that or replace with the name of your shape.

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

av8tordude
03-12-2011, 10:43 AM
Kenneth, it works perfect. Thank you very much!!!:friends:

av8tordude
03-14-2011, 09:11 PM
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.

Kenneth Hobs
03-15-2011, 05:39 AM
That is not much different than what you asked before.
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

av8tordude
03-15-2011, 06:08 AM
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.

Kenneth Hobs
03-15-2011, 06:15 AM
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.

av8tordude
03-15-2011, 06:32 AM
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.

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

Kenneth Hobs
03-15-2011, 08:17 AM
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