我的切换按钮隐藏行导致我的VBA代码单元格出现#VALUE错误。

huangapple go评论72阅读模式
英文:

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 = &quot;7:14&quot;
    
    If Hide1.Value Then
        Application.ActiveSheet.Rows(xAddress).Hidden = False
    Else
        Application.ActiveSheet.Rows(xAddress).Hidden = True
    End If
    
    Application.ScreenUpdating = False &#39; Disable screen updating
    
    &#39; Recalculate formulas
    Application.CalculateFullRebuild
    Application.ScreenUpdating = True &#39; 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
   &#39; 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 &quot;Cell &quot; &amp; Rng.Address(0, 0) &amp; &quot; is not numeric!&quot;
         End If
      End If
   Next
   SumBold = xSum
End Function

huangapple
  • 本文由 发表于 2023年7月3日 19:07:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/76604165.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定