英文:
My Toggle Button to hide rows causes my VBA code cells to #VALUE error
问题
我刚刚发现了切换按钮,并输入了一个数字来隐藏和显示表格中不使用的各个部分。
在每个隐藏的部分中,我使用了一个VBA代码来求粗体字的和斜体字的总和,在页面底部有一个等式来将这些单元格相加,以给我整个表格的总和。
当我隐藏一个部分时,用于求粗体/斜体字的VBA代码会出现#VALUE错误,底部的相加单元格也会出现相同的错误。如果我点击错误的求粗体/斜体字单元格的等式并按回车键,它会恢复正常,但显然如果单元格被隐藏,我无法这样做。
手动隐藏单元格不会引起相同的问题,只有使用按钮才会出现。有什么办法可以阻止这种情况发生吗?
我知道我可以更改我的相加单元格,以使用每个部分的VBA代码,但如果有一个方法来阻止错误发生,那将更容易。
供参考,这是我的按钮代码格式:
Private Sub Hide1_Click()
Dim xAddress As String
xAddress = "7:14"
If Hide1.Value Then
Application.ActiveSheet.Rows(xAddress).Hidden = False
Else
Application.ActiveSheet.Rows(xAddress).Hidden = True
End If
End Sub
这是我的求粗体字和斜体字的格式:
Function SumBold(WorkRng As Range)
'更新20220516
Dim Rng As Range
Dim xSum As Double
For Each Rng In WorkRng
If Rng.Font.Bold Then
xSum = xSum + Rng.Value
End If
Next
SumBold = xSum
End Function
谢谢!
英文:
I've just discovered Toggle Buttons and have input a number to hide and unhide various sections of my sheet when they're not in use.
Within each hidden section I'm using a VBA code to sum bold and sum italics, and at the bottom of the page have an equation to add those cells together to give me the total for the whole sheet.
When I hide a section the VBA code to sum bold/italic gives a #VALUE error, also giving the additions cell at the bottom the same. If I click on the errored sum bold/italic cell's equation and press enter it goes back to normal, but obviously if the cell is hidden I'm unable to do this.
Manually hiding the cells doesn't cause the same issue, only using the button. Is there something I can add in to stop this from happening?
I know I can change my additions cell to use the VBA code for each section, but if there's a loop around to just fix the error from coming up that would be easier.
For reference this is my button code format:
Private Sub Hide1_Click()
Dim xAddress As String
xAddress = "7:14"
If Hide1.Value Then
Application.ActiveSheet.Rows(xAddress).Hidden = False
Else
Application.ActiveSheet.Rows(xAddress).Hidden = True
End If
End Sub
And this is my sum bold format
Function SumBold(WorkRng As Range)
'Update 20220516
Dim Rng As Range
Dim xSum As Double
For Each Rng In WorkRng
If Rng.Font.Bold Then
xSum = xSum + Rng.Value
End If
Next
SumBold = xSum
End Function
Thank you!
答案1
得分: 1
我与ChatGPT进行了一些来回讨论,并找到了解决方案。
范围仅包括数字单元格,因此这不是问题所在,并且在手动隐藏单元格时并没有问题,所以切换按钮是问题所在。
对切换按钮的这一修正解决了问题。
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-html -->
Private Sub Hide1_Click()
Dim xAddress As String
xAddress = "7:14"
If Hide1.Value Then
Application.ActiveSheet.Rows(xAddress).Hidden = False
Else
Application.ActiveSheet.Rows(xAddress).Hidden = True
End If
Application.ScreenUpdating = False ' 禁用屏幕更新
' 重新计算公式
Application.CalculateFullRebuild
Application.ScreenUpdating = True ' 启用屏幕更新
End Sub
<!-- end snippet -->
英文:
I did some back and forth with ChatGPT and found a solution to the problem.
The range was only numeric cells so that wasn't the issue, and it didn't pose a problem when manually hiding cells so the toggle button was the issue.
This amendment to the toggle buttons fixed it.
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-html -->
Private Sub Hide1_Click()
Dim xAddress As String
xAddress = "7:14"
If Hide1.Value Then
Application.ActiveSheet.Rows(xAddress).Hidden = False
Else
Application.ActiveSheet.Rows(xAddress).Hidden = True
End If
Application.ScreenUpdating = False ' Disable screen updating
' Recalculate formulas
Application.CalculateFullRebuild
Application.ScreenUpdating = True ' Enable screen updating
End Sub
<!-- end snippet -->
答案2
得分: -1
问题出在一些数值上,它们不是数字。下面的已更正的函数将在即时窗口中显示非数值的值。
Option Explicit
Function SumBold(WorkRng As Range) As Double
' 更新 20220516
Dim Rng As Range
Dim xSum As Double
For Each Rng In WorkRng
If Rng.Font.Bold Then
If IsNumeric(Rng.Value2) Then
xSum = xSum + Rng.value
Else
Debug.Print "Cell " & Rng.Address(0, 0) & " is not numeric!"
End If
End If
Next
SumBold = xSum
End Function
英文:
The problem lies in some values, which are not numeric. The corrected Function below will reveal the non-numeric values to you in the Immediate window
Option Explicit
Function SumBold(WorkRng As Range) As Double
' Update 20220516
Dim Rng As Range
Dim xSum As Double
For Each Rng In WorkRng
If Rng.Font.Bold Then
If IsNumeric(Rng.Value2) Then
xSum = xSum + Rng.value
Else
Debug.Print "Cell " & Rng.Address(0, 0) & " is not numeric!"
End If
End If
Next
SumBold = xSum
End Function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论