• Code: VBE — Not to be outdone

    Author
    Topic
    #354193

    Ok, I realized my wimpy VBE “Open the global add-in project component” code was not going to cut it for long. So here’s a friendlier UI version. The attachment is provided for your convenience.

    You will need to modify the LastEdit function to indicate a file location conducive to your environment.

    Put these subs in a form named frmVBE:

    Option Explicit
    Option Base 1
    
    Private Sub UserForm_Initialize()
    Dim arrMods() As String
    Dim lTotal As Long
    Dim strActiveProj As String
    Dim i As Long
    Dim strWorkMod As String
    
    #Const mDeveloping = False
    #If mDeveloping Then
        Dim vbComp As VBComponent
        Dim vbComps As VBComponents
    #Else
        Dim vbComp As Object
        Dim vbComps As Object
    #End If
    
        Documents.Open ThisDocument.FullName
        Set vbComps = ThisDocument.VBProject.VBComponents
        strActiveProj = ThisDocument.VBProject.Name
        
        lTotal = vbComps.Count
        lblVBEForm.Caption = " " & CStr(lTotal) & " Components in this Project: " & strActiveProj
        
        ReDim arrMods(lTotal)
        i = 1
        strWorkMod = LastEdit()
        With Me
            .txtCurrentMod.Text = "MRU Component: " & strWorkMod
            For Each vbComp In vbComps
                arrMods(i) = vbComp.Name
                i = i + 1
            Next
            WordBasic.Sortarray (arrMods())
            .drpMods.List = arrMods()
            .drpMods.Text = strWorkMod
        End With
        
        Set vbComp = Nothing
        Set vbComps = Nothing
    End Sub
    Private Sub cmdOk_Click()
       Unload Me
    End Sub
    Private Sub cmdCancel_Click()
        End
    End Sub
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Dim strWorkMod As String
    
        Select Case CloseMode
        Case 0  'User clicked close from the control menu. Treat this as a CANCEL!
            Cancel = False  'Allow form to close. Read this as DO NOT CANCEL this close event. Bye, bye
    
        Case 1 'The UNLOAD code in the OK button is attempting to execute.
             strWorkMod = Me.drpMods
            If strWorkMod = "" Then
                MsgBox "You did not select a module.  Try again.", vbCritical + vbOKOnly, "Data Entry Error"
                Me.drpMods.SetFocus
                Cancel = True
            Else
    
            ThisDocument.VBProject.VBComponents(strWorkMod).Activate
            
            Call LastEdit(strWorkMod)
            
            Application.ShowVisualBasicEditor = True
            
            End If
        End Select
    
    End Sub
    
    Function LastEdit(Optional strLastEdit As String) As String
        If strLastEdit = "" Then 'Get from ini
            LastEdit = System.PrivateProfileString(vDrv & "VBADev.ini", "VBADev", "LastEdit")
        Else 'Put into ini and into var
            System.PrivateProfileString(vDrv & "VBADev.ini", "VBADev", "LastEdit") = strLastEdit
            LastEdit = strLastEdit
        End If
    End Function
    

    Put this sub in a module and attach it to a toolbar button:

    Sub OpenWorkingModForm()
        frmVBE.Show
    End Sub
    
    Viewing 3 reply threads
    Author
    Replies
    • #795424

      >Application.ShowVisualBasicEditor = True

      Kevin, I’ve been using this every day since March 2001, and this morning decided it was time to use it in Excel97, as well as Word97, but sadly
      (1) it doesn’t translate directly and
      (2) I can’t find anyhting like it in Excel.

      Any clues?

      • #795428

        What made you think that VBA would be consistent across Office applications? grin

        Try SendKeys “%{F11}”

        That should work in all Office apps…

        • #795440

          > consistent across Office applications?

          Yeah!
          Right!! hah hah

          I had this conversation Thursday night at a Trainers Meeting – how each VBA is different from the rest. I am of the conclusion that they are (I hate saying this) remarkably similar.

          When I started in VBA, as a beginner, they all look different in part because they have different objects (after 6 months of Word DOCUMENTS I tried Excel DOCUMENTS with little success….) and in part because there are un-friendly aspects (such as no Comment button for the Access VBE toolbar.

          But now (ahem!), I’m feeling a lot more comfortable exploring at a higher level. That’s why, this morning, I decided it was time to export Kevin’s excellent toolbar “Open VBE” from Word and drop it into each Office application.

          Do you remember a month or two back back, “Use ThisDocument rather than MacroContainer”; that’s a classic example. I grabbed at MacroContainer as the first straw that came in sight back then; I haven’t graduated, as i should, to a higher level by using the more acceptable ThisDocument.

        • #795441

          > consistent across Office applications?

          Yeah!
          Right!! hah hah

          I had this conversation Thursday night at a Trainers Meeting – how each VBA is different from the rest. I am of the conclusion that they are (I hate saying this) remarkably similar.

          When I started in VBA, as a beginner, they all look different in part because they have different objects (after 6 months of Word DOCUMENTS I tried Excel DOCUMENTS with little success….) and in part because there are un-friendly aspects (such as no Comment button for the Access VBE toolbar.

          But now (ahem!), I’m feeling a lot more comfortable exploring at a higher level. That’s why, this morning, I decided it was time to export Kevin’s excellent toolbar “Open VBE” from Word and drop it into each Office application.

          Do you remember a month or two back back, “Use ThisDocument rather than MacroContainer”; that’s a classic example. I grabbed at MacroContainer as the first straw that came in sight back then; I haven’t graduated, as i should, to a higher level by using the more acceptable ThisDocument.

          • #795446

            The principles are similar, Chris, but the devil is in the details. grin A high level of VBA experience in Word or Excel isn’t going to buy you mcuh but frustration in Access because of the radical difference in the object model. shrug If you don’t believe me, play with the concept of “document” in the three apps. evilgrin And as for 97 … rofl

            • #795462

              Charlotte, I can relate to your ambivalent feelings about Office 97. I was SO glad to move to Office 2000 – finally able to get rid of all the ugly WordBasic code I had to write for Word 97.

            • #795494

              >> all the ugly WordBasic code I had to write for Word 97.

              Huh? WordBasic was in Word 95 and earlier, Word 97 already had Visual Basic for Applications.

            • #795522

              Oh well. Guess I need to re-seat my RAM chips.

            • #795523

              Oh well. Guess I need to re-seat my RAM chips.

            • #795463

              Charlotte, I can relate to your ambivalent feelings about Office 97. I was SO glad to move to Office 2000 – finally able to get rid of all the ugly WordBasic code I had to write for Word 97.

          • #795447

            The principles are similar, Chris, but the devil is in the details. grin A high level of VBA experience in Word or Excel isn’t going to buy you mcuh but frustration in Access because of the radical difference in the object model. shrug If you don’t believe me, play with the concept of “document” in the three apps. evilgrin And as for 97 … rofl

      • #795429

        What made you think that VBA would be consistent across Office applications? grin

        Try SendKeys “%{F11}”

        That should work in all Office apps…

    • #795425

      >Application.ShowVisualBasicEditor = True

      Kevin, I’ve been using this every day since March 2001, and this morning decided it was time to use it in Excel97, as well as Word97, but sadly
      (1) it doesn’t translate directly and
      (2) I can’t find anyhting like it in Excel.

      Any clues?

    • #795426

      I hate it when that happens ……

      Public Sub OpenVBE()
          Dim strName As String ' Retain filename/extent from dialogue box.
          With Application.Dialogs(xlDialogOpen)
              If .Show = -1 Then
                  strName = .Name ' Grab the Name; user will have navigated to DefaultFilePath.
                  Workbooks.Open FileName:=Application.DefaultFilePath & Application.PathSeparator & strName
              Else
              End If
          End With
      '    Application.ShowVisualBasicEditor = True ' Open VBE
          Application.VBE.MainWindow.Visible = True
          Application.VBE.MainWindow.WindowState = 2 ' same as vbext_ws_Maxz
      End Sub
      
    • #795427

      I hate it when that happens ……

      Public Sub OpenVBE()
          Dim strName As String ' Retain filename/extent from dialogue box.
          With Application.Dialogs(xlDialogOpen)
              If .Show = -1 Then
                  strName = .Name ' Grab the Name; user will have navigated to DefaultFilePath.
                  Workbooks.Open FileName:=Application.DefaultFilePath & Application.PathSeparator & strName
              Else
              End If
          End With
      '    Application.ShowVisualBasicEditor = True ' Open VBE
          Application.VBE.MainWindow.Visible = True
          Application.VBE.MainWindow.WindowState = 2 ' same as vbext_ws_Maxz
      End Sub
      
    Viewing 3 reply threads
    Reply To: Code: VBE — Not to be outdone

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: