How to Loop Worksheet_Change Macro

**KareninCT** · 05-22-2011, 01:23 PM

Hi! I am trying to create a timesheet in Excel using Macros. I have dropdown boxes and based on the combinations selected, I want to force the user to input a number in the 7th columns or not allow the value. At this point, I have been able to figure out how to blacken the cell, but not completely disallow entry. More importantly, the only way I have found to replicated this over the next 100 rows is copy and paste the formula. There must be a way to rewrite the formula or use a loop to replicate the code. With the way I have it now, the tab key takes the user all over the spreadsheet. PLEASE HELP!!!!

Please Login or Register  to view this content.

**Simon Lloyd** · 05-22-2011, 03:20 PM

I've not looked at your workbook as i don't have xl2007 or 2010 here, i have tidied up your code, forced users to only enter a nuber in column 7 and forced only the use of column 7

Please Login or Register  to view this content.

**barryleajo** · 05-22-2011, 03:43 PM

With acknowledgements to Simon
Working on this at the same time

Please Login or Register  to view this content.

Then write your code to handle your data entry

**KareninCT** · 05-22-2011, 10:04 PM

Sorry, I'm trying to teach myself VBA as I go here. What do you mean by writing the code to handle the data entry??

Also, this replicates the code which is such a timesaver, but if I select "Claims" in Column D and press tab, it still skips over to Column F. Is there a way to fix that??

Thank you SO much for your help!

**Simon Lloyd** · 05-29-2011, 02:01 PM

Have you solved this?

**KareninCT** · 05-29-2011, 02:53 PM

The code works great, but the tab key still skips all over the spreadsheet. Is there a way to fix that?

**barryleajo** · 05-30-2011, 06:55 PM

Hi Karen
Sorry for not replying sooner I have been offline for a week.
To deal with your queries.

but if I select "Claims" in Column "D" and press tab, it still skips over to Column F

Your original code selected column "F" if the specific combination wasn't selected so this is what my code segment did.
To change this behaviour change (or delete) this line:

Please Login or Register  to view this content.

6 = column "F" (1 = "A", 2 = "B", 3 = "C" and so on).

What do you mean by writing the code to handle the data entry??

Simon's example code (reproduced below) illustrates in principle how you can test data entered into cells for both validity (as in this case) and values - work on this and develop it to your requirement.
Note that following on from the above explanation, the reference to "7" is column "G"

Please Login or Register  to view this content.

The Tab key works correctly on your s/sheet I downloaded i.e. moving one cell to the right.
As a suggestion the following link may be something to check out:
http://excelribbon.tips.net/T006710_...n_to_Jump.html
Hope this helps take you a bit further.