December 3rd, 2013, 11:27 PM
Outlook forms combobox
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.
' 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
' 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
' 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
' There's some distribution lists, so use the smaller
' ConArray to eliminate extra blank values in the list box
For I = 0 to NumContacts - 1
ConArray(I,1) = FullArray(I,1)
ConArray(I,2) = FullArray(I,2)
Control.List() = ConArray
December 4th, 2013, 12:01 PM
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.
December 4th, 2013, 08:14 PM
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?
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")
If objXL Is Nothing Then
Set objXL = CreateObject("Excel.Application")
On Error GoTo 0
Set Mybook = objXL.Workbooks.Add("X:\WIP\DANI\Activity Sheets 2014\nsw list.xlsx")
Control1.List() = MyCompanyName
Control2.List() = MyCompanyID
December 4th, 2013, 10:39 PM
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.
December 4th, 2013, 11:22 PM
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
This person uses a vlookup in the second sub of their code. So maybe this makes sense to you?
December 5th, 2013, 11:13 AM
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.
December 5th, 2013, 06:58 PM
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?
December 6th, 2013, 01:14 PM
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.
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))
Private Sub Combo1_Click()
Combo2.ListIndex = Combo1.ListIndex
Private Sub Combo2_Click()
Combo1.ListIndex = Combo2.ListIndex
December 8th, 2013, 07:55 PM
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?
December 8th, 2013, 09:38 PM
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.
December 8th, 2013, 10:05 PM
Cool. Thanks so much for your help to this point. I'll post the code once I crack it!