So my goal is that I want a pick list that when I select values from it, it adds the newly selected value comma separated from the previous values - essentially allowing the user to select multiple options from the pick list.
I googled and I have working code which does just this, the only problem is that nothing stops the user from selecting the same item over and over, and the items end up out of order. I want to split the string into an array of strings using Split(), and sorting it alphabetically, and if the newly chosen item already exists, then it isn't appended onto the list.
The problem I am having is that I can't figure out how to import the libraries / namespaces that the functions Split(), IndexOf(), Array.Sort(), etc. are involved in.
Here is a link to the code I have so far - the IndexOf line isn't recognized, and "Imports System" at the top results in an "Invalid Outside Procedure" error.
http://pastebin.com/raw.php?i=yBw4NKXw
Any help or guidance would be really appreciated - thanks!
Hello opsayo,
Welcome to the Forum!
I am not sure you will get an answer to your question in this forum. Your question is about VB.Net programming. This forum is for VBA programming.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thanks Leith. Before I post this in another forum, what is the exact difference between VB.net and VBA programming? All I knew was that I was trying to code a macro in visual basic for an Excel spreadsheet.Originally Posted by Leith Ross
Thanks!
In this demo I take a comma seperated list that is out of order and contains duplicates ... the resulting comma seperated list is in order and contains no duplicates...Is that what you looking for ?
Option Compare Text Public Sub DemoHere() Def = "F,d,a,rod,k,BCA,d,ABC,a,d,I,b,nim,c,K" TestList = Application.InputBox("Please Enter Scrambled List", "Demo", Def) If TestList = False Then Exit Sub ResultList = SortToUniqueList(TestList) ln1 = "Orginal List : " & TestList & vbNewLine ln2 = "Result List: " & ResultList pt = MsgBox(ln1 & ln2, vbInformation, "Resulting List") End Sub Private Function SortToUniqueList(TestList) As String Dim listCollection As New Collection Dim CollectionMember 'MAKE UNIQUE COLLECTION On Error Resume Next For Each ValStr In Split(TestList, ",") listCollection.Add ValStr, CStr(ValStr) Next ValStr 'turn off error handling On Error GoTo 0 ' SORT COLLECTION For Sort1 = 1 To listCollection.Count - 1 For Sort2 = Sort1 + 1 To listCollection.Count If listCollection(Sort1) > listCollection(Sort2) Then Tmp1 = listCollection(Sort1) Tmp2 = listCollection(Sort2) 'Swap the items over listCollection.Add Tmp1, , Sort2 listCollection.Add Tmp2, , Sort1 'Delete the original items listCollection.Remove Sort1 + 1 listCollection.Remove Sort2 + 1 End If Next Next 'make comma sep string list For Each CollectionMember In listCollection If Len(Trim(CollectionMember)) > 0 Then If FinalList = "" Then FinalList = CollectionMember Else FinalList = FinalList & "," & CollectionMember End If End If Next SortToUniqueList = FinalList End Function
Contactus ut Sentio
YOUR FEEDBACK: To Say Thanks, or to leave Constructive Comments, please click on the Scales of Justice Icon at top of current post.
>Develope Good Habits with MSDN Coding Standards <>How To Add Macros & VBA Code To Your Workbooks<>Best Practices For Referencing Cells, Ranges and Sheets<
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Bookmarks