I'm a VBA rookie using Excel 2002. I set up a simple userform with a
ComboBox populated from an Outlook 2002 contacts folder. It's working
fine. When the user selects a name from the ComboBox, the macro fills
some cells with the contact's Name, Address,etc. I'd like to upgrade
my userform to have dynamic text boxes. When the user selects a
contact, but has yet to click OK, I'd like the text boxes to update
with that contact's info.

I'm doing pretty well with the Outlook interface (thanks to Dick
Kusleika), so I can get the data I want from Outlook. I just can't
figure out how to do the text boxes. I'm attaching the code I have so
far. It all works. Any help would be greatly appreciated.
_______

Option Explicit

Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim Fldr As Outlook.MAPIFolder
Dim olCi As Outlook.ContactItem
Dim olItems As Outlook.Items
Dim custFldr As Outlook.MAPIFolder

Private Sub UserForm_Initialize()

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.Folders(1)
Set custFldr = Fldr.Folders("Customers")
Set olItems = custFldr.Items
olItems.Sort "[LastName]", False

For Each olCi In olItems
Me.ComboBox1.AddItem olCi.LastName
Next olCi

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)

Set Fldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

Private Sub btnOK_Click()

If ComboBox1.ListIndex < 0 Then
Beep
Else



For Each olCi In olItems
If olCi.LastName = Me.ComboBox1.Value Then
[LName] = olCi.LastName
[Fname] = olCi.FirstName
[Sname] = olCi.Spouse
[AddrStreet] = olCi.MailingAddressStreet
[AddrCity] = olCi.MailingAddressCity
[AddrZip] = olCi.MailingAddressPostalCode
[Phone] = olCi.HomeTelephoneNumber

Exit For
End If
Next olCi

Unload Me

End If

End Sub

Private Sub btnCancel_Click()

Unload Me

End Sub

Never mind. I figured it out.

Kelley

You need to use the Combobox_AfterUpdate event. Then it would look
something like

For Each olCi in Fldr.Items
If olCi.Fullname = Me.Combobox1.Value
Me.TextBox1.Text = olCi.Address
Me.TextBox2.Text = olCi.Phone
etc...
End If
Next olCi

When the combobox is updated, it will fill the textboxes with the other
information. Does that answer your question? Post back if you get stuck
and need more details.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Kelley" wrote in message
om...
I'm a VBA rookie using Excel 2002. I set up a simple userform with a
ComboBox populated from an Outlook 2002 contacts folder. It's working
fine. When the user selects a name from the ComboBox, the macro fills
some cells with the contact's Name, Address,etc. I'd like to upgrade
my userform to have dynamic text boxes. When the user selects a
contact, but has yet to click OK, I'd like the text boxes to update
with that contact's info.

I'm doing pretty well with the Outlook interface (thanks to Dick
Kusleika), so I can get the data I want from Outlook. I just can't
figure out how to do the text boxes. I'm attaching the code I have so
far. It all works. Any help would be greatly appreciated.
_______

Option Explicit

Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim Fldr As Outlook.MAPIFolder
Dim olCi As Outlook.ContactItem
Dim olItems As Outlook.Items
Dim custFldr As Outlook.MAPIFolder

Private Sub UserForm_Initialize()

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.Folders(1)
Set custFldr = Fldr.Folders("Customers")
Set olItems = custFldr.Items
olItems.Sort "[LastName]", False

For Each olCi In olItems
Me.ComboBox1.AddItem olCi.LastName
Next olCi

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)

Set Fldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

Private Sub btnOK_Click()

If ComboBox1.ListIndex < 0 Then
Beep
Else



For Each olCi In olItems
If olCi.LastName = Me.ComboBox1.Value Then
[LName] = olCi.LastName
[Fname] = olCi.FirstName
[Sname] = olCi.Spouse
[AddrStreet] = olCi.MailingAddressStreet
[AddrCity] = olCi.MailingAddressCity
[AddrZip] = olCi.MailingAddressPostalCode
[Phone] = olCi.HomeTelephoneNumber

Exit For
End If
Next olCi

Unload Me

End If

End Sub

Private Sub btnCancel_Click()

Unload Me

End Sub