Thread: Create popup field to enter value and have that value part of a formula that populate

I don't know if this is possible but if it is, that would be awesome! Here's what I want to do. I have a drop-down menu created in excel (let's say in cell A1) with different text selections. One of the selections is "Travel Mileage." If choose "travel mileage" from the drop-down menu, I want to either have a pop-up field that has you enter the number of miles traveled in that pop-up field. If I enter, for example 10.75 miles, I want cell B1 to return the value of 10.75 miles X $0.55 = $5.91. If "travel mileage" is not selected, it doesn't do anything. Is this possible in excel? If so, I want to know if it can go one step further and display the number of miles (the number 10.75) next to the word "travel mileage" so that I know what the person entered into the pop-up field.

Please help!
Thank you!!!!

This is VBA territory, and requires a Worksheet_Change macro. This macro fires every time a cell on the worksheet is changed.

Open the VBA Editor. Double click on the sheet you are interested in and paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sMiles As Single

If Target.Value = "Travel Mileage" Then
 sMiles = InputBox("Please Enter Mileage", "Travel Mileage")
 Target.Offset(0, 1) = 0.55 * sMiles
 Target.Value = "Travel Mileage " & sMiles
 End If
 
End Sub

You need to ensure the text exactly matches (capitalisation, etc), but if you're using drop downs this is easy.

Best regards, Rob.

Rob, that was AWESOME! It worked flawlessly and exactly what I wanted. Thank you times a million!!!

Hi Roy

I thought it would be necessary as the request was for a pop up box, a drop down (assumed to be a validation list) is being used, and then the validation needs to be violated to insert the mileage next to "Travel Mileage". There's no way to enter the mileage without introducing another column or the possibility of user error (i.e. relax the validation and people start entering "Travel Mileage 10m", "Travel Mileage10" ...) which becomes hard to read in the IF statement.

Mind you, I'm 10 years out of date using Excel 2003 (damn my archaic company!) There may well be other and better options in 2010 that I'm not aware of.

I agree - it's normally better to avoid VBA and I would be very pleased to see my code become obsolete.

Cheers, Rob.