NEED HELP QUICK!

E

excel-chump

I have until the end of the day (5pm est) to resolve this issue!
Now that I have your suggestion working I have a followup question:

I select "Agency" in H1 that allows input into H2
I put in "4.5", which reads as $4.50
I then change in H1 to "Permanent Resident"
H2 still displays "$4.50"

How do I get H2 to return blank when neither "Agency" or "Employee Traveler"
is chosen after the previous steps have been taken? Clearly the field can be
cleared manually, but I want to take out the human error of forgetting to go
back and change this.

-------------------------------
 
P

Peo Sjoblom

Not possible using validation, you would need either a formula in H2 or an
event macro. The latter might be the best solutions but there are too many
idiots in this world to idiot proof against.


--


Regards,


Peo Sjoblom
 
E

excel-chump

I'm very new to macros and so far I've only been able to cut and paste
portions to get any results in that arena. Do you have any suggestions
regarding a macro to accomplish this? I can't put a formula in H2 b/c I need
to allow a number input if the previous criteria is met and that would erase
any formula in H2. thank you for your help!
 
G

Gord Dibben

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$H$1" And Target.Value <> "Agency" _
And Target.Value <> "Employee Traveler" Then
Target.Offset(1, 0).Value = ""

End If
stoppit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord Dibben MS Excel MVP
 
E

excel-chump

Thank you so much, Gordon and Peo Sjoblom! Your help has been invaluable to
me.
 
Top