Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Check a range is not the entire sheet?

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)?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...