#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    6
    Rep Power
    0

    Unhappy Outlook forms combobox


    Hi all,

    Let me know if I'm posting in the wrong area. I'm a complete noob and I'm cutting and pasting scripts that I have googled into the Script Editor of outlook. So I'm not 100% if that area is VB or not.

    So I have a 2 column combobox that successfully displays my outlook contacts company name and organizational id number.

    The problem is when type anything or select something in the combobox nothing happens.

    Has anyone else ever had this problem? I'm pulling my hair out each day trying to solve this.

    Code:
    Sub Item_Open()
    
       Dim FullArray()
    
       ' Sets the name of page on the form (Activity Sheet)
       Set FormPage = Item.GetInspector.ModifiedFormPages("Activity Sheet")
    
       ' Sets Control to a list box called ListBox1.
       Set Control = FormPage.Controls("ComboBox1")
    
       ' Get the default Contacts folder
       Set ConFolder1 = Application.Session.GetDefaultFolder(10)
       Set ConFolder2 = ConFolder1.Folders("Nswlist")
    
       ' Get the items in the folder
       Set ConItems = ConFolder2.Items
    
       ' Get the number of total items in the Contacts folder
       NumItems = ConItems.Count
    
       ' Resize array to handle total number of item in the folder
       ReDim FullArray(NumItems-1,2)
    
       ' Loop through all of the items in the Contacts folder,
       ' filling the array with sample data and keeping track
       ' of the number of contacts found.
       NumContacts = 0
       For I = 1 to NumItems
          Set itm = ConItems(I)
          If Left(itm.MessageClass, 11) = "IPM.Contact" Then
             NumContacts = NumContacts + 1
             FullArray(NumContacts-1,1) = itm.CompanyName
             FullArray(NumContacts-1,2) = itm.OrganizationalIDNumber
          End If
       Next
    
       ' Set the control to handle 2 data columns
       Control.ColumnCount = 3
    
       If NumItems = NumContacts Then
          ' They are all contacts, so use the FullArray
          Control.List() = FullArray
       Else
          ' There's some distribution lists, so use the smaller
          ' ConArray to eliminate extra blank values in the list box
          Dim ConArray()
          ReDim ConArray(NumContacts-1,2)
          For I = 0 to NumContacts - 1
             ConArray(I,1) = FullArray(I,1)
             ConArray(I,2) = FullArray(I,2)
          Next
          Control.List() = ConArray
       End If
    
    End Sub
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    So what is Control? You say it is a ListBox, yet you call it a ComboBox. The 2 are quite different. A ComboBox supports typing; a ListBox does not. A ListBox supports columns; a ComboBox does not. Both of them require you to react to the Click event, yet there is no coding for that event.

    J.A. Coutts
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    6
    Rep Power
    0
    Thanks so much for the quick response.

    The control is definitely a combobox, I need the type function. Sorry I had been experimenting with both and forgot to change the comment in the code.

    I require both the Company Name and an ID Number to be on the form so if combobox doesn't support 2 columns then I will have to make another combobox which will autopopulate based on combobox1 value.

    I couldn't think of a way to do this with my outlook contacts. So maybe it would be easier working with an excel range that way I can do a vlookup.

    I've just fudged a code together which just makes 2 comboboxes look up separate ranges in excel. I am able to type in both comboboxes and they are working 100%.

    My question is how to link MyCompanyID to MyCompanyName? Or in otherwords how to perform the vlookup method?

    Code:
    Sub Item_Open()
     
    Set FormPage = Item.GetInspector.ModifiedFormPages("Activity Sheet")
    Set Control1 = FormPage.Controls("ComboBox1")
    Set Control2 = FormPage.Controls("ComboBox2")
    
    On Error Resume Next
    Set objXL = GetObject(, "Excel.Application")
    Err.Clear
    If objXL Is Nothing Then
    Set objXL = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set Mybook = objXL.Workbooks.Add("X:\WIP\DANI\Activity Sheets 2014\nsw list.xlsx")
    Mybook.Worksheets("Sheet1").Activate
    
    MyCompanyName=objXL.Range("a1:a1600").Value
    MyCompanyID=objXL.Range("b1:b1600").Value
    
    
    Set Mybook=Nothing
    Set objXL=Nothing
    
    Control1.List() = MyCompanyName
    Control2.List() = MyCompanyID
    
    End Sub
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    There are a couple of possibilities, but I don't think 2 ComboBoxs is one of them. You could join the 2 arrays into a single string using some kind of separator such as a TAB or ":" or "|". Alternately, you could make your own ComboBox using a ListBox, a TextBox, and an ImageBox for the dropdown arrow. I don't know if this second alternative would work using scripts, but if you wish to proceed, I can probably find some sample code.

    J.A. Coutts
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    6
    Rep Power
    0
    I would have to keep the Company ID separate because when the calendar gets exported to a public excel file they need to be in separate cells for our interfacing program to read them.

    I wouldn't be able to use the Listbox either because the user would then have to search through up to 1000 Company Names. Management wouldn't approve the form if that was the case.

    There must be a way to join the 2 comboboxes doing something like this
    Code:
    MyCompanyName=objXL.Range("a1:a1600").Value
    MyCompanyID=MyCompanyName.Offset(0, 2).Value
    This person uses a vlookup in the second sub of their code. So maybe this makes sense to you?
    http://stackoverflow.com/questions/1...ata-from-excel
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    Just because the items are joined to be added to the ComboBox does not mean that they cannot be easily separated. That is why you use a separator. Since you already have the data in arrays, you could also simply use the index from the selected ComboBox item to directly access the array values. This is probably the easiest solution.

    When using a ListBox in combination with a TextBox, as the user types each character in the TextBox, your code would find the closest match in the ListBox. This technique does require the list to be sorted alphabetically however.

    J.A. Coutts
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    6
    Rep Power
    0
    First of all, thank you so much for your help. I wish I could buy you a drink or 10 in return haha, or at least return the favor somehow!
    I have a lot of calendars exporting into many different excel files so I'd like to avoid touching the excel files at all.

    I'm interested to hear more about using the index to access the array values. Sounds perfect to me. Except I wouldn't have a clue how to go about doing this.

    I have found a sample that I am currently playing around with but considering my experience I don't think I will get very far. Is it easy enough to write this code? If so, do you have a sample I could take a look at?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    After experimenting a bit, I found it was dead easy to coordinate 2 ComboBoxs. If you were using an array, you would just use the ListIndex value to recover the array item.

    J.A. Coutts
    Code:
    Option Explicit
    
    Dim NumItems As Long
    
    Private Sub Form_Load()
        Dim lPntr As Long
        NumItems = 10
        For lPntr = 0 To NumItems - 1
            Combo1.AddItem ("Name" & CStr(lPntr))
            Combo2.AddItem (CStr(lPntr))
        Next lPntr
    End Sub
    
    Private Sub Combo1_Click()
        Combo2.ListIndex = Combo1.ListIndex
    End Sub
    
    Private Sub Combo2_Click()
        Combo1.ListIndex = Combo2.ListIndex
    End Sub
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    6
    Rep Power
    0
    So keeping in mind I'm a noob and trying to learn, I have tried using this code in a few different ways and I keep getting errors.

    How do I use this code? As I mentioned earlier my code is located in the script editor, do I need to move it to the module area and add your code up the top?

    Or should I just be taking bits and pieces from your code and putting it in mine for it to work?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    The code I supplied is VB6 code. VB Script uses the same basic engine, but there are some differences. The biggest one is that all variables in VB Script are defined as Variant. The second biggest difference is that VB6 is compiled code, whereas VB Script is Pseudo Compiled Code; that is to say it is compiled on the fly as each statement is executed.

    Unfortunately, that is the extent of my knowledge of VB Script. I have used it several times on Web pages, but I have never used it with controls, and always found it to be a pain in the *** to troubleshoot. Most of the time I would get it working in VB6 and then convert it to VB Script, which is what you are going to have to do.

    J.A. Coutts
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    6
    Rep Power
    0
    Cool. Thanks so much for your help to this point. I'll post the code once I crack it!

IMN logo majestic logo threadwatch logo seochat tools logo