hi OSAVentures Calamba
check this link
http://blogs.techrepublic.com.com/howdoi/?p=111
VBA, the automated way
First, create two ranges: the list’s source data and a target range
for the unique list. Select the source data; in this case, that’s
cells A2:A18. From the Insert menu, choose Name and then select
Define. Excel will automatically assume the content of A1, the string
OrderID, as the name. Click OK. Next, select cell H1 and name it
UniqueList using the same process.
Next, you need a user form and a combo box. Press Alt+F11 to launch
the Visual Basic Editor (VBE). From the Insert menu, choose UserForm.
If necessary, click the Toolbox button to launch the Toolbox and drag
a combo box control to the user form. Name the combo box control
cboUniqueList. Save the user form as UserForm1.
Now, you’re ready to enter the code that automates the list. Choose
Module from the Insert menu. Then, enter the function in Listing A.
This function creates a unique list from the data in the OrderID named
range, populates cboUniqueList with that list, displays UserForm1, and
then deletes the unique list from the sheet.
Listing A
Function UniqueList()
'Populate control with
'unique list.
Range("OrderID").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("UniqueList"), Unique:=True
'Set combo control's Row Source property.
Range("UniqueList").Activate
UserForm1.cboUniqueList.RowSource = Selection.CurrentRegion.Address
'Display user form.
UserForm1.Show
Selection.CurrentRegion.Clear
End Function
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
