PDA

View Full Version : Combobox items



garydp
01-15-2010, 01:31 AM
I have a combobox that is populated from a spreadsheet.

the user selects a number of options and then the combobox is populated with dates. there could be the same date in the combobox twice. what i want is to be able to know what date out of all has been selected.

So if there are 5 dates say
01-01-10, 02-01-10,03-01-10,04-01-10 and 05-01-10

and they select the third date (03-01-10) i want to be able to put 3 into a variable. or if they select the first date then 1 into the variable.

is there a way of doing this?

Gary

mikerickson
01-15-2010, 02:06 AM
What kind of combobox is it? on a Userform, ActiveX or from the Forms Menu?
MsgBox ComboBox1.ListIndex will return a 0 based value if from a Userform or ActiveX.

If the Drop Down is from the Forms menu, linking it to a cell will return the value you seek or the VBA MsgBox ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value

garydp
01-15-2010, 02:11 AM
hi its on a user form

mikerickson
01-15-2010, 02:54 AM
ThenMsgBox ComboBox1.ListIndex will return 0 if the first item is selected, 1 if the second is selected, etc. and -1 if the user has selected nothing from the list.

garydp
01-15-2010, 03:37 AM
ok thanks for the reply,

one problem i have is my combobox as i said before could have the same date in twice or more.

so for instance

01-01-01
02-01-01
02-01-01
03-01-01
03-01-01

what i am doing is when the date is selected the data that is in the cell that matches the date is entered into a label. The problem i have is as i am using do until to search through my spreadsheet until it matches the date found. i need to distinguish between the dates that are the same. so if the second of the 02-01-01 is selected it will show the data from the second matched date rather than the first. what i am finding at the moment is because im using a loop it always shows the second set of data and doesnt stop at the first set.

mikerickson
01-15-2010, 03:59 AM
How are the list entrys being put in the ComboBox?
If something likeComboBox1.List = Range("A1:A10").ValueThen
Range("A1").Offset(ComboBox1.ListIndex,0).Select will find the selected item on the sheet.

garydp
01-15-2010, 06:25 AM
Im using the following


comdate.Clear
num = 4
Do Until Sheets(u).Range("E" & num).Value = ""
If Sheets(u).Range("B" & num).Value = Combobox1.Value Then
If Sheets(u).Range("C" & num).Value <> "Pass" Then
comdate.AddItem Format(Sheets(u).Range("E" & num).Value, "DD/MM/YYYY")
End If
End If
num = num + 1
Loop

tpoynton
01-15-2010, 01:34 PM
I would add the row # then to a hidden column in the combobox. I have done this with listboxes, but not comboboxes, so it is untested...you will need to add the second column to the combobox (columncount = 2), and play with columnwidths to hide it.


comdate.Clear
num = 4
Do Until Sheets(u).Range("E" & num).Value = ""
If Sheets(u).Range("B" & num).Value = Combobox1.Value Then
If Sheets(u).Range("C" & num).Value <> "Pass" Then
comdate.AddItem Format(Sheets(u).Range("E" & num).Value, "DD/MM/YYYY")
comdate.List(comdate.ListCount - 1, 1) = num
End If
End If
num = num + 1
Loop

then, when you want to get the row #, use
comdate.List(comdate.ListIndex, 1)
a sample workbook would help if this doesnt work or make sense

EDIT - fixed code after initial post...assuming that num is the row #

garydp
01-19-2010, 12:27 PM
that works brilliant, thanks