文本框条件格式 - Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22155936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Text Box Conditional Formatting - Excel VBA
提问by Edward Armstrong
I have a dashboard excel spreadsheet that has textboxes. In each text box is a formula pointing to a cell where a formula is applied to the raw data.
我有一个带有文本框的仪表板 Excel 电子表格。每个文本框中都有一个公式，指向一个单元格，其中公式应用于原始数据。
I am looking for a way to conditionally format the text boxes depending on either the value in the text box or the raw data behind if that's easier. Essentially if a textbox has a value over one I would like the font to be green if it is under I would like it to be red. I've had a hard time doing this so far and would appreciate anyones help. Below is my code so far but it wont run. I am a bit of a novice when it comes to VBA.
我正在寻找一种方法来根据文本框中的值或后面的原始数据有条件地格式化文本框，如果这样更容易的话。本质上，如果文本框的值超过 1，我希望字体为绿色，如果它低于我希望它为红色。到目前为止，我很难做到这一点，并感谢任何人的帮助。以下是到目前为止我的代码，但它不会运行。在 VBA 方面，我有点新手。
Sub Test_Change_Text()
If ActiveSheet.Range("A1").Value > ActiveSheet.Range("B1").Value Then
ActiveSheet.Shapes.Range(Array("textbox 1")).Select
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
Else
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
End With
End With
End Sub
Update: The code below is my final working code. This allows for three constraints.
更新：下面的代码是我最终的工作代码。这允许三个约束。
Sub ChangeText()
Dim shap As Shape
For Each shap In Sheets("Output").Shapes
If shap.Type = msoTextBox Then
If IsNumeric(shap.TextEffect.Text) Then
If shap.TextEffect.Text >= 3 Then
shap.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 255, 0)
Else
If shap.TextEffect.Text <= -3 Then
shap.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
Else
shap.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
End If
End If
End If
End If
Next shap
MsgBox "Done"
End Sub
回答by Brad
If they are regular textboxes (i.e. Insert > Text Box) you could try this
如果它们是常规文本框（即插入 > 文本框），您可以试试这个
Sub ChangeText(sht As Worksheet)
Dim shap As Shape
For Each shap In sht.Shapes
If shap.Type = msoTextBox Then
If IsNumeric(shap.TextEffect.Text) Then
With shap.TextFrame2.TextRange.Font.Fill.ForeColor
If CDbl(shap.TextEffect.Text) > 0 Then
.RGB = RGB(0, 255, 0)
Else
.RGB = RGB(255, 0, 0)
End If
End With
End If
End If
Next shap
End Sub
回答by Tim Williams
Assuming your "textboxes" are really shapes with linked text content:
假设您的“文本框”实际上是带有链接文本内容的形状：
Sub DoColor()
Dim shp As Shape, tmp
For Each shp In ActiveSheet.Shapes
'only operate on linked shapes
If Len(shp.DrawingObject.Formula) > 0 Then
tmp = shp.TextFrame.Characters.Text
'ignore non-numeric values
If IsNumeric(tmp) And Len(tmp) > 0 Then
shp.TextFrame.Characters.Font.Color = _
IIf(tmp >= 0, vbGreen, vbRed)
End If
End If
Next shp
End Sub
