Copying combobox
Posted by Hock on July 05, 2000 12:43 AM
I'm trying to create a data entry sheet with each row as a record, and each column within the row as a field. For some of these fields, I do not want the user to key in text freely, but rather, I want them to select from a combobox, i.e. select from a drop down listbox.
I've done that for a row. Now I want to duplicate that row 1000 times. However, I find that when I copy and paste, the LinkedCell property of the combobox does not change. Eg. in Cell B3, I create a combobox and the LinkedCell property of that combobox is set to return the selection value back to cell B3. When I copy row 3 and paste it to row 4, the LinkedCell property of the combobox in row 4 still refers to cell B3. For normal formulas, Excel would have changed it automatically to refer to B4 instead.
Any ideas on how to solve this? I wouldn't want to manually edit the LinkedCell property for 1000 rows.
Thanks!
Posted by Ryan on July 05, 0100 3:33 AM
Hock,
Instead of a combobox you can use Data Validation. If you click on the cell you want it in then go to Data --> Validation and where it says "Allow" click list. Set your source for the list and your done. Now there is a built in drop down list in the cell, and you can refer to the cell directly using the formula "=A1." Hope this gets around your problem. Let me know!
Ryan
Posted by Ryan on July 06, 0100 6:06 AM
Hock,
The validation can work for another sheet if you use a named Range. But for your 1000 or so ranges, that wouldn't work well, but I thought I would tell you for the future.
Ryan
Posted by ak on July 05, 0100 1:51 PM
You can do this programmatically using VBA.
Make a loop that goes to the cell gets the cell address into a variable and then plug that variable into the cell link on the combo box properties.
Run a loop, you have to figure out the spacing per cell.
Posted by Hock on July 05, 0100 11:55 PM
Thanks Ryan. Your suggestion works well for my needs. The only limitation is that the validation list needs to reside on the same worksheet.
In fact, this data validation function is simpler to use than the combobox.
Your kind assistance is very much appreciated. Thanks again.
