How to make use of excel (2003) drop down box value in cell callculations

P

Pekka Siiskonen

How can I make a cell function that would use the value selected by user
from a drop box?

pekka
 
B

Bob Phillips

Just refer to the cell itself. For instance, if the DD is in H1,

=H1*10

or

=IF(H1="Y","red","blue")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
P

Pekka Siiskonen

I should have been more precise -- sorry:

I'm referring to a drop box (or combo box) that needs to have the excel
macros enabled if I desire to select a value from the list. If I choose not
to enable macros while opening the xls, the drop box appears as a bitmap
with:

=EMBED("Forms.ComboBox.1";"")

at the formula bar. It works much the way the "Validation command" under
data menu but it has no "cell value" as such. Also it always shows the
control button for opening the list of possible values.

It is -- I think -- a visual basic control. It has its values from a column
of cells "hidden" elsewhere on the sheet (like validation command has).

The boxes cannot be copy/pasted by: Edit-> select all -> copy -> paste if
the sheet was opened macros enabled. If it was opened macros disabled I can
copy the "image" and it will appear pasted as a working combo box with the
selected value active but no other values are in the list.

I need to export the selected values and all other data on the original
sheet to another program without typing in the drop box values by hand.

I'm _not_ handy with visual basic.

pekka
 
D

Dave Peterson

I used xl2003 to test this.

I put a combobox from the control toolbox toolbar on a worksheet and assigned a
linkedcell and listfillrange. I didn't have any macros in my workbook's project
and I closed and reopened the workbook and it worked fine.

If I had any macro in the project and disabled the macros, then the workbook
opened in design mode (and I couldn't toggle out of it). But the combobox
stayed a combobox.

But I could hit ctrl-a (a few times) to select all and I could paste into a new
worksheet in a new workbook and that pasted combobox worked fine.

I guess the next questions are:

What version of excel are you using?

And what are you trying to do? If you're opening the workbook in code, maybe
you can enable macros, but stop the workbook_open event from firing????
 
P

Pekka Siiskonen

I'm using Excel 2003 SP2 on W2K SP4.

Excel files are arriving as e-mail attachments. I'm digging the Excel
workbooks from IIS maildata 'drop' folder's eml-files using perl. I have so
far succeded in extracting the excel workbooks and also the data from within
the Excel workbook sheets w. perl 5 except the damned combo boxes. Hence
I've opened the extracted Excel-files with Excel 2003. What I saw is what I
described earlier. The same happens if I open the eml-files with Outlook and
then use Excel, so the extraction process should not be the cause of the
problem.

The sender of Excel-files is changing the combo boxes into something more
plain, but I just got myself jammed with the thing and desired to solve the
case by trying to find a way of opening the excel workbooks and extracting
the data semi-manually with a excel macro or something. I'm a bit puzzled as
why the excel workbooks insist on me enabling the macros if there is nothing
visible there: opening the tools->macro->macros displays _no_ macros from
the sheets. It might be that the spam/virus detecting software in my
workstation is jamming the workbook_open event -- yet that should rather
prevent the macros from getting hidden.. Ah, I don't know..

Anyway -- this is just a nice to know thing really -- but I just got this
urge to solve... Thanks for the effort. I just hate to give up!

pekka
 
Top