Drop-down menu options

C

conksu

Hi,
I am creating an excel sheet with drop down menus in different cells,
however, I would like to have one option where it would automatically
prompt for text entry. For example, one question is asking whether you
charge a fee based on a call or whether if it is a flat fee. If the
person chooses "Flat fee", I would like it to allow the person to enter
the value of the fee. If it could be some kind of seperate prompt
dialog, that would be ideal.

Thanks!
 
B

Bernie Deitrick

consksu,

You could use the worksheet's change event: copy the code below, right click the sheet tab, select
"View Code" and paste the code in the window that appears.

This is written for a single cell (B3, putting the rate into C3), but could easily be re-written for
multiple cells.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
If UCase(Target.Value) = "FLAT FEE" Then
Application.EnableEvents = False
Range("C3").Value = Application.InputBox("Enter your fee rate", , , , , , , 1)
Application.EnableEvents = True
End If
End If
End Sub

HTH,
Bernie
MS Excel MVP
 
C

conksu

I'm sorry, I'm not quite sure I understand. What should happen using
this code?

I tried copying and pasting, however, I didn't know what was supposed
to happen so I couldn't test it.

Maybe another example that would help (I'm not sure if I'm explaining
myself clearly) would be to have a single cell with a drop-down of
multiple items and one of the options would be "Other..." and when
"Other..." is selected, you are prompted to input what that value
should be.

I know how to create the menu, however, I'm trying to make this as
simple as possible for those that will be filling it out.

Thanks.
 
B

Bernie Deitrick

Not sure what you mean by bumping - folks who know an answer always feel free to jump in at any
point.

Contact me privately deitbe at consumer dot org, and I will send you a working example of the
code that I posted. When you enter the value "Flat Fee" into cell B3, an input box will pop up
asking for the rate, which would then be entered into cell C3.

HTH,
Bernie
MS Excel MVP
 
Top