How can I programmatically collapse a project in Excel VBA VBE IDE

Posted on 2009-02-24
Last Modified: 2012-05-06
I know that MZ Tools does this.  The VBProject object can be gotten using:


How do I collapse it in the Project Explorer?
Question by:jnash67

    Author Comment

    I found the following advice from Stephen Bullen (

    The Microsoft Visual Basic for Applications Extensibility library
    provides the object model for the VBIDE, allowing us to add menu items
    to the Project Explorer popup. It doesn't, however, provide the
    granularity of which nodes in the treeview are expanded. So, I guess
    that MZTools uses the object model to get the window handle and Windows
    API calls to find the handle of the TreeView and collapse the nodes.

    How can I do this?
    LVL 85

    Accepted Solution

    If you want to collapse ALL projects (as MZTools does) then something like this should work:

    Private Const TVE_COLLAPSE = &H1
    Private Const TVE_COLLAPSERESET = &H8000
    Private Const TVE_EXPAND = &H2
    Private Const TVE_EXPANDPARTIAL = &H4000
    Private Const TVE_TOGGLE = &H3
    Private Const TV_FIRST = &H1100
    Private Const TVM_EXPAND = (TV_FIRST + 2)
    Private Const TVM_GETNEXTITEM = (TV_FIRST + 10)
    Private Const TVGN_ROOT = &H0
    Private Const TVGN_NEXTVISIBLE = &H6
    Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
    (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    Sub CollapseProjects()
       Dim hWndVBE As Long, hWndPE As Long, hWndTvw As Long, hNode As Long, varReturn
       hWndVBE = FindWindowEx(0, 0, "wndclass_desked_gsk", Application.VBE.MainWindow.Caption)
       hWndPE = FindWindowEx(hWndVBE, 0, "PROJECT", vbNullString)
       hWndTvw = FindWindowEx(hWndPE, 0, "SysTreeView32", vbNullString)
       hNode = SendMessage(hWndTvw, TVM_GETNEXTITEM, TVGN_ROOT, 0&)
       Do While hNode <> 0
          varReturn = SendMessage(hWndTvw, TVM_EXPAND, TVE_COLLAPSE, hNode)
          hNode = SendMessage(hWndTvw, TVM_GETNEXTITEM, TVGN_NEXTVISIBLE, hNode)
    End Sub

    Open in new window


    Author Closing Comment

    You rock

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now