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

combo box from data validation with source list in separate sheet
 

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??
I like to name the range of the source, say List1
so then go to
Data,validation,list,source
type =List1


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=518997

combo box from data validation with source list in separate sheet
 

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

combo box from data validation with source list in separate sh
 

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

combo box from data validation with source list in separate sh
 

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

combo box from data validation with source list in separate sh
 

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

combo box from data validation with source list in separate sh
 

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