Alexandre said:
Hi
I want to place one combobox on sheet2, to do a list of some itens located
on sheet 1.
With validation don´t work, because data is on sheet1 and combobox on sheet2
I don´t want to use forms.
I think than there is a way to insert code on the cell, but i don't know....
please somebody help me!!!
Thanks
You can use validation if you trick excel.
Let's say you have the following on sheet1
A1 - first
A2 - second
A3 - third
A4 - fourth
and you want them to be in a combobox on sheet 2
Add validation as you normally would
(Validation->Settings->Allow->Lists) and for the source, use the
INDIRECT function to wrap the range address.
e.x. Source: =INDIRECT("'Sheet1'!A1:A4") and you'll have a working
combobox with the options from sheet1. (Make sure you use an apostrophe
when sheet names have spaces in them)
If for some reason that solution is not acceptable, you can also use a
control combobox instead of a form combobox. You can add it from the
control toolbox (Edit->toolbars->control toolbox)
After adding/sizing the combobox, you can link it by right clicking it
(assuming you are in design mode) and selecting "properties". Under
properties, find "ListFillRange" and type in the address that you want
to link to (i.e. =Sheet1!A1:A4). Once you exit design mode, the
combobox should work properly.
You can also fill the combobox using code. If that is your intention,
please let me know.
One final note - If you use the control combobox and you have macros in
the workbook; if you disable macros when the workbook is opened, it
will disable the combobox as well.
Validation and forms comboboxes will still work if macros are disabled.
I hope this helps.
