• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

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

I know that MZ Tools does this.  The VBProject object can be gotten using:


How do I collapse it in the Project Explorer?
  • 2
1 Solution
jnash67Author Commented:
I found the following advice from Stephen Bullen (http://www.archivum.info/microsoft.public.excel.programming/2005-05/msg00318.html)

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?
Rory ArchibaldCommented:
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

jnash67Author Commented:
You rock

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now