Cells value to combobox

[hensucker]

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.

[westconn1]

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

[hensucker]

Data Store.zip

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.

[westconn1]

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

[hensucker]

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?

[westconn1]

can i get it to show both columns?

can you see both in the dropdown list?

[hensucker]

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

[westconn1]

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

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

[hensucker]

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.

[westconn1]

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

[hensucker]

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.

[westconn1]

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

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

[hensucker]

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?

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

[westconn1]

try

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