Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Detroit, Michigan, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fiddling with the VBE (WD2000)

    I'm working on a certain application that includes a UserForm (called frmOne). I added the VB extensibility library to the project and now I have access to the VB components through code. What I want to do is add a certain piece of text to a combo box that is part of the form. Accessing the objects goes ok until I get to the designer part of the form (that is the graphics area). I thought that given a form object I'd get to the controls via "...formObj.designer.controls( and so on.....). Sad enough I get the designer entry but keying the extra dot doesn't do the trick. What is the problem? The way the for was defined or declared? The fact that it's already included in the template?...

    This is the code....

    Public Sub addAss()
    Dim obVBE As VBIDE.VBE
    Dim obVBProj As VBIDE.VBProject
    Dim obVBComp As VBIDE.VBComponent

    Dim obCB As MSForms.ComboBox
    Dim obCod As VBIDE.VBComponent

    Set obVBE = Application.VBE
    Set obVBProj = obVBE.VBProjects("rejectreport")
    Set obVBComp = obVBProj.VBComponents("frmOne")


    'obvbcomp.Designer????
    End Sub

    Thank a lot in advance

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiddling with the VBE (WD2000)

    What is it you are trying to do?

    Modifying a control permanently (sort of designing the form using code)

    or

    Changing a control's property at runtime?

    I guess you know you can:

    Userform1.Controls("Commandbutton1").Caption="Hi there"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Detroit, Michigan, USA
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiddling with the VBE (WD2000)

    I tried the code and the problem is that if the UserForm has already been saved as part of the template, the designer won't recognize the ccontrols collection. That seems to be the problem...which I haven't figured out anyway...

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

    Re: Fiddling with the VBE (WD2000)

    If you want to add an item to the list part of a combo box, you don't need all this VBE stuff, you can use the AddItem method of the combo box.

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Detroit, Michigan, USA
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiddling with the VBE (WD2000)

    This VBE "stuff" is required if you want to save the extra entry to the original code so the combo box will be populated as required next time the form is initialized and loaded (the event that actually loads the info in the box....). Otherwise, the changes would be lost upon closing of the current document. This is an issue of document acting on VB code as opposed to the usual VB code acting on a document.

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Fiddling with the VBE (WD2000)

    Why not store all the required list box entries as CustomDocumentProperties and populate the list box during the FormActivate event?

    StuartR

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiddling with the VBE (WD2000)

    Wouldn't it be MUCH simpler to have a tiny textfile with the list of items to fill the control with?
    If the items need updating, you just change the text file.

    For example:

    (text file begins with the number of items, then immediately below, the items. For example:

    ===
    3
    test1
    test2
    test3
    =======

    Option Explicit

    Dim iItemCount As Integer
    Dim sItemList() As String
    Const sFilename As String = "c:dataItemList.txt"

    Sub ReadItems()
    Dim iCount As Integer
    Dim sstr As String
    sstr = ""
    ReDim sItemList(1)
    Open sFilename For Input As #1
    Input #1, iItemCount
    For iCount = 1 To iItemCount
    Input #1, sItemList(iCount)
    sstr = sstr & vbNewLine & sItemList(iCount)
    ReDim Preserve sItemList(iCount + 1)
    Next
    Close #1
    MsgBox sstr
    End Sub

    Sub WriteItems()
    Dim iCount As Integer
    Open sFilename For Output As #1
    Write #1, iItemCount
    For iCount = 1 To iItemCount
    Write #1, sItemList(iCount)
    Next
    Close #1
    End Sub

    Sub UpdateList()
    ReadItems
    iItemCount = iItemCount + 1
    ReDim Preserve sItemList(iItemCount)
    sItemList(iItemCount) = InputBox("please give text for new entry")
    WriteItems
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    Sep 2002
    Location
    Detroit, Michigan, USA
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiddling with the VBE (WD2000)

    Well that's not impossible. Technically it would be feasible to take the data in the box , concatenate them and save them to a DocProp and the later split the results and populate back the control. But again..that still doesn't explain why the controls collection is not avaialble when the user form is part of the template. Personally , I'd like to keep more control over the workarounds of the VBE but yes, it's possible.

  9. #9
    Star Lounger
    Join Date
    Sep 2002
    Location
    Detroit, Michigan, USA
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiddling with the VBE (WD2000)

    I can't speak for the original poster but ....in my opinion., no it wouldn't. Keeping track of two files is never easier than keeping track of one and again...what if the project is a data entry thing that many people have access too?. The issue is that the VBE should be controllable like anything else.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiddling with the VBE (WD2000)

    If the project is a data entry thing many people have access to, the last thing I would do is trying to modify the code on the fly to add items to a list in the application. Use that method and you're asking for trouble. The only reliable way I can see if many people have access to the project is to use ADO to control a tiny table in an Access database file holding the projects' dynamic data.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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