How can I...
 

Hi everyone,

Here's my problem...

I currently have cells which use data validation to show a list of
available entries for that cell. Both the cell and source list start
life on a different sheet, e.g. sheet 3, with the validated cells being
copied to sheet 1 as a part of a macro to insert a new row into the
invoice form on sheet 1.

The problem is that the source list is now quite large and my manager
has asked if the list could be numbered such that the first item at the
top of the list is numbered "1" the second down "2" etc.

I would also like to increase the number of rows displayed at once when
the drop-down list appears.

I tried the forms version combobox but I couldn't set these to a source
list with two columns (item no, item description).

I then tried setting up activex combo boxes on the template sheet
(sheet 3) instead of the data validation which works fine until the
macro copies the cells and the combo boxes across to sheet 1. The
combo boxes continue to link to the cell as referenced when it was on
the template (even though I didn't set them as absolute references
using the "$" symbol), thus when I have inserted several new rows to
the form on sheet one, each row links to the same cell and not one
relative to it's position.

Can anyone suggest how to fix my activex approach or an alternative way
of achieving my managers request?


---
Message posted from http://www.ExcelForum.com/

How can I...
 

addresses in activeX controls are not adjusted automatically as are
references in Cells. When you do the copy with code, you simply need to
have your code assign a new reference/link for the linkedcell property or
listfillrange property or both.


--
Regards,
Tom Ogilvy

"cornishbloke " wrote in
message ...
Hi everyone,

Here's my problem...

I currently have cells which use data validation to show a list of
available entries for that cell. Both the cell and source list start
life on a different sheet, e.g. sheet 3, with the validated cells being
copied to sheet 1 as a part of a macro to insert a new row into the
invoice form on sheet 1.

The problem is that the source list is now quite large and my manager
has asked if the list could be numbered such that the first item at the
top of the list is numbered "1" the second down "2" etc.

I would also like to increase the number of rows displayed at once when
the drop-down list appears.

I tried the forms version combobox but I couldn't set these to a source
list with two columns (item no, item description).

I then tried setting up activex combo boxes on the template sheet
(sheet 3) instead of the data validation which works fine until the
macro copies the cells and the combo boxes across to sheet 1. The
combo boxes continue to link to the cell as referenced when it was on
the template (even though I didn't set them as absolute references
using the "$" symbol), thus when I have inserted several new rows to
the form on sheet one, each row links to the same cell and not one
relative to it's position.

Can anyone suggest how to fix my activex approach or an alternative way
of achieving my managers request?


---
Message posted from http://www.ExcelForum.com/