0

I have a sheet including code that would trigger some VBA code depending of which cells are selected. (a cell has the function of a button). Since the user can select ranges including several cells the way to limit that problem (not triggering the vba code when the cell is included in the selected range, but IT IS NOT THE ONLY CELL) is using targe.count This work fine indeed when the user select several cells including the one which fires the vba BUT i am wondering why it gives error when the user selects all with CTRL-A

this is the code behind the sheet

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
     run some code

If I select the whole sheet with contrl-A a error ocurr.

runtime error 6 Overflow

More strange even. the sheet contains a table (listobject) If I do ctrl-A within the table the whole table is selected and no error occurs. If I do ctrl-A outside the table the whole sheet is selected and an error occurs.

Some explanation for this behaiviour?

JFerro
  • 3,203
  • 7
  • 35
  • 88
  • 3
    Most likely because using count errors out above a certain numbers of selected cells (Count is a Long data type). Use CountLarge like explained over here: https://stackoverflow.com/questions/25756467/target-count-causing-an-overflow-error – JvdV Dec 12 '18 at 12:40

2 Answers2

2

Replace your Target.Count with Target.CountLarge property.

Ref: http://msdn.microsoft.com/en-us/library/office/ff196838(v=office.15).aspx

Alexey
  • 1,521
  • 1
  • 13
  • 24
1

tldr; You're trying to retrieve too large a number into the Range.Count property.

The Range.Count property was written to return a long integer resolved to the total number of individual cells within the range object.

Range.Count property (Excel)
Returns a Long value that represents the number of objects in the collection.

You're receiving Runtime error 6: Overflow because the total number of individual cells on an XLSX worksheet is 1,048,576 rows × 16,384 (A through XFD) columns. This resolves to 17,179,869,184 individual cells which exceeds the maximum alllowed value of a signed long integer¹.

¹ The maximum value that a signed long integer can retain is 2,147,483,647 or &H7FFF,FFFF16.