Copying cell menu contents

[kevinlcarlson]

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

 

[Jim Rech]

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

 

[kevinlcarlson]

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?

 

[Jim Rech]

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

 

[kevinlcarlson]

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