[dba-Tech] Using Defined Names in VBA (was RE: Error handling in Excel)

Well, the following works, but seems rather convoluted to me. OrderID is a
defined range. UniqueList is a single cell where the AdvancedFilter feature
creates a unique list from the items in OrderID. I didn't expect using a
defined name to be so difficult -- and it probably isn't. There is probably
an easier way to accomplish this. If anyone has suggestions, I'm listening.
I do absolutely no development in Excel -- I'm winging it. 

Susan H. 


Function UniqueList()
  'Populate control with
  'unique list.
  Dim rgSource As Range
  Dim rgTarget As Range
  'Get source and unique list
  'range addresses.
  Range("OrderID").Select
  Set rgSource = Selection
  Range("UniqueList").Select
  Set rgTarget = Selection
  'Create unique list.
  Range(rgSource.Address).AdvancedFilter Action:=xlFilterCopy, _
   CopyToRange:=Range(rgTarget.Address), Unique:=True
  'Set combo control's Row Source property.
  rgTarget.Activate
  UserForm1.cboUniqueList.RowSource = Selection.CurrentRegion.Address
  'Display user form.
  UserForm1.Show
End Function 


Way over my head I'm afraid, sorry Susan


Jon