Howdy.
What i would like to try and achive is when a user selects a item from the 1st Combobox the 2nd Combobox is the populated with the cell that is to the right of the selected item.
for Example if a user selects AAB from combobox1,, Combobox2 should populate with Belly.
Maby using combobox1_Exit for the excercise.
Last edited by D_Rennie; 06-20-2009 at 12:41 PM.
This example :http://www.4shared.com/file/10257117...listBoxes.html, uses listboxes but can easily be used for ComboBoxes
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Not sure if yo simply mean this
Each combox is filled from the lists in Sheet1, so when yo change combobox1 you also change combobox2
With Me .ComboBox2.ListIndex = .ComboBox1.ListIndex End With
Cheers RoyUK
Ill Take A look.
I though of using somethink like in post#3
Though that would be on good as the listindex of the combobox is forever changing so will never relate to the list on the spreedsheet.
(hope That makes sence)
The Example doesent realy help.
The Example is storing the .listindex +1 as a variable for the use in selecting the column for the 2nd listbox.
My Issue may be very techincal due to the way that the list is being soughted.
I was thining so somethink along the lines of using the .text value to find matches in coumn A then with the second combobox using additem from column B.
So if i can search colum A for a name when a match is found it sotres the rowcount as a variable so then i can use it like range B variable additem. and then loop through to no more matches found.
I dont even know where to start, Of if its possable.
have you looked at my second post?
Yeah that what i tryed to explain in post #5
I dont think that approach will work. Since my listindex wont corrospond with the Sheet RowCount.Col = .ListBox1.ListIndex + 1 With wsLists Set rSource = .Range(.Cells(2, Col), .Cells(.Rows.Count, Col).End(xlUp)) End With .ListBox2.List = rSource.Value
Cheers
I'm not sure what you are trying to do? Are you trying to match the selection with the adjacent cell in the list?
Ive changed it workbook a little to help explain.
Currently Combobox1 does not remove duplacate's Though i would like to be able to have that option If i choose.
If the users selects excelforum.com and tabs out of the combobox.
Combobox2 list is then populated with the adjacent cell for excelforum.com.
Cheers.
You seem to be making your code over complicated, why such a complicated way to load combobox!?
I had a feeling that would come up sooner or later.
WHY??? Just cos i though it would be cool. :d
And it also allowes for a unique way to enter the data. And i figured it could be used in many application. Like Telephone Contacts, or adding items to stocklist.
Like most of the coding ive done, Ive gone a hell of a long way round. to acchive my goal. This will probley prove to be no exception. LOL
Am I right/ basically combobox1 lists Column A.
Select from ComboBox1 & fill ombobox2 with matches.
Code doesn't need to be cool, it needs to be efficient & fast!
Yep i think you got it.
This is all you need
Option Explicit Dim rSource As Range Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim Cl As Range Dim ClAddress As String With Me 'if no selection in combobox1 quit If .ComboBox1.ListIndex < 0 Then Exit Sub .ComboBox2.Clear Set Cl = rSource.Find(Me.ComboBox1.Value, LookIn:=xlValues) If Not Cl Is Nothing Then ClAddress = Cl.Address Do .ComboBox2.AddItem Cl.Offset(0, 1).Value Set Cl = rSource.FindNext(Cl) Loop While Not Cl Is Nothing And Cl.Address <> ClAddress End If End With End Sub Private Sub UserForm_Initialize() 'load combobox1 With Sheet1 Set rSource = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With Me.ComboBox1.List = rSource.Value End Sub
Thats real nice.
by using rSource it also allowed me to remove everythink related to dataRange and use rSource instead, Wich was alot cleaner than my attempt.
do you have am example for removing duplacates ive tryed nodupes with no success
Thanks Heaps RoyUk
There seams to be a few issues still.
If i add A into a line on column A and Added next to it.
Combobox2 is populated with 5 items when it should only show Added.
Cheers
That's because yo have used a single letter, the code finds each instance of a - in Titan etc.Originally Posted by D_Rennie
What do you mean/ Removing duplicates from the sheet?
No i would like to keep any duplacates on the sheet.
Though stop them entering the combobox
Thats what i figured.
When you use the .find can it be set to only find exact maches
i tryed Lookin:=xlWhole though got a error
its ok i got it
LookIn:=xlValues, lookat:=xlWhole)
Last edited by D_Rennie; 06-20-2009 at 12:37 PM.
Am pretty new on this Forum. Am working on a program that should auto generate receipts for and keep finacial record for my small school. My headache is that i am not able to load students information on the form when i name is selected. I have named the combobox containing name as 'cboName', the other comboboxes that should be automatically udated with corresponding info are 'cboAdm' and 'cboClass' The data source for this form is worksheet called Enrolment. Aparently this worksheet can increase or decrease total numbers depending on the enrolment in the school. Somebody please assist. Thanks Alot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks