I'm populating a spreadsheet from a template using Access VBA. A cell
in row 3 contains a pulldown static menu of selection items. I need
to copy these cell menu options to each new row of data copied to the
spreadsheet.

If I use wks.Cells(iRow, 22) = wks.Cells(3, 22) then only the cell
contents (not the menu options) get copied.

If I use wks.Cells(iRow, 22).Formula = wks.Cells(3, 22).Formula then
nothing get copied.

Thanks for your help!

Kevin

You're saying that you have a drop down list in an Excel worksheet and you
want to get its members listed? There are at least three ways to put a drop
down on a sheet: Data Validation, a Forms toolbar combobox and Control
Toolbox combobox. Which one are you using?

--
Jim
"kevinlcarlson" wrote in message
oups.com...
| I'm populating a spreadsheet from a template using Access VBA. A cell
| in row 3 contains a pulldown static menu of selection items. I need
| to copy these cell menu options to each new row of data copied to the
| spreadsheet.
|
| If I use wks.Cells(iRow, 22) = wks.Cells(3, 22) then only the cell
| contents (not the menu options) get copied.
|
| If I use wks.Cells(iRow, 22).Formula = wks.Cells(3, 22).Formula then
| nothing get copied.
|
| Thanks for your help!
|
| Kevin
|

Basically, I want to limit the contents of a specific cell to items
from a static menu list within that cell. This has been created in
Row 3, and I want to re-create this same cell menu list for each
additional row added programmatically.
Unfortunately, the cell menu contents seem to be neither simple text
nor a formula. How, then, does one copy this menu to another cell?


On May 16, 1:22 pm, "Jim Rech" wrote:
You're saying that you have a drop down list in an Excel worksheet and you
want to get its members listed? There are at least three ways to put a drop
down on a sheet: Data Validation, a Forms toolbar combobox and Control
Toolbox combobox. Which one are you using?

--
Jim"kevinlcarlson" wrote in message

oups.com...
| I'm populating a spreadsheet from a template using Access VBA. A cell
| in row 3 contains a pulldown static menu of selection items. I need
| to copy these cell menu options to each new row of data copied to the
| spreadsheet.
|
| If I use wks.Cells(iRow, 22) = wks.Cells(3, 22) then only the cell
| contents (not the menu options) get copied.
|
| If I use wks.Cells(iRow, 22).Formula = wks.Cells(3, 22).Formula then
| nothing get copied.
|
| Thanks for your help!
|
| Kevin
|

Sorry but Excel does not have a 'static menu list' feature. I asked you how
the 'list' was created and you just restated what you posted before.

--
Jim
"kevinlcarlson" wrote in message
ups.com...
| Basically, I want to limit the contents of a specific cell to items
| from a static menu list within that cell. This has been created in
| Row 3, and I want to re-create this same cell menu list for each
| additional row added programmatically.
| Unfortunately, the cell menu contents seem to be neither simple text
| nor a formula. How, then, does one copy this menu to another cell?
|
|
| On May 16, 1:22 pm, "Jim Rech" wrote:
| You're saying that you have a drop down list in an Excel worksheet and
you
| want to get its members listed? There are at least three ways to put a
drop
| down on a sheet: Data Validation, a Forms toolbar combobox and Control
| Toolbox combobox. Which one are you using?
|
| --
| Jim"kevinlcarlson" wrote in message
|
| oups.com...
| | I'm populating a spreadsheet from a template using Access VBA. A cell
| | in row 3 contains a pulldown static menu of selection items. I need
| | to copy these cell menu options to each new row of data copied to the
| | spreadsheet.
| |
| | If I use wks.Cells(iRow, 22) = wks.Cells(3, 22) then only the cell
| | contents (not the menu options) get copied.
| |
| | If I use wks.Cells(iRow, 22).Formula = wks.Cells(3, 22).Formula then
| | nothing get copied.
| |
| | Thanks for your help!
| |
| | Kevin
| |
|
|

Sorry if my question was unclear. This would be the menu created by
selecting Data / Validation / Settings / Allow: List and specifying a
cell range of valid values. I want to create this functionality for a
specific column in every new row added using VBA code. Simply copying
cell contents or formulas does not accomplish this...

Thanks,
Kevin


On May 17, 6:42 am, "Jim Rech" wrote:
Sorry but Excel does not have a 'static menu list' feature. I asked you how
the 'list' was created and you just restated what you posted before.