Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and VBA
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(UniqueItem List) End If End If On Error GoTo 0 End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and VBA
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" wrote in message ... 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(UniqueItem List) End If End If On Error GoTo 0 End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and VBA
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" wrote in message ... 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(UniqueItem List) End If End If On Error GoTo 0 End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Crazy Data Validation ... List Validation Not Working | Excel Programming | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |