Identify the active item in a worksheet

Question

Given a worksheet, I understand that selected items and the active item are not the same thing. It seems that only one item of all the selected items can be active. I would like to write a piece of code to identify the active item automatically.

Selection always refers to selected items, however it seems that there is no expression like that to fetch the active item. The types of the active item can be various. It may be a cell, in this case ActiveCell is used to refer to it. It also can be a Chart, then ActiveChart should be used.

Do I have to check all the ActiveXXXX to get the active item? In this case, is there an exhaustive list for this?

Otherwise, does anyone know any other workaround?

There are a lot of reasons why you should avoid using `Selection` and `Activate` methods... you probably already know this, but just in case you do not: http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select — David Zemens, May 17 '14 at 14:06
Well, I am not using them... I just want to parse/identify them... — SoftTimur, May 17 '14 at 14:09
@DavidZemens You're right, there are lots of reasons to not use Selection & Activate, but what if I want to kick the focus to a certain cell or range when a certain action happens? There are legit reasons to use `.select` and `.selection`. — RubberDuck, May 17 '14 at 17:42

Cor_Blimey · Accepted Answer · 2014-05-17T14:31:57.313

Something like the below should cover most situations. I only have come across these from experience so there could certainly be ones I have missed. However, it is important you understand what the Selection is - as intepreting what is the 'Active' item is pretty subjective.

In terms of actual "Active" items there is only ActiveCell and ActiveChart that relate to Selection. ActiveWindow/Dialog/Workbook/Sheet etc are never in Selection. The others below infer what the 'Active' item should be and is dependent on your taste.
For example, a selection of multiple shapes (or controls etc) is contained within DrawingObjects.ShapeRange. There is no active item so it is up to you to decide how to treat that. The below returns the first item (which I think is the item first selected by the user).
Similarly, a group of shapes is a single GroupObject -> whether you want to look inside the group is again a personal decision. The below returns a GroupObject as the active item as it is technically a single object.
And etc...;)

Function:

Function GetActive()
    If typeof Selection Is Range Then 'ActiveCell can remain not nothing even if selection changes
        Set GetActive = ActiveCell
    ElseIf Not ActiveChart Is Nothing Then 'we test for ActiveChart instead of using typeof/typename as Selection could be e.g. ChartArea/PlotArea etc
        Set GetActive = ActiveChart
    ElseIf TypeName(Selection) = "DrawingObjects" Then
        'Multiple objects selected (e.g. embedded OLE objects / shapes / controls)
        'Container is DrawingObjects, whose ShapeRange contains the items
        Set GetActive = Selection.ShapeRange.Item(1)
    Else
        'Single object selected (or else a special case not covered by DrawingObjects
        Set GetActive = Selection
    End If
End Function

@DavidZemens thanks for formatting. I don't know what happened - it went very weird when I added the bullets — Cor_Blimey, May 17 '14 at 14:14
I think it was the nested bullets and for some reason it wasn't recongnizing the line break before your code. Cheers. BTW, same approach I would take with this question!! — David Zemens, May 17 '14 at 14:15

Identify the active item in a worksheet

1 Answers1