CODEWITHSUNDEEP
vbaexcel
SoftTimur
SoftTimur

Reputation: 5510

Identify the active item in a worksheet

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?

Upvotes: 3

Views: 299

Answers (1)

Cor_Blimey
Cor_Blimey

Reputation: 3310

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

Upvotes: 3

Related Questions