Selection and Areas

J

Jac Tremblay

Hi everyone
I'm trying to find a way to loop on every area in a multiple selection and the help provided is rather concise
' ****
Sub NoMultiAreaSelection(
NumberOfSelectedAreas = Selection.Areas.Coun
If NumberOfSelectedAreas > 1 The
MsgBox "You cannot carry out this command on multi-area selections
End I
End Su
' ****
I would like to get some information like the area's address for each area in the current selection. Something like this
****
Sub MultiAreaSelection(
Dim NumberOfSelectedAreas As Integer, intI As Intege
NumberOfSelectedAreas = Selection.Areas.Coun
For intI = 1 To NumberOfSelectedArea
'How can I get each area's address, for example
Next int
End Su
' ****
Another question about Selection
Why, in VBA, when I type "Selection" followed by a dot, I do not get any option suggested. The same as when an object is declared as Object, a too general object

Thanks to anyone who can help.
 
C

Chip Pearson

Jac,

Try something like

Dim Ar As Range
For Each Ar In Selection.Areas
Debug.Print Ar.Address
Next Ar

Why, in VBA, when I type "Selection" followed by a dot, I do
Click to expand...
not get any option suggested.

The reason is that Selection is a generic Object, not a specific
type of object. It usually refers to a range of cells, but may
refer to a ChartObject, a Shape, or whatever happens to be
selected at run time. When you are writing code, the editor has
no idea what type of object might be selected, so no Intellisense
is provided.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
Hi everyone,
I'm trying to find a way to loop on every area in a multiple
Click to expand...
selection and the help provided is rather concise:
' *****
Sub NoMultiAreaSelection()
NumberOfSelectedAreas = Selection.Areas.Count
If NumberOfSelectedAreas > 1 Then
MsgBox "You cannot carry out this command on multi-area selections"
End If
End Sub
' *****
I would like to get some information like the area's address
Click to expand...
for each area in the current selection. Something like this:
*****
Sub MultiAreaSelection()
Dim NumberOfSelectedAreas As Integer, intI As Integer
NumberOfSelectedAreas = Selection.Areas.Count
For intI = 1 To NumberOfSelectedAreas
'How can I get each area's address, for example?
Next intI
End Sub
' *****
Another question about Selection:
Why, in VBA, when I type "Selection" followed by a dot, I do
Click to expand...
not get any option suggested. The same as when an object is
declared as Object, a too general object?
 
J

Jac Tremblay

Hi Chip
Thank you very much for this precious help. It works fine for me
For the Selection object, is there a way to restrict the range of objects so that Intellisense is provided? And also, is there a way to check the properties or methods availaible for a specific case of the Selection object, like the selection of worksheet cells
Thanks again for your help.
 
T

Tom Ogilvy

No Jac. Intellisense will not be provided for selection.

You can substitute a specific object and build your code, then go back and
replace it with selection.

or if you know what the selection will be (and you more than likely will),
define a variable to that type

Dim rng as Range


set rng = Selection

' now use rng and you will get the intellisense.

If you are recording your code, then you need to clean it up anyway.

--
Regards,
Tom Ogilvy


Jac Tremblay said:
Hi Chip,
Thank you very much for this precious help. It works fine for me.
For the Selection object, is there a way to restrict the range of objects
Click to expand...
so that Intellisense is provided? And also, is there a way to check the
properties or methods availaible for a specific case of the Selection
object, like the selection of worksheet cells?
 
C

chris

You can do something like this..
Dim MyRange as Range '>>Do a complie after you type the Dim statemen
Set MyRange = Selection
MyRange.?
----- Jac Tremblay wrote: ----

Hi Chip
Thank you very much for this precious help. It works fine for me
For the Selection object, is there a way to restrict the range of objects so that Intellisense is provided? And also, is there a way to check the properties or methods availaible for a specific case of the Selection object, like the selection of worksheet cells
Thanks again for your help.
 
T

Tom Ogilvy

'>>Do a complie after you type the Dim statement

?? why do a compile ?? That wouldn't be required to the best of my
knowledge.

--
Regards,
Tom Ogilvy

chris said:
Dim MyRange as Range '>>Do a complie after you type the Dim statement
Set MyRange = Selection
MyRange.??

----- Jac Tremblay wrote: -----

Hi Chip,
Thank you very much for this precious help. It works fine for me.
For the Selection object, is there a way to restrict the range of
Click to expand...
objects so that Intellisense is provided? And also, is there a way to check
the properties or methods availaible for a specific case of the Selection
object, like the selection of worksheet cells?
 
Top