excel vba 根据值更改文本框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28361284/
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
excel vba change textbox based on values
提问by SKP
I am trying to change the font color based on the values in the cells(16,3). For some reason it is not working. The present value at 16,3 is 94% and it is showing up in green color instead of amber. Your help would be appreciated.
我正在尝试根据单元格（16,3）中的值更改字体颜色。由于某种原因，它不起作用。16,3 处的现值为 94%，显示为绿色而不是琥珀色。您的帮助将不胜感激。
Private Sub TextBox1_Change()
TextBox1.Value = Cells(16, 3).Text
Cells(16, 3).Font.Bold = True
If TextBox1.Value > 95 Then TextBox1.ForeColor = RGB(0, 128, 0) 'Green
If TextBox1.Value > 80 And TextBox1.Value < 95 Then TextBox1.ForeColor = RGB(255, 153, 0) 'Amber
If TextBox1.Value < 80 Then TextBox1.ForeColor = RGB(255, 0, 0) 'Red
End Sub
回答by Alan K
You'll get that result if the value in the cell is in text format rather than as an actual value. Is it possible that that's the case?
如果单元格中的值是文本格式而不是实际值，您将得到该结果。有可能是这种情况吗？
The other thing to bear in mind, though this wouldn't be the cause of your existing problem or the text would always be red, is that 94% is actually 0.94, not 94.
要记住的另一件事是，尽管这不会是您现有问题的原因，或者文本始终为红色，但 94% 实际上是 0.94，而不是 94。
Edit: (I'm doing an edit to my original answer in response to the comment because I need to include code which does not go into comments well)
编辑：（我正在对我的原始答案进行编辑以回应评论，因为我需要包含不能很好地进入评论的代码）
You have a few other problems.
您还有其他一些问题。
The first is that if this is being driven by the value in cell(16,3) I'm not sure that you should be driving it from the Textbox1_Change event. By doing that you are waiting for someone to enter a value into that text box and immediately overwriting it with whatever is in that cell. Better to populate the entry during the form load.
第一个是，如果这是由 cell(16,3) 中的值驱动的，我不确定您是否应该从 Textbox1_Change 事件驱动它。通过这样做，您正在等待某人在该文本框中输入一个值并立即用该单元格中的任何内容覆盖它。最好在表单加载期间填充条目。
Much depends on what you're trying to do with this, and I have no information on that.
这在很大程度上取决于你想用它做什么，我没有这方面的信息。
Second, if you step through the code you'll find that TextBox1.Value is returning a string; it has double quotes around it. But you are comparing it to a numeric set of values.
其次，如果您单步执行代码，您会发现 TextBox1.Value 正在返回一个字符串；它周围有双引号。但是您将它与一组数值进行比较。
The third is that your code does not deal with the situation where there is an exact match of (say) 80%.
第三是你的代码没有处理（比如）80% 的完全匹配的情况。
The fourth is that if you suck the value straight from the cell, and it really is a percentage value, it'll come up as 0.94, not formatted as a percentage.
第四个是如果你直接从单元格中提取值，它确实是一个百分比值，它会显示为 0.94，而不是格式化为百分比。
This deals with the lot.
这涉及很多。
Dim rng As Excel.Range
Set rng = ActiveSheet.Cells(16,3)
TextBox1.Value = Format(rng.Value, "00%")
rng.Font.Bold = True
'You should really implement error checking here.
'There's no point reading the value from the textbox if it's coming from a cell.
'Just use the cell value.
If rng.Value >= 0.95 Then
TextBox1.ForeColor = RGB(0, 128, 0) 'Green
ElseIf rng.Value >= 0.8 And rng.Value < 0.95 Then
TextBox1.ForeColor = RGB(255, 153, 0) 'Amber
ElseIf rng.Value < 0.8 Then
TextBox1.ForeColor = RGB(255, 0, 0) 'Red
End If
Set rng = Nothing
回答by Dubison
If you use
如果你使用
TextBox1.Value = Cells(16, 3).Value
instead of
代替
TextBox1.Value = Cells(16, 3).Text
You will see the real values used in your if statements. And result will be like below:
您将看到 if 语句中使用的实际值。结果将如下所示：
If Text Type of related cell is : "General"
如果相关单元格的文本类型是：“常规”
And if you use related cell type as "Percentage". You will get following results
如果您使用相关的单元格类型作为“百分比”。你会得到以下结果
So, depending on your preferences you can either use the cell value as general or you can convert it in the macro from percentage to general and display it as it is.
因此，根据您的偏好，您可以将单元格值用作一般值，也可以在宏中将其从百分比转换为一般值并按原样显示。
If you want to keep using Percentage in your related sheet which means actually as value you will have 0.XX in that cell however it will display as XX%, then you can simply multiply your value with 100 as follows:
如果您想在相关工作表中继续使用百分比，这意味着实际上作为值，您将在该单元格中有 0.XX 但它将显示为 XX%，那么您可以简单地将您的值乘以 100，如下所示：
TextBox1.Value = Cells(16, 3).Value * 100
