Userforms

When writing applications for the Visual Basic Editor you will obviously want any userforms to be displayed in the VBE editor window and not in the application window.
The easiest way to achieve this is to hide the application window, display the userform and then display the application window again.


This finds the workbook that corresponds to the active VB project.


Function ActiveProjectBook() As Workbook 
Dim objVBP As VBIDE.VBProject
Dim objVBC As VBIDE.VBComponent
Dim sName As String

'If an error occurs (for example the project may be locked), assume we can't find the workbook.
Set objVBP = Application.VBE.ActiveVBProject

If objVBP.Protection = vbext_pp_locked Then
'if the project has been locked it must have been saved so we can read its filename
   sName = objVBP.FileName

''remove the path
   If InStrRev(sName,"\") <> 0 Then
      sName = Mid(sName, InStrRev(sName,"\") + 1)

''if it is the name of a workbook then we have found it (it could be a name of a VBE project)
      If IsWorkbook(sName) = True Then
        Set ActiveProjectBook = Workbooks(sName)
         Exit Function
      End If

    Else
''loop through all the VB Components in the project
''The 'ThisWorkbook' component exposes the name of the workbook, but the component may not be called "ThisWorkbook"

      For Each objVBC In objVBP.VBComponents
''only need to check Document types (i.e. Excel objects)

            If objVBC.Type = cbext_ct_Document Then
''get the name of the component
            sName = objVBC.Properties("Name")

'is it the name of an open workbook ?
             If IsWorkbook(sName) = True Then
 
                If Workbooks(sName).VBProject Is objVBP Then

'we have found it
                  Set ActiveProjectBook = Workbooks(sName)
                  Exit Function
                End If
            End If
         End If
   Next
End If

Function IsWorkbook(sWbkName As String) As Boolean
Dim sName As String
   On Error GoTo AnError
   sName = Workbooks(sWbkName).Name
   IsWorkbook = True
   Exit Function

AnError:
   IsWorkbook = False
End Function

The Close routine presents us with a new challenge
We are adding a Close Workbook menu item to the popup menu for the Project Explorer and hence need to determine which VBProject was clicked.
The ActiveVBProject property of the VBE provides this, but a way is needed to get from the VBProject object to the workbook containing it.


Can this be used to remove the spurious VB projects ???



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext