英文:
Excel VBA: When Chekcbox ticked, any cells with a value of 0 display a word instead
问题
我有一个包含已经有公式的计算的表格。理想情况下，我想要有一个复选框，当选中时，将表格中所有数值为0的单元格替换为单词"None"。
我已经看到一些关于使用公式的建议，但迄今为止没有成功。
提前感谢任何帮助！
我已经尝试了几种方法，但无法弄清楚如何编写一个复选框宏，其中需要满足两个变量才能运行宏。
英文:
I have a table of calculations which all already have formulas in them. I ideally want to have a checkbox which when ticked replaces all cells in the table which are 0 with the word "None".
I have seen some suggestions using formulas but have had no luck so far.
Thanks for any help in advance!
I have tried a few ways but can't figure out how to write a checkbox macro in which two variables need to be met to run the macro
答案1
得分: 5
以下是您要翻译的内容：
"除非确实必要，否则不应更改数据。在这种情况下，您可以通过设置单元格的“NumberFormat”来实现您的目标。
Excel中的数字格式包含最多4个部分（由“;”分隔）。第一部分用于正数，第二部分用于负数，第三部分用于0（第四部分用于字符串，但我们可以忽略它）。一个可能的数字格式是“General;General;'None'”：除了0以外的所有内容都将应用“General”格式。对于0，Excel将显示“None”（但请注意，单元格的值不会更改，只是在工作表中显示的方式发生了变化）。
现在，您需要做的就是：
o 转到VBA编辑器，添加一个模块（如果没有的话），并添加以下代码：
Sub ShowHideZeros()
With ThisWorkbook.Sheets("Sheet1") ' 替换为您的工作表名称
Dim showNone As Boolean
showNone = (.CheckBoxes(1).Value = xlOn)
Dim r As Range
' 使用此代码格式化所有单元格
Set r = .UsedRange
' 如果您只想格式化特定单元格，请改用以下内容：
' Set r = .Range("A2:A100")
If showNone Then
.UsedRange.NumberFormat = "General;General;""None"""
Else
.UsedRange.NumberFormat = "General"
End If
End With
End Sub
o 在工作表上放置一个复选框，并为其提供有意义的文本，例如“显示“None”为0”。请注意，有两种不同类型的复选框，此示例假定您使用所谓的“表单控件”（这是控件选择的上部分）。
o 右键单击复选框，选择“分配宏”。选择例程“ShowHideZeros”。
[1]: https://i.stack.imgur.com/QfVWc.png
<details>
<summary>英文:</summary>
You shouldn't modify your data unless it is really necessary. In this case, you can achieve your goal by setting the `NumberFormat` of the cells instead.
The number format in Excel contains up to 4 part (that are separated by ";"). The first part is for positive numbers, the second is for negative numbers, the third is for 0 (and the forth for strings, but we can igore that). A possible number format would be `General;General;"None"`: For everything except 0, the `General` format would apply. For `0`, Excel would display `None` (but note that the *value* of the cell doesn't change, only the way it is displayed in the sheet).
Now all you have to do is:
**o** Go to the VBA Editor, add a Module (if you don't have any) and add the following code:
Sub ShowHideZeros()
With ThisWorkbook.Sheets("Sheet1") ' Replace with your sheet name
Dim showNone As Boolean
showNone = (.CheckBoxes(1).Value = xlOn)
Dim r as Range
' Use this to format all cells
Set r = .UsedRange
' Use this instead if you want to format only specific cells:
' Set r = .Range("A2:A100")
If showNone Then
.UsedRange.NumberFormat = "General;General;""None"""
Else
.UsedRange.NumberFormat = "General"
End If
End With
End Sub
**o** Place a checkbox on your sheet and give it a meaningfull text, eg `Show "None" for 0`. Note that there are 2 different types of checkboxes, this example assumes that you use a so called "Form-Control" (that's the upper part of the control selection).
[![enter image description here][1]][1]
**o** Right click on the checkbox and choose "Assign Macro". Select the routine `ShowHideZeros`.
[1]: https://i.stack.imgur.com/QfVWc.png
</details>
# 答案2
**得分**: 2
使用表单复选框切换匹配值
-
![在此输入图片描述][1]
- 假设您的数据是从`A1`开始的表格，带有一行标题。
```vb
Option Explicit
Sub ToggleNone()
Const WorksheetName As String = "Sheet1"
Const CheckBoxName As String = "Check Box 1"
Const CellString As String = "None"
Const CellNumber As Double = 0
Dim wb As Workbook: Set wb = ThisWorkbook ' 包含此代码的工作簿
Dim ws As Worksheet: Set ws = wb.Sheets(WorksheetName)
Dim rg As Range
With ws.Range("A1").CurrentRegion
Set rg = .Resize(.Rows.Count - 1).Offset(1)
End With
Dim chkValue As Long: chkValue = ws.CheckBoxes(CheckBoxName).Value
Dim OldValue, NewValue, VarValue As Long
If chkValue = 1 Then ' 已选中（1）
OldValue = CellNumber
NewValue = CellString
VarValue = vbDouble
Else ' 未选中（-4146）
OldValue = CellString
NewValue = CellNumber
VarValue = vbString
End If
Dim urg As Range, cell As Range, CurrentValue
' 合并
For Each cell In rg.Cells
CurrentValue = cell.Value
If VarType(CurrentValue) = VarValue Then
If CurrentValue = OldValue Then
If urg Is Nothing Then
Set urg = cell
Else
Set urg = Union(urg, cell)
End If
End If
End If
Next cell
' 替换
If Not urg Is Nothing Then
Dim arg As Range
For Each arg In urg.Areas
arg.Value = NewValue
Next arg
End If
End Sub
英文:
Toggle Matching Values Using a Forms Check Box
- It is assumed that your data is a table starting in
A1with one row of headers.
<!-- language: lang-vb -->
Option Explicit
Sub ToggleNone()
Const WorksheetName As String = "Sheet1"
Const CheckBoxName As String = "Check Box 1"
Const CellString As String = "None"
Const CellNumber As Double = 0
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Sheets(WorksheetName)
Dim rg As Range
With ws.Range("A1").CurrentRegion
Set rg = .Resize(.Rows.Count - 1).Offset(1)
End With
Dim chkValue As Long: chkValue = ws.CheckBoxes(CheckBoxName).Value
Dim OldValue, NewValue, VarValue As Long
If chkValue = 1 Then ' Checked (1)
OldValue = CellNumber
NewValue = CellString
VarValue = vbDouble
Else ' Unchecked (-4146)
OldValue = CellString
NewValue = CellNumber
VarValue = vbString
End If
Dim urg As Range, cell As Range, CurrentValue
' Combine
For Each cell In rg.Cells
CurrentValue = cell.Value
If VarType(CurrentValue) = VarValue Then
If CurrentValue = OldValue Then
If urg Is Nothing Then
Set urg = cell
Else
Set urg = Union(urg, cell)
End If
End If
End If
Next cell
' Replace
If Not urg Is Nothing Then
Dim arg As Range
For Each arg In urg.Areas
arg.Value = NewValue
Next arg
End If
End Sub
答案3
得分: 2
"I have a table of calculations which all already have formulas in them". 我有一个包含已经有公式的计算表格。
英文:
"I have a table of calculations which all already have formulas in them". I think this is the key. The formulas are there and have to stay there. So we can set the formating:
Private Sub CheckBox1_Click() 'ActiveX checkBox
Call toggleNone
With CheckBox1
If .value Then
.Caption = "Zeros as 0"
Else
.Caption = "Zeros as None"
End If
End With
End Sub
Sub toggleNone()
Dim t As ListObject, r As Range, setNone As Boolean
setNone = Me.CheckBox1.value
Set t = Me.ListObjects("CALCTABLE")
For Each r In t.DataBodyRange
If r.Value2 = 0 Then
If setNone Then
'IN NEXT LINE INSTEAD OF ; USE THE LOCAL LIST SEPARATOR (maybe , )
r.NumberFormat = " ; ; ""None"" ; -"
Else
r.NumberFormat = "General"
End If
Else
r.NumberFormat = "General"
End If
Next
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论