| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dependent List (via Data Validation) Error | Excel Worksheet Functions | |||
| Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
| Data Validation using List (But needs unique list in drop down lis | New Users to Excel | |||
| named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
| Validation - List - Separate Worksheet | Excel Worksheet Functions | |||
Hybrid Mode
