MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Request for input

Posted by Nick on July 20, 2001 6:04 AM

I am in the process of automating the company's expense reporting structure. 90% of the form is complete but I am running into a roadblock.

I need to validate the input on one cell to only allow a calcualtion on mileage if the option of mileage is chosen. If this selection is picked than I would like an input box to appear for the user to enter their mileage and perform the calculation to enter into the totals field. If any other selection is chosen (hotel, taxi, etc) the user can simply inut their totals into the field without any input screens appearing.

Can anyone shed any light on how this can be accomplished?


Posted by Ivan F Moala on July 20, 2001 7:13 AM

Via the worksheets SelectionChange Event
Right click on the sheet tab
select view code and paste this in

Note: Assumes
1) Mileage input cell = D2
2) Totals @ A1
Change as required or repost if unsure.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Mileage
Dim Prompt As String
Dim Title As String
Dim Totals

Prompt = "Enter mileage"
Title = "Mileage Input required"

If Target.Address = "$D$2" Then

Mileage = Application.InputBox(Prompt, Title, Type:=1)
Mileage = Mileage * 1.12
Totals = Range("A1")
Totals = Totals + Mileage
Range("A1") = Totals
End If

End Sub