CODEWITHSUNDEEP
c#visual-studioexcelvstovba
Tushar Chhabhaiya
Tushar Chhabhaiya

Reputation: 700

Check whether whole sheet is selected or not?

How to check whether user has selected whole excel worksheet or not?. I have tried to use following.

selection.cells.count

but it gives a out of present range exception.

is there any way to do the same?

Upvotes: 2

Views: 1400

Answers (4)

seadoggie01
seadoggie01

Reputation: 530

I realize this is an old discussion, but I came here from a Google search. For anyone else who finds this, an easier way would probably be just to use ".address". For example

If Selection.address = Cells.address then Msgbox "You selected an entire sheet!"

Upvotes: 1

N_A
N_A

Reputation: 19897

As noted by @TimWilliams in the comments, if you select the whole worksheet, the count will overflow an int (i.e. the Count property) causing the "out of present range" exception. To get around this use the CountLarge property. In C#, the CountLarge property is an object. To use it, cast it to a long.

long cellCount = (long)selectedRange.Cells.CountLarge;

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27488

I'm going to plagiarize brettdj's code and create a version to test if the entire sheet is selected. While I'm intrigued by his use of a string to contain TRUE, FALSE and failure values, I'd just go with a Boolean, so people like me won't have to think too hard.

Sub CheckSelection()
Dim IsMatch As Boolean
Dim ErrNum As Long

With ActiveSheet
    On Error Resume Next
    IsMatch = (.Range(.Cells(1), .Cells(.Rows.Count, Columns.Count)).Address = Selection.Address)
    ErrNum = Err.Number
    On Error GoTo 0
    If ErrNum <> 0 Then
        MsgBox "test failed: have you selected part of the sheet", vbCritical
    Else
        MsgBox IsMatch = True
    End If
End With
End Sub

Upvotes: 1

brettdj
brettdj

Reputation: 55702

If you wanted to test whether the UsedRange was indentical to the Selection in then

  1. You need to ensure the UsedRange has been updated
  2. Catering for errors if there is no range selection is also needed

something like ths gives either

  • a warning message for an error (no selection)
  • True for the same Address strings
  • False for different Address strings

code

Sub TestData()
Dim strTest As String
'force usedrange to update
ActiveSheet.UsedRange
On Error Resume Next
strTest = (ActiveSheet.UsedRange.Address = Selection.Address)
On Error GoTo 0
If Len(strTest) = 0 Then
MsgBox "test failed: have you selected part of the sheet", vbCritical
Else
MsgBox strTest
End If
End Sub

Upvotes: 3

Related Questions