Easy way to test range as single cell

D

Dallman Ross

I'm trying to come up with an easy way in VBA to know if
the selected range is one single cell only -- and from a
restricted range of cells. I suspect this is trivially
easy to do, but the how of it escapes me at this moment.
Ideas gladly solicited.

dman
 
J

james.billy

I'm trying to come up with an easy way in VBA to know if
the selected range is one single cell only -- and from a
restricted range of cells. I suspect this is trivially
easy to do, but the how of it escapes me at this moment.
Ideas gladly solicited.

dman

If the user has already selected the range then it would be:

if Selection.Cells.Count > 1 then
Msgbox "More than one cell is selected"
end if

James
 
C

Chip Pearson

You might try

' ensure a Range is selected
If TypeOf Selection Is Excel.Range Then
If Selection.Cells.Count = 1 Then
' one cell selected
If Not Application.Intersect(Selection, Range("A1:A10")) Is Nothing
Then
' range is within A1:A10
' do something
Else
' not in A1:A10
End If
Else
' more than one cell selected
End If
Else
' selection is not a range (perhaps a Shape or something)
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
P

Per Jessen

Hi

Try this:

Option Explicit

Dim isect As Variant
Dim rRange As Range

Sub test()
Set rRange = Range("A1:C5")
If Selection.Rows.Count * Selection.Columns.Count > 1 Then MsgBox "Multiple
cells selected!"

Set isect = Application.Intersect(Selection, rRange)
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If

End Sub
 
D

Dallman Ross

Thank you, Chip, this looks very good indeed. Per's similar
approach was also interesting to see. Thanks to you both!

dman
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top