Clear User Input

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I am creating a time sheet (with help from my friends
here) and would like to arrange it so that if a user
enters "H" or "S" or "V" (holiday,sick,vacation) into a1,
any data that was entered in a2,a3,a4,a5 is cleared.

I have a data validation on a2:a5 so you can't entere
data if H,S,or V is entered into a1, but if the user
enters data into a2:a5 and then goes back and enters
H,S,V in a1 I would like the data they entered to
disappear.

How can I do this?

thank you,

Matt
 
Thanks anyway, Curillon, but this doesn't help me much.
I am already using Data VALIDATION for the error message
in these cells. If there is a way to also have the cells
cleared by using Data Validation then this is what I need
help with.
Anyone else have an idea that will help, or can elaborate
on Curillon's idea.

Thanks to all,

Matt
 
Matt,
I have no idea how to go about this but you should be able to use an Event Macro. I'm sure you can set it up so when something is entered in A1 the value is checked and the other cells are cleared if the criteria is met.

Check out Chip Pearson's site for some ideas:

http://www.cpearson.com/excel/events.htm


Good Luck,
Mark Graesser
(e-mail address removed)

----- Matt wrote: -----

I am creating a time sheet (with help from my friends
here) and would like to arrange it so that if a user
enters "H" or "S" or "V" (holiday,sick,vacation) into a1,
any data that was entered in a2,a3,a4,a5 is cleared.

I have a data validation on a2:a5 so you can't entere
data if H,S,or V is entered into a1, but if the user
enters data into a2:a5 and then goes back and enters
H,S,V in a1 I would like the data they entered to
disappear.

How can I do this?

thank you,

Matt
 
Matt
I played around with it for awhile and I think I have something you can use

1) rightclick on the worksheet tab and select View Cod
2) Delete the existing code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range
'Clears A2:A5 if A1 contains h,s, or
n = Excel.Range("A1").Valu
If n = "H"
Or n = "h"
Or n = "S"
Or n = "s"
Or n = "V"
Or n = "v" The
Range("A2:A5").ClearContent
End I
End Su

Now whenever anything is done to the sheet this event macro will check cell A1 and clear A2:A5 if necesary. This would also negate the need for data validation, however the user wouldn't get any feed back as to why the inputs are being erased

Good Luck
Mark Graesse
(e-mail address removed)

----- Matt wrote: ----

I am creating a time sheet (with help from my friends
here) and would like to arrange it so that if a user
enters "H" or "S" or "V" (holiday,sick,vacation) into a1,
any data that was entered in a2,a3,a4,a5 is cleared

I have a data validation on a2:a5 so you can't entere
data if H,S,or V is entered into a1, but if the user
enters data into a2:a5 and then goes back and enters
H,S,V in a1 I would like the data they entered to
disappear.

How can I do this

thank you

Mat
 
Back
Top