检查范围不是整张表？ [英] Check a range is not the entire sheet?

问题描述

我有这个功能，试图检测特定的单元格值何时改变。问题是，如果用户选择整个电子表格并按删除，我的支票中会出现一个溢出，该范围只是一个单元格：

 code> Public Sub Worksheet_Change（ByVal Target As Range）
 
'如果range =整个电子表格
可能会发生溢出如果不是IsError（Int（Target.Cells.Count））则
如果Target.Cells.Count = 1然后
如果Target.Cells.Row = 4和Target.Cells.Column = 1然后
表单（X）。单元格（5，1 ）.Calculate 
 End If 
 End If 
 End If 
 End Sub 
  

有没有更优雅的方式，我可以得到这个代码只有运行，当一个特定的单元格值改变？ （没有溢出，我清除整个工作表时的问题等）？

解决方案

我假设你在Excel 2007+因为这些版本中行和列的数量急剧增加。你可能有更好的运气检查，以确保行和列数= 1，因为这些最大值将比两者的产品（即，细胞计数）低得多：

 如果Target.Rows.Count = 1和Target.Columns.Count = 1然后
  

I have this function which is trying to detect when a particular cell value changes. The problem is, if the user selects the whole spreadsheet and presses delete, I get an overflow in my check that the range is only a single cell:

Public Sub Worksheet_Change(ByVal Target As Range)

    'Overflow can occur here if range = whole spreadsheet
    If Not IsError(Int(Target.Cells.Count)) Then 
        If Target.Cells.Count = 1 Then
            If Target.Cells.Row = 4 And Target.Cells.Column = 1 Then
                Sheets("X").Cells(5, 1).Calculate
            End If
        End If
    End If
End Sub

Is there a more elegant way that I can get this code to only run, when a single particular cell value is changed? (with no overflows, problems when I clear the whole worksheet etc)?

解决方案

I'm assuming you are on Excel 2007+ since the number of rows and columns increased dramatically in those versions. You might have better luck checking to make sure both the row and column count = 1, since those maxes will be much lower than the product of the two (ie, the cell count):

If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then

这篇关于检查范围不是整张表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！