combo box from data validation with source list in separate sheet

[Guest]

I am able to create a combo box for a data validation drop down list when the
source list is in the same sheet. I can't seem to get it to work on
workbooks where my source list is in a separate sheet? The combo box is
empty??

 

[davesexcel]

I am able to create a combo box for a data validation drop down list
when the
source list is in the same sheet. I can't seem to get it to work on
workbooks where my source list is in a separate sheet? The combo box
is
empty??

I like to name the range of the source, say List1
so then go to
Data,validation,list,source
type =List1

 

[Debra Dalgleish]

There are instructions here for a combobox that uses a named range on a
different sheet:

http://www.contextures.com/xlDataVal11.html

 

[Guest]

Thank you Debra!! This now works using a named range from a different sheet.
I was using the wrong code. Now a problem I have is that I am using "Time"
in one of my source lists, example: 7:00 AM, 8:00 AM, etc... and when I use
the Combo Box it converts the time to a number- 8:00 AM to .33333333, etc..
Is there a way to fix this?? I have formatted the cells in both the source
lists and in the validation cell and I don't see anything in the combobox
properties list?? Thank you again for your help. Jay

 

[Debra Dalgleish]

You could add a change event to the combo box, and pick up the
formatting from the cell. For example:

Private Sub TempCombo_Change()
If IsNumeric(TempCombo.Value) Then
TempCombo.Value = Format(CDate(TempCombo.Value), _
Range(TempCombo.LinkedCell).NumberFormat)
Range(TempCombo.LinkedCell).Value = TempCombo.Value
End If
End Sub

 

[Guest]

Thank you very much, this solved the problem. Your examples have been very
helpful. Jay

 

[Guest]

Everything is working fine except when I click in the combo box list on the
the time 6:00 AM it put's 12:25 AM in the cell and when I click on 12:00 PM
it puts 12:05 AM in the cell. All of the other " 46 time values" in the
source list work except for these two?? When I click on these two particular
"times" in the data validation drop down list the proper value comes up??