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

On 9 Dec, 17:45, Dallman Ross <dman@localhost. wrote:
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

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)


"Dallman Ross" <dman@localhost. wrote in message
...
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

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

"Dallman Ross" <dman@localhost. skrev i en meddelelse
...
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

Thanks, James.

dman
-----------------------------------
In
,
spake thusly:

On 9 Dec, 17:45, Dallman Ross <dman@localhost. wrote:
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

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

dman

----------------------
In , Chip
Pearson spake thusly:

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