how can vba detect what is currently selected?
 

hi,

i have a userform which i use to create the formulae for dynamic ranges.
when it initializes, it bases itself on the currently selected cell, which
works fine. however, i noticed the other day that if some other object is
selected (in this case, a chart) the VBA returns an error.

how can i determine what sort of thing is selected when the form is
initialized? the user form uses a RefEdit control (I know, I know!), so i
wouldn't really want the form to close, as the user would have the chance to
select a range whilst the form is still open.

tia,

Tim

how can vba detect what is currently selected?
 

Roedd <<Tim Marsh wedi ysgrifennu:

hi,

i have a userform which i use to create the formulae for dynamic
ranges. when it initializes, it bases itself on the currently
selected cell, which works fine. however, i noticed the other day
that if some other object is selected (in this case, a chart) the VBA
returns an error.

how can i determine what sort of thing is selected when the form is
initialized? the user form uses a RefEdit control (I know, I know!),
so i wouldn't really want the form to close, as the user would have
the chance to select a range whilst the form is still open.


if typeof selection is Excel.Range then
' Load address into refedit
else
' Do whatever
end if

HTH

Rob

how can vba detect what is currently selected?
 

Thanks Rob - never used 'typeof' before, but it seems to do the trick
nicely!

cheers

tim

"Robert Bruce" <rob@analytical-dynamicsdotcodotukay wrote in message
...
Roedd <<Tim Marsh wedi ysgrifennu:

hi,

i have a userform which i use to create the formulae for dynamic
ranges. when it initializes, it bases itself on the currently
selected cell, which works fine. however, i noticed the other day
that if some other object is selected (in this case, a chart) the VBA
returns an error.

how can i determine what sort of thing is selected when the form is
initialized? the user form uses a RefEdit control (I know, I know!),
so i wouldn't really want the form to close, as the user would have
the chance to select a range whilst the form is still open.


if typeof selection is Excel.Range then
' Load address into refedit
else
' Do whatever
end if

HTH

Rob