Getting around Overflow (Error 6)

[Shanthan]

Hi,

I was getting an overflow error at Line 2:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sheets("User Names").Range("D9").Interior.ColorIndex = xlNone And Selection.Cells.Count = 1 Then
        CurrentCellValue = Target.Value
    End If
End Sub

So, I changed my code to look like this, but I am still getting an overflow error:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim SelectedCellCount As Long
    SelectedCellCount = Selection.Cells.Count
    
    If Sheets("User Names").Range("D9").Interior.ColorIndex = xlNone And SelectedCellCount = 1 Then
        CurrentCellValue = Target.Value
    End If
End Sub

Does anyone know how to get around this?

Thanks!

 

[Firefly2012]

You are using xl2007+ I presume? If you select the entire worksheet cells then the number of cells exceeds the capacity of a Long (which Count returns) so you need to use a different property (CountLarge):

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.CountLarge<>1 Then Exit Sub
    If Sheets("User Names").Range("D9").Interior.ColorIndex = xlNone Then
        CurrentCellValue = Target.Value
    End If
End Sub

 

[Shanthan]

Thanks so much for the quick reply. I am using Excel 2010, but I need this to work on Excel 2003 as well. Does CountLarge work on Excel 2003?

 

[Andrew Poulsom]

How can you expect it to work in Excel 2003? You won't be able to select that row.

 

[Shanthan]

Hi Andrew,

I am not quite sure what you mean. I was getting the overflow error when I click on the Select All button located left to column A on the column header. Firefly2012's solution works, but I read that CountLarge expression only works in Excel 2007 and up. I was wondering if there is a solution that works on Excel 2003 as well.

 

[Firefly2012]

Try this instead (for xl2003+ compatibilty):

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If InStr(Target.Address, ":") > 0 Then Exit Sub

    If Sheets("User Names").Range("D9").Interior.ColorIndex = xlNone Then
        CurrentCellValue = Target.Value
    End If
End Sub

 

[Shanthan]

Thank you so much, Firefly2012. It works great! If you get a chance, can you please take a look at this link: Resizing comment box I haven't been able to solve this issue.

PS: I wish they hadn't cancelled that show.