Data Validation and VBA
 

All,
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(UniqueItem List)
End If
End If
On Error GoTo 0
End Function

Data Validation and VBA
 

Hi, Harry. You probably meant to ask this in the "Excel Programming" forum;
this one's for the non-coders.

--- "Harry Stevens" wrote:
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....

Data Validation and VBA
 

Bob Bridges wrote:
Hi, Harry. You probably meant to ask this in the "Excel Programming" forum;
this one's for the non-coders.

--- "Harry Stevens" wrote:
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....

Bob,
Thanks I will post there.

Harry

Data Validation and VBA
 

I didn't look into your code but checked out the link.

you can set any range to be used for datavalidation by manipulating the
Validation object of the cell you want validated. The code on the site uses;

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:=sFormula
.IgnoreBlank = True
.InCellDropdown = True
End With

In this case variable "sFormula" is a string and holds the range used to
validate the entries.

hope this helps.
Tieske

"Harry Stevens" wrote in message
...
All,
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(UniqueItem List)
End If
End If
On Error GoTo 0
End Function

Data Validation and VBA
 

Tieske,
Thanks, now I will have to see how to get my code to file the
sFormula...more plugging to do.

Thanks

Tieske wrote:
I didn't look into your code but checked out the link.

you can set any range to be used for datavalidation by manipulating the
Validation object of the cell you want validated. The code on the site
uses;

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=sFormula
.IgnoreBlank = True
.InCellDropdown = True
End With

In this case variable "sFormula" is a string and holds the range used to
validate the entries.

hope this helps.
Tieske

"Harry Stevens" wrote in message
...
All,
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(UniqueItem List)
End If
End If
On Error GoTo 0
End Function