Cells value to combobox

I have a combobox on sheet 1 which i need populating with the values of cells on sheet 2.

So the combobox if possible could have the value or 2 cells in it on the same line for instance sheet 2 column 1 consists of dates and column 2 consists of times, so 1 line in the combobox would look like "22/11/2011 13:45" is that possible?
And this needs to be a continuous list but it only shows column 1 and column 2 data if column 3 is empty.

how are you currently populating combobox?
i presume setting the rowsource should work, else you could fill the combobox using the additem method

post a sample workbook that demonstrates what you want to achieve

Attachment 86812

Above is the file i have attached for you, There are 2 pages Diary and Entry,
the combobox on the Entry page needs to be populated with a list created from the diary page columns 1 and 2.

So a continious list of dates and times to be displayed, but only if there is nothing in column 3 on the same row.

e.g.

Column1 Column2 Column3

22/11/2011 10:00 Hello
23/11/2011 11:00
24/11/2011 12:00
25/11/2011 13:00 Hello

So in the combobox on the entry page you would see

23/11/2011 11:00
24/11/2011 12:00

So as you can see the combobox only detects rows of data that havent been complete past column 3.

vb Code:

Private Sub Workbook_Open()
Sheets("entry").ComboBox1.Clear
For Each c In Sheets("diary").Range("a:a")
    If IsEmpty(c) Then Exit For
    If IsEmpty(c.Offset(, 2)) Then
        With Sheets("entry").ComboBox1
            .AddItem c
            .List(.ListCount - 1, 1) = Format(c.Offset(, 1), "h:nn")
        End With
    End If
Next
End Sub

you will need to set enough columns in the combobox, with appropriate widths

Works a treat thank you very much pete, just quickly though when a selection is made from the combobox the displayed selection only shows the date can i get it to show both columns?

Quote:

---

can i get it to show both columns?

---

can you see both in the dropdown list?

Yes they are both in the list but when you select one it only displays the first column in the combobox

you can only display one column in the text area of a multi column combobox, but you can change which column you display

or try like

vb Code:

Private Sub ComboBox1_Click()
With ComboBox1
    .Text = .List(.ListIndex, 0) & vbTab & .List(.ListIndex, 1)
End With
End Sub

Thanks pete, i have realised that only one column can be displayed after selection so that is fine.
May i ask for another slight bit of assistance, after i have made selection i want my data to go back to that line of.

So i populate the entry page as you see, make a selection from the drop down then press submit this will then place my data on the same row as the selection i made from the comobox.

as the list is not contiguous from the sheet, you can not use an index to return the row

i would suggest adding the row number to an additional column (visible or not) in the combobox

the alternative would be a double find, find date, then find time staring at date

Thats thrown a spanner in the works right there, nothing is ever straight forward :(

I think if used a userform this wouldnt be an issue, but i didnt want that route as its not really needed.

Quote:

---

I think if used a userform this wouldnt be an issue

---

i don't believe it would make any difference at all, just add a column to the combo and add the row number

vb Code:

With Sheets("entry").ComboBox1
            .AddItem c
            .List(.ListCount - 1, 1) = Format(c.Offset(, 1), "h:nn")
            .list(.listcount -1, 2) = c.row    ' add this line
        End With

if you want to conceal from users make column width = 0

that way you can always return the row number

So the code below send the data to the sheet, but where do i implement the c.row so it goes back to the row it came from please?

vb Code:

Private Sub commandbutton1_click()
    nrow = Range("a65536").End(xlUp).Row + 1
    col = 1
        Do Until IsEmpty(Sheets(2).Cells(nrow, col + 3))
            nrow = nrow + 1
        Loop
 
    Worksheets(2).Cells(nrow, col + 2).Value = Sheets(1).Range("C3").Value
    Worksheets(2).Cells(nrow, col + 3).Value = Sheets(1).Range("C5").Value
    Worksheets(2).Cells(nrow, col + 4).Value = Sheets(1).Range("C7").Value
    Worksheets(2).Cells(nrow, col + 5).Value = Sheets(1).Range("C9").Value
    Worksheets(2).Cells(nrow, col + 6).Value = Sheets(1).Range("C11").Value
    Worksheets(2).Cells(nrow, col + 7).Value = Sheets(1).Range("C13").Value
    Worksheets(2).Cells(nrow, col + 8).Value = Sheets(1).Range("C15").Value
    Worksheets(2).Cells(nrow, col + 9).Value = Sheets(1).ComboBox1.Value
        On Error GoTo 0
End Sub

try

vb Code:

nrow = combobox1.list(combobox1.listindex, 2)

note: lines 4 to 6 in your code should not be doing anything useful
or change to column d for your endrow