K
Kelley
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
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