Re: Cells value to combobox
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
1 Attachment(s)
Re: Cells value to combobox
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.
Re: Cells value to combobox
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
Re: Cells value to combobox
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?
Re: Cells value to combobox
Quote:
can i get it to show both columns?
can you see both in the dropdown list?
Re: Cells value to combobox
Yes they are both in the list but when you select one it only displays the first column in the combobox
Re: Cells value to 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
Re: Cells value to combobox
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.
Re: Cells value to combobox
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
Re: Cells value to combobox
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.
Re: Cells value to combobox
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
Re: Cells value to combobox
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
Re: Cells value to combobox
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