calling inputbox if cell value is true

[jeanstp]

Hi everyone,
I am working on a piece of VBA code to pop an InputBox to user if cell value in same row changes to "lost". The result would be that ACtive Cell would take the entered value by user in Input Box.

This is where I am with code


Function MyFunction(scellule As Range) As String
Dim sMycell As String
If MyFunction = "lost" Then
Set sMycell = Application.InputBox("Please enter lowest bidder amount")
ActiveCell.value = MyFunction.sMycell
Else
Exit Function
End Function

[mdmackillop]

Can you post a workbook showing your formulae and layout to see how formulae interrelate

[jeanstp]

Hi here is a screenshot so basically IF user selects Lost from drop down in column D (Job Status) THEN an InputBox would pop and force the user to enter an amount which would set the value of next cell in column E (Lowest Bid).

I wanted to use the a Function in VBA because I wanted to use the cell in column D as an argument passed to MyFunction() and MyFunction() would return the result in active cell.

thank you

Jean


19057

[jeanstp]

I found it, the only problem is that InputBox popping twice for some reason


Public Function MyFunction(MyTest As String) As Double
If MyTest = "Lost" Then
MyFunction = Application.InputBox("Please enter LOWEST BIDDER's amount")
Else
MyFunction = 0
End If
End Function

[jeanstp]

I just found out that the DROP DOWN LIST data validation in cell triggers MyFunction twice, not sure how to prevent this

[mdmackillop]

Put this code in the Worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> "Lost" Then Exit Sub
Target.Offset(, 1) = InputBox("Enter lowest bid")
End Sub