We help IT Professionals succeed at work.

Excel dropdown list choice used in a formula

Medium Priority
348 Views
Last Modified: 2012-08-23
Using Excel 2007.  Want to use a combobox on a worksheet for users to choose a month (January, etc.).  Their choice should be stored as a text variable "January" for use in an existing formula in the same worksheet.  The formula should execute when they make their choice from the list.  Can someone help me construct the solution using VBA?  I'm a newbie with VBA.

Thank you, experts!
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,
any particular reason you want to do this with VBA and a combo box? You could use Data Validation instead with a list of the 12 month names.

See attached.

cheers, teylyn
Book3.xlsx

Author

Commented:
That works nicely, Teylyn.

But I'm afraid the workgroup who will be using this workbook have changed how they want this to work:  They want to rename a worksheet to the name of a month and use the updated name in this formula, which is on a different worksheet in the same workbook.

=IF(ISBLANK(July!E12)^""^RIGHT(July!E12^11)).

Said another way.  When they change then name of the July worksheet to August, the formula should change along with it.

Thanks, and apologies for changing the requirements.  I am open to a variety of methods.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
If a formula refers to a cell in another sheet and the sheet gets renamed, the formula will reflect the new sheet name.

If that is not working for you, please post a file with some examples.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.