Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How did he do this?
In searching through the posts in this group for a solution to my problem,
I discovered the forms toolbar and I suspect that part of my solution may involve that. Can anyone point me to a resource to find more about it. The real question: I have to invent a workbook to interface with an existing workbook and enter data into it so I tried to understand what the author has done. The sheet in question has the first 2 columns (ignoring header rows) populated with invisible combo boxes. They remain invisible until selected when the down arrow appears and becomes active. Clicking DnArrow shows data. As I am going to stuff data in these cells from the new workbook, I need to make sure its on the list. In trying to look at combo properties I can't. Here is what I did: I made the control toolbox visible, went to design mode, selected a combo and asked for properties. I get the properties of the sheet. What is going on? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How did he do this?
1). Are you sure the cells are no DATA VALIDATION instead?
Corey.... "Len B" <gonehome(atoptusnet:com:au) wrote in message ... In searching through the posts in this group for a solution to my problem, I discovered the forms toolbar and I suspect that part of my solution may involve that. Can anyone point me to a resource to find more about it. The real question: I have to invent a workbook to interface with an existing workbook and enter data into it so I tried to understand what the author has done. The sheet in question has the first 2 columns (ignoring header rows) populated with invisible combo boxes. They remain invisible until selected when the down arrow appears and becomes active. Clicking DnArrow shows data. As I am going to stuff data in these cells from the new workbook, I need to make sure its on the list. In trying to look at combo properties I can't. Here is what I did: I made the control toolbox visible, went to design mode, selected a combo and asked for properties. I get the properties of the sheet. What is going on? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How did he do this?
Sounds like the workbook is using Data Validation.
Strangely enough found on the Data menu as "Validation". It has a "list option" that behaves as you state. Also, the ComboBox from the Control Toolbox must be selected in order to display its properties. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Len B" <gonehome(atoptusnet:com:au) wrote in message In searching through the posts in this group for a solution to my problem, I discovered the forms toolbar and I suspect that part of my solution may involve that. Can anyone point me to a resource to find more about it. The real question: I have to invent a workbook to interface with an existing workbook and enter data into it so I tried to understand what the author has done. The sheet in question has the first 2 columns (ignoring header rows) populated with invisible combo boxes. They remain invisible until selected when the down arrow appears and becomes active. Clicking DnArrow shows data. As I am going to stuff data in these cells from the new workbook, I need to make sure its on the list. In trying to look at combo properties I can't. Here is what I did: I made the control toolbox visible, went to design mode, selected a combo and asked for properties. I get the properties of the sheet. What is going on? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How did he do this?
These are probably Data Validation dropdowns. They don't support referencing
to othere worksheets or workbooks however. But you can have a range of helper cells on your worksheet that link to the other workbook using formulas that reference the required cells. Example set up: 1. Enter a formula that references the other workbook in the first helper cell. For example: ='[My Time Sheet.xls]Data'!A1 (note the apostrophes) 2. Drag the formula down for the required number of cells. It should autofill to reference A1, A2, A3... of the other wb and the helper cells should then display the values from the other wb (see note) 3. Now select the cells that you want to contain the dropdowns 4. Select Data Validation... Settings tab 5. Select "List" from the "Allow" dropdown window 6. Ensure that the "In-cell dropdown" checkbox is checked 7. Click inside the "Source" window and then select the helper cell range 8. See the Input Message and Error Alert tabs for optional messages when you select the cell and if/when an error occurs 9. Click the OK button 10. Each of the cells should display a dropdown when selected that lists the helper cell range values Note: This assumes the cells in the other wb are listed vertically and are contiguous. Else, you will have to list them individually or can similarly create a helper cell range in the other wb that is contiguous and in a column. Regards, Greg "Len B" wrote: In searching through the posts in this group for a solution to my problem, I discovered the forms toolbar and I suspect that part of my solution may involve that. Can anyone point me to a resource to find more about it. The real question: I have to invent a workbook to interface with an existing workbook and enter data into it so I tried to understand what the author has done. The sheet in question has the first 2 columns (ignoring header rows) populated with invisible combo boxes. They remain invisible until selected when the down arrow appears and becomes active. Clicking DnArrow shows data. As I am going to stuff data in these cells from the new workbook, I need to make sure its on the list. In trying to look at combo properties I can't. Here is what I did: I made the control toolbox visible, went to design mode, selected a combo and asked for properties. I get the properties of the sheet. What is going on? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How did he do this?
Actually, if you give that range on another sheet a nice name, you can use it.
Debra Dalgleish explains it he http://contextures.com/xlDataVal01.html#Dropdown And Debra explains how to use other workbooks: http://contextures.com/xlDataVal05.html Greg Wilson wrote: These are probably Data Validation dropdowns. They don't support referencing to othere worksheets or workbooks however. But you can have a range of helper cells on your worksheet that link to the other workbook using formulas that reference the required cells. Example set up: 1. Enter a formula that references the other workbook in the first helper cell. For example: ='[My Time Sheet.xls]Data'!A1 (note the apostrophes) 2. Drag the formula down for the required number of cells. It should autofill to reference A1, A2, A3... of the other wb and the helper cells should then display the values from the other wb (see note) 3. Now select the cells that you want to contain the dropdowns 4. Select Data Validation... Settings tab 5. Select "List" from the "Allow" dropdown window 6. Ensure that the "In-cell dropdown" checkbox is checked 7. Click inside the "Source" window and then select the helper cell range 8. See the Input Message and Error Alert tabs for optional messages when you select the cell and if/when an error occurs 9. Click the OK button 10. Each of the cells should display a dropdown when selected that lists the helper cell range values Note: This assumes the cells in the other wb are listed vertically and are contiguous. Else, you will have to list them individually or can similarly create a helper cell range in the other wb that is contiguous and in a column. Regards, Greg "Len B" wrote: In searching through the posts in this group for a solution to my problem, I discovered the forms toolbar and I suspect that part of my solution may involve that. Can anyone point me to a resource to find more about it. The real question: I have to invent a workbook to interface with an existing workbook and enter data into it so I tried to understand what the author has done. The sheet in question has the first 2 columns (ignoring header rows) populated with invisible combo boxes. They remain invisible until selected when the down arrow appears and becomes active. Clicking DnArrow shows data. As I am going to stuff data in these cells from the new workbook, I need to make sure its on the list. In trying to look at combo properties I can't. Here is what I did: I made the control toolbox visible, went to design mode, selected a combo and asked for properties. I get the properties of the sheet. What is going on? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How did he do this?
And I knew better damn it. I have a project that does this already. Don't
know why I posted that. Oops... Greg "Dave Peterson" wrote: Actually, if you give that range on another sheet a nice name, you can use it. Debra Dalgleish explains it he http://contextures.com/xlDataVal01.html#Dropdown And Debra explains how to use other workbooks: http://contextures.com/xlDataVal05.html Greg Wilson wrote: These are probably Data Validation dropdowns. They don't support referencing to othere worksheets or workbooks however. But you can have a range of helper cells on your worksheet that link to the other workbook using formulas that reference the required cells. Example set up: 1. Enter a formula that references the other workbook in the first helper cell. For example: ='[My Time Sheet.xls]Data'!A1 (note the apostrophes) 2. Drag the formula down for the required number of cells. It should autofill to reference A1, A2, A3... of the other wb and the helper cells should then display the values from the other wb (see note) 3. Now select the cells that you want to contain the dropdowns 4. Select Data Validation... Settings tab 5. Select "List" from the "Allow" dropdown window 6. Ensure that the "In-cell dropdown" checkbox is checked 7. Click inside the "Source" window and then select the helper cell range 8. See the Input Message and Error Alert tabs for optional messages when you select the cell and if/when an error occurs 9. Click the OK button 10. Each of the cells should display a dropdown when selected that lists the helper cell range values Note: This assumes the cells in the other wb are listed vertically and are contiguous. Else, you will have to list them individually or can similarly create a helper cell range in the other wb that is contiguous and in a column. Regards, Greg "Len B" wrote: In searching through the posts in this group for a solution to my problem, I discovered the forms toolbar and I suspect that part of my solution may involve that. Can anyone point me to a resource to find more about it. The real question: I have to invent a workbook to interface with an existing workbook and enter data into it so I tried to understand what the author has done. The sheet in question has the first 2 columns (ignoring header rows) populated with invisible combo boxes. They remain invisible until selected when the down arrow appears and becomes active. Clicking DnArrow shows data. As I am going to stuff data in these cells from the new workbook, I need to make sure its on the list. In trying to look at combo properties I can't. Here is what I did: I made the control toolbox visible, went to design mode, selected a combo and asked for properties. I get the properties of the sheet. What is going on? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How did he do this?
Or INDIRECT it
-- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... Actually, if you give that range on another sheet a nice name, you can use it. Debra Dalgleish explains it he http://contextures.com/xlDataVal01.html#Dropdown And Debra explains how to use other workbooks: http://contextures.com/xlDataVal05.html Greg Wilson wrote: These are probably Data Validation dropdowns. They don't support referencing to othere worksheets or workbooks however. But you can have a range of helper cells on your worksheet that link to the other workbook using formulas that reference the required cells. Example set up: 1. Enter a formula that references the other workbook in the first helper cell. For example: ='[My Time Sheet.xls]Data'!A1 (note the apostrophes) 2. Drag the formula down for the required number of cells. It should autofill to reference A1, A2, A3... of the other wb and the helper cells should then display the values from the other wb (see note) 3. Now select the cells that you want to contain the dropdowns 4. Select Data Validation... Settings tab 5. Select "List" from the "Allow" dropdown window 6. Ensure that the "In-cell dropdown" checkbox is checked 7. Click inside the "Source" window and then select the helper cell range 8. See the Input Message and Error Alert tabs for optional messages when you select the cell and if/when an error occurs 9. Click the OK button 10. Each of the cells should display a dropdown when selected that lists the helper cell range values Note: This assumes the cells in the other wb are listed vertically and are contiguous. Else, you will have to list them individually or can similarly create a helper cell range in the other wb that is contiguous and in a column. Regards, Greg "Len B" wrote: In searching through the posts in this group for a solution to my problem, I discovered the forms toolbar and I suspect that part of my solution may involve that. Can anyone point me to a resource to find more about it. The real question: I have to invent a workbook to interface with an existing workbook and enter data into it so I tried to understand what the author has done. The sheet in question has the first 2 columns (ignoring header rows) populated with invisible combo boxes. They remain invisible until selected when the down arrow appears and becomes active. Clicking DnArrow shows data. As I am going to stuff data in these cells from the new workbook, I need to make sure its on the list. In trying to look at combo properties I can't. Here is what I did: I made the control toolbox visible, went to design mode, selected a combo and asked for properties. I get the properties of the sheet. What is going on? -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How did he do this?
Thanks Guys,
Indeed it was Data Validation. That's why, when I selected the cell I thought was a combo, I couldn't get it to show any handles; and that's why the properties I saw were the Sheet's properties. The name of the other guy's wb misled me to an extent too. It is Template97.xls. I was assuming XL ver 97. Oops. His wb is copied and renamed every month (ProjSumFeb.xls) so I can't use Debra's methods and refer to the range in it. I will copy the ranges to a hidden sheet in my wb and use that for my Data Validation. My wb will accumulate project data, summarize it and then paste results row by row (as each project is completed) into the monthly project summary. Debra's page says you can paste data into such cells so I should be ok. Thanks again -- Len "Greg Wilson" wrote in message ... And I knew better damn it. I have a project that does this already. Don't know why I posted that. Oops... Greg "Dave Peterson" wrote: Actually, if you give that range on another sheet a nice name, you can use it. Debra Dalgleish explains it he http://contextures.com/xlDataVal01.html#Dropdown And Debra explains how to use other workbooks: http://contextures.com/xlDataVal05.html Greg Wilson wrote: These are probably Data Validation dropdowns. They don't support referencing to othere worksheets or workbooks however. But you can have a range of helper cells on your worksheet that link to the other workbook using formulas that reference the required cells. Example set up: 1. Enter a formula that references the other workbook in the first helper cell. For example: ='[My Time Sheet.xls]Data'!A1 (note the apostrophes) 2. Drag the formula down for the required number of cells. It should autofill to reference A1, A2, A3... of the other wb and the helper cells should then display the values from the other wb (see note) 3. Now select the cells that you want to contain the dropdowns 4. Select Data Validation... Settings tab 5. Select "List" from the "Allow" dropdown window 6. Ensure that the "In-cell dropdown" checkbox is checked 7. Click inside the "Source" window and then select the helper cell range 8. See the Input Message and Error Alert tabs for optional messages when you select the cell and if/when an error occurs 9. Click the OK button 10. Each of the cells should display a dropdown when selected that lists the helper cell range values Note: This assumes the cells in the other wb are listed vertically and are contiguous. Else, you will have to list them individually or can similarly create a helper cell range in the other wb that is contiguous and in a column. Regards, Greg "Len B" wrote: In searching through the posts in this group for a solution to my problem, I discovered the forms toolbar and I suspect that part of my solution may involve that. Can anyone point me to a resource to find more about it. The real question: I have to invent a workbook to interface with an existing workbook and enter data into it so I tried to understand what the author has done. The sheet in question has the first 2 columns (ignoring header rows) populated with invisible combo boxes. They remain invisible until selected when the down arrow appears and becomes active. Clicking DnArrow shows data. As I am going to stuff data in these cells from the new workbook, I need to make sure its on the list. In trying to look at combo properties I can't. Here is what I did: I made the control toolbox visible, went to design mode, selected a combo and asked for properties. I get the properties of the sheet. What is going on? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|