combo box from data validation with source list in separate sheet

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??

Originally Posted by Jay Trull

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

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

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

Jay Trull wrote:
> 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??


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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" wrote:

> There are instructions here for a combobox that uses a named range on a
> different sheet:
>
> http://www.contextures.com/xlDataVal11.html
>
> Jay Trull wrote:
> > 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??
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

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


Jay Trull wrote:
> 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" wrote:
>
>
>>There are instructions here for a combobox that uses a named range on a
>>different sheet:
>>
>> http://www.contextures.com/xlDataVal11.html
>>
>>Jay Trull wrote:
>>
>>>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??
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

"Debra Dalgleish" wrote:

> 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
>
>
> Jay Trull wrote:
> > 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" wrote:
> >
> >
> >>There are instructions here for a combobox that uses a named range on a
> >>different sheet:
> >>
> >> http://www.contextures.com/xlDataVal11.html
> >>
> >>Jay Trull wrote:
> >>
> >>>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??
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

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??

"Debra Dalgleish" wrote:

> 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
>
>
> Jay Trull wrote:
> > 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" wrote:
> >
> >
> >>There are instructions here for a combobox that uses a named range on a
> >>different sheet:
> >>
> >> http://www.contextures.com/xlDataVal11.html
> >>
> >>Jay Trull wrote:
> >>
> >>>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??
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>