active menu bar

Question

i use excel 2007, generaly when we open any excel file the home menu will be ativated i need a macro which will activate Add-Ins, as i open on of excel fileas.

Answer 1

This works with Word.  I don't know if it will work with Excel.

Create a module in the document named Accessibility and paste in this code:

Option Explicit

Public Const CHILDID_SELF As Long = &H0&

Public Const STATE_SYSTEM_UNAVAILABLE As Long = &H1&

Public Const STATE_SYSTEM_INVISIBLE As Long = &H8000&

Public Const STATE_SYSTEM_SELECTED As Long = &H2&

Public Enum RoleNumber

 ROLE_SYSTEM_CLIENT = &HA&

 ROLE_SYSTEM_PANE = &H10&

 ROLE_SYSTEM_GROUPING = &H14&

 ROLE_SYSTEM_TOOLBAR = &H16&

 ROLE_SYSTEM_PAGETAB = &H25&

 ROLE_SYSTEM_PROPERTYPAGE = &H26&

 ROLE_SYSTEM_GRAPHIC = &H28&

 ROLE_SYSTEM_STATICTEXT = &H29&

 ROLE_SYSTEM_Text = &H2A&

 ROLE_SYSTEM_PAGETABLIST = &H3C&

End Enum

Private Enum NavigationDirection

NAVDIR_FIRSTCHILD = &H7&

End Enum

Private Declare Function AccessibleChildren Lib "oleacc.dll" _

       (ByVal paccContainer As Object, ByVal iChildStart As Long, _

       ByVal cChildren As Long, rgvarChildren As Variant, pcObtained As Long) As Long

Public Function GetAccessible(Element As IAccessible, RoleWanted As RoleNumber, _

               NameWanted As String, Optional GetClient As Boolean) As IAccessible

Dim ChildrenArray() As Variant

Dim Child As IAccessible

Dim ndxChild As Long

Dim ReturnElement As IAccessible

If Element.accRole(CHILDID_SELF) = RoleWanted And Element.accName(CHILDID_SELF) = NameWanted Then

 Set ReturnElement = Element

Else ' not found yet

 ChildrenArray = GetChildren(Element)

 If (Not ChildrenArray) <> True Then

   For ndxChild = LBound(ChildrenArray) To UBound(ChildrenArray)

     If TypeOf ChildrenArray(ndxChild) Is IAccessible Then

       Set Child = ChildrenArray(ndxChild)

       Set ReturnElement = GetAccessible(Child, RoleWanted, NameWanted)

       If Not ReturnElement Is Nothing Then Exit For

      End If ' Child is IAccessible

    Next ndxChild

  End If ' there are children

End If ' still looking

If GetClient Then

 Set ReturnElement = ReturnElement.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)

End If

Set GetAccessible = ReturnElement

End Function

Private Function GetChildren(Element As IAccessible) As Variant()

Const FirstChild As Long = 0&

Dim NumChildren As Long

Dim NumReturned As Long

Dim ChildrenArray() As Variant

NumChildren = Element.accChildCount

If NumChildren > 0 Then

 ReDim ChildrenArray(NumChildren - 1)

 AccessibleChildren Element, FirstChild, NumChildren, ChildrenArray(0), NumReturned

End If

GetChildren = ChildrenArray

End Function

In a new module paste in this code:

Sub AutoOpen()

Dim RibbonTab As IAccessible

Set RibbonTab = GetAccessible(CommandBars("Ribbon"), ROLE_SYSTEM_PAGETAB, "Add-Ins")

If Not RibbonTab Is Nothing Then

 If ((RibbonTab.accState(CHILDID_SELF) And (STATE_SYSTEM_UNAVAILABLE Or _

        STATE_SYSTEM_INVISIBLE)) = 0) Then

   RibbonTab.accDoDefaultAction CHILDID_SELF

 Else

   MsgBox "Designated Tab is unavailable"

 End If

End If

End Sub

Any credit for the code goes to Tony Jollans

 "ranjith kurian" wrote in message news:*** Email address is removed for privacy ***...

i use excel 2007, generaly when we open any excel file the home menu will be ativated i need a macro which will activate Add-Ins, as i open on of excel fileas.

---

Greg Maxey --- Visit my website at: http://gregmaxey.mvps.org/word\_tips.htm

Answer 2

Excellent and thanks a lot