Worksheet change event applying to multiple ranges

Hello - there many quesitons that very nearly provide the answer to this, and think I've tried them all....I'm probably missing something as I'm putting the pieces together. The following code works just find on a range in an excel table I have (adding colon's to times entered), but I have 5 seperate tables, that I need this to apply to. I have tried writing 5 sub procedures and calling them based on condition, but thats not working - probably my syntax.

Please Login or Register  to view this content.

thanks

Hy,

The Error isn't she here ?

Please Login or Register  to view this content.

The rest of the code seems to work well

I don't get an error at all with that particular code actually. What i need is for this same operation to apply to 4 other ranges in the worksheet, in addition to Range("TableResp[Time]"):

I also need it to apply to Range("TableCVS[Time]), Range ("TableRen[Time]"), and 3 other tables.

You just create 5 other test as same the first

A little awkward, but did you try repeating the code for each range?

Please Login or Register  to view this content.

Hi,
Oh john, there are easier, no

It's the same think for the 5 or more area, so the code below should work

Please Login or Register  to view this content.

Regards

Thanks both for the suggestions.

John I tried your suggestion over and over and couldn't get it to work. there's something with this procedure that results in entering a time 1559 that results in "00:00", and it has something to do with the application.enableevents. With help on this board I got it working with this single procedure but when I tried your suggestion, that issue reappeared. Maybe because there were 10 of these using this method.

Brian - your suggestion seems to have solved this. Presumably because it kept the same format and there remains only two of those enableevent statements even though it applies to 5 tables. Thanks!! So that solved the problem! With John's suggestion there was 10 of these statements and i'm guessing that's why the problem reared it's ugly head. I noticed other people having this same issue when I was looking myself.

I suppose why I'm looking have this as a called procedure depending on what column you happen to be in is because I would eventually like to have similar code that acts on a date column within the same table. So if you enter a date as 102513, it's formatted as 25/Oct/13. If you're in the "time" column, the procedure that Brian recommended is called.

I should add, my problem with calling this (sorry if my terminology isn't correct), has to do with the arguments. When I try to make Brians suggestion a sub procedure

Please Login or Register  to view this content.

This I can't get working. Obviously I'm asking questions that are beyond my abilities so sorry if they aren't phrased exactly right. doing my best to describe what I hope to accomplish.

Why do you want "export" the code in a other Sub ?

See attached worksheet....maybe I'm just not looking at this the correct way. But I intend to do the same sort of formatting with the date field. this would require almost identical code (i presume). I thought it would be easier to have two seperate sub procedures: yours formatting the time, and another formatting the date. And the worksheet change event "calling" the appropriate one based on whether or not a date field was changed, or a time field was changed.

Hi,

A If ... End If it used for one condition, you can Add more If... Endif

Try this

Please Login or Register  to view this content.

Thanks - you make it sound and look so easy so I tried to use this and apply the time formatting to the date column as well (just to test). Had two questions come up as I was doing this:

1) Is the error handling as I have it below OK? I enter in an invalid time in teh date field and no error pops up.
2) The application.enable events. Does each if...endif start with enableevents = false, and end with enableevents = true?

Please Login or Register  to view this content.

Really apprecaite your help, if you have the patience to continue to respond, I won't be able to reply for some time. Off to catch a flight..