All,
I posted in another Excel newsgroup and was directed here.

I am looking to do something similar to
http://puremis.net/excel/code/065.shtml on a worksheet (Itemized) that
they are doing here. I currently have a form that uses VB to create a
unique and sorted list on a user form. And it works fine, but sometimes
I have to manual edit some past entry. I would like to use the list
generated by the VB to use as the source for Data Validation source
during manual entry for column B.

Is it possible to use the list generated by the VB as the source for
data validation? I have included the VB that I use to generate the list
below.

Thanks in advance.
Harry

Private Sub UserForm_Initialize()
Dim MyUniqueList As Variant, i As Long
GLCode.Value = ""

With Me.GLCode
.Clear ' clear the listbox content
MyUniqueList = UniqueItemList(Range("'Income
Stmt'!$B$4:$B$40"), True)
For i = 1 To UBound(MyUniqueList)
If Trim(MyUniqueList(i)) = "" Then
'skip it
Else
.AddItem MyUniqueList(i)
End If
Next i
.ListIndex = 0 ' select the first item
End With
End Sub

Private Function UniqueItemList(InputRange As Range, _
HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count > 0 Then
ReDim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = _
Application.WorksheetFunction.Transpose(UniqueItemList)
End If
End If
On Error GoTo 0
End Function

Yes, just Join the array with a comma delimiter, and load that string.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Harry Stevens" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> All,
> I posted in another Excel newsgroup and was directed here.
>
> I am looking to do something similar to
> http://puremis.net/excel/code/065.shtml on a worksheet (Itemized) that
> they are doing here. I currently have a form that uses VB to create a
> unique and sorted list on a user form. And it works fine, but sometimes I
> have to manual edit some past entry. I would like to use the list
> generated by the VB to use as the source for Data Validation source during
> manual entry for column B.
>
> Is it possible to use the list generated by the VB as the source for
> data validation? I have included the VB that I use to generate the list
> below.
>
> Thanks in advance.
> Harry
>
> Private Sub UserForm_Initialize()
> Dim MyUniqueList As Variant, i As Long
> GLCode.Value = ""
>
> With Me.GLCode
> .Clear ' clear the listbox content
> MyUniqueList = UniqueItemList(Range("'Income Stmt'!$B$4:$B$40"),
> True)
> For i = 1 To UBound(MyUniqueList)
> If Trim(MyUniqueList(i)) = "" Then
> 'skip it
> Else
> .AddItem MyUniqueList(i)
> End If
> Next i
> .ListIndex = 0 ' select the first item
> End With
> End Sub
>
> Private Function UniqueItemList(InputRange As Range, _
> HorizontalList As Boolean) As Variant
> Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
> Application.Volatile
> On Error Resume Next
> For Each cl In InputRange
> If cl.Formula <> "" Then
> cUnique.Add cl.Value, CStr(cl.Value)
> End If
> Next cl
> UniqueItemList = ""
> If cUnique.Count > 0 Then
> ReDim uList(1 To cUnique.Count)
> For i = 1 To cUnique.Count
> uList(i) = cUnique(i)
> Next i
> UniqueItemList = uList
> If Not HorizontalList Then
> UniqueItemList = _
> Application.WorksheetFunction.Transpose(UniqueItemList)
> End If
> End If
> On Error GoTo 0
> End Function

Bob,
I am going to show my inexperience. I found the below searching the
net and adapted it for my use. I am not sure what you are saying or how
to do it.

Thanks
Harry

Bob Phillips wrote:
> Yes, just Join the array with a comma delimiter, and load that string.
>

"Harry Stevens" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> > All,
> > I posted in another Excel newsgroup and was directed here.
> >
> > I am looking to do something similar to
> > http://puremis.net/excel/code/065.shtml on a worksheet (Itemized) that
> > they are doing here. I currently have a form that uses VB to create a
> > unique and sorted list on a user form. And it works fine, but
sometimes I
> > have to manual edit some past entry. I would like to use the list
> > generated by the VB to use as the source for Data Validation source
during
> > manual entry for column B.
> >
> > Is it possible to use the list generated by the VB as the source for
> > data validation? I have included the VB that I use to generate the
list
> > below.
> >
> > Thanks in advance.
> > Harry
> >
> > Private Sub UserForm_Initialize()
> > Dim MyUniqueList As Variant, i As Long
> > GLCode.Value = ""
> >
> > With Me.GLCode
> > .Clear ' clear the listbox content
> > MyUniqueList = UniqueItemList(Range("'Income
Stmt'!$B$4:$B$40"),
> > True)
> > For i = 1 To UBound(MyUniqueList)
> > If Trim(MyUniqueList(i)) = "" Then
> > 'skip it
> > Else
> > .AddItem MyUniqueList(i)
> > End If
> > Next i
> > .ListIndex = 0 ' select the first item
> > End With
> > End Sub
> >
> > Private Function UniqueItemList(InputRange As Range, _
> > HorizontalList As Boolean) As Variant
> > Dim cl As Range, cUnique As New Collection, i As Long, uList() As
Variant
> > Application.Volatile
> > On Error Resume Next
> > For Each cl In InputRange
> > If cl.Formula <> "" Then
> > cUnique.Add cl.Value, CStr(cl.Value)
> > End If
> > Next cl
> > UniqueItemList = ""
> > If cUnique.Count > 0 Then
> > ReDim uList(1 To cUnique.Count)
> > For i = 1 To cUnique.Count
> > uList(i) = cUnique(i)
> > Next i
> > UniqueItemList = uList
> > If Not HorizontalList Then
> > UniqueItemList = _
> > Application.WorksheetFunction.Transpose(UniqueItemList)
> > End If
> > End If
> > On Error GoTo 0
> > End Function

Dana DeLouis wrote:
>>>> Private Function UniqueItemList(InputRange As Range, _
>
> Not sure, but you may find this simple demo using a Dictionary a little easier.
> I included a demo of "Join"
> Again, just to offer some ideas.
>
> Sub TestIt()
> Dim UniqueList
> 'Load some data...
> [A1:A7] = [{"AA";"BB";"CC";"AA";"BB";"CC";"AA"}]
> UniqueList = UniqueItems([A1:A7])
> 'An example of Join...
> MsgBox Join(UniqueList, vbLf) 'May want to join with ","
> End Sub
>
> Function UniqueItems(Rng As Range) As Variant
> Dim Cell As Range
> Dim Dic As Variant
> Set Dic = CreateObject("Scripting.Dictionary")
>
> On Error Resume Next
> For Each Cell In Rng.Cells
> Dic.Add Cell.Formula, 1 '1 is Dummy value
> Next Cell
> UniqueItems = Dic.Keys
> End Function
>

Dana,
Thanks for the idea. I will play around with it a little bit to see
what happens.

Thanks again.
Harry