Worksheet_Change conflict

**BRISBANEBOB** · 12-01-2010, 06:15 PM

I'm a compelte newbie to VBA (reasonably OK around Excel) and I'm trying to set up two Worksheet_Change routines as detailed below. If both are enabled neither will work so I assume there is some sort of conflict and one needs to be renamed. Is this correct, and if so, how do I do it?

Thanks for any help

Please Login or Register  to view this content.

**Richard Buttrey** · 12-01-2010, 06:37 PM

Hi,

Where are these procedures? It's confusing since you seem to be implying there are two, whereas there is only one Worksheet_Change event procedure and that's associated with the Sheet.

Have you by any chance written these at the Module level?

Please upload your workbook so that we can see the problem in context.

Rgds

**BRISBANEBOB** · 12-01-2010, 06:57 PM

Thanks for the response. Unfortunately, for reasons of confidentiality, I can't submit the sheet.

Procedure 1 is 'designed' so that if a cell (range name "Course") is changed (a new course is selected from a drop-down menu) the student's name also must be changed so a message advises this.

Procedure 2 below is a clever bit of code (needless to say, not written by me) which allows easy entry of time values when a numeric value is entered in a particular range ("Time") of cells .

The code for both is contained in the sheet, not in a module.

I hope this is enough to establish the error of my ways...

Thanks

Please Login or Register  to view this content.

**Richard Buttrey** · 12-01-2010, 07:06 PM

Hi,
Are you able to anonymise the data and delete all non relevant sheets to overcome the confidentiality restriction. It's always easier to work with real data.

Regards

**BRISBANEBOB** · 12-01-2010, 07:37 PM

Thanks for your patience - I've cleared out most of the data and left the bits I can;'t get to work.If both bits of code are enabled (code contained on the sheet), neither works.

Thanks for your help

**Richard Buttrey** · 12-02-2010, 06:17 AM

Hi,

See the attached. I've removed most of the Sheet_Change code to simplify it and put data validation on the time cells rather than relying on VBA. As a general rule it's almost always preferable to use standard Excel functionality where you can rather than re-invent the wheel with VBA.

I've also created a dynamic range name 'studentlist' and used that in the student cell validation. I also removed the merged cells from the time cells. Again, and unless there's a real need to do so, try and avoid merging cells. These cause real problems with other aspects of Excel functionality. If you just want to centre text/numbers across a range you can use the format horizontal alignment 'center cells across selection' which is usually just as good and avoids the merge.

Rgds

**romperstomper** · 12-02-2010, 06:48 AM

Please note when using code tags that the closing tag is [/code] and not [code/]

**BRISBANEBOB** · 12-02-2010, 04:13 PM

Thanks for that input - it is appreciated. The reason I took the approach I did was to use an indirect data validation i.e. when a course was chosen, the only student names that would come up in the student drop-down were these from that course. I used the code with the OnChange routine so that if, after a course was selected with a student name from the corresponding student drop-down list the course was changed, I wanted the student name to either be deleted (as that student was not a membe of the new course) or a message box come up telling them to select a new student (hence numerous ranges with student names). The reason I used the routine to allow the time 12:30 to be input as 1230 was to speed up the data entry process - a lot of our students battle with the concept of a colon separation of numbers. Using the two OnChange routines solved both of these problems. The problem I remain with is what do I have to change in the routiens so that they don't conflict.