Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code: VBE -- Not to be outdone

    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:
    <pre>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).Ac tivate

    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
    </pre>


    Put this sub in a module and attach it to a toolbar button:
    <pre>Sub OpenWorkingModForm()
    frmVBE.Show
    End Sub
    </pre>

    Attached Files Attached Files
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code: VBE -- Not to be outdone

    >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?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code: VBE -- Not to be outdone

    I hate it when that happens ......


    <pre>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
    </pre>


  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code: VBE -- Not to be outdone

    What made you think that VBA would be consistent across Office applications? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Try SendKeys "%{F11}"

    That should work in all Office apps...

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code: VBE -- Not to be outdone

    > 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.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code: VBE -- Not to be outdone

    The principles are similar, Chris, but the devil is in the details. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> 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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> If you don't believe me, play with the concept of "document" in the three apps. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> And as for 97 ... <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Code: VBE -- Not to be outdone

    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.
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code: VBE -- Not to be outdone

    >> 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.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Code: VBE -- Not to be outdone

    Oh well. Guess I need to re-seat my RAM chips.
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •