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!!!!

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("D5").Value = "Claims" Then
If Range("E5").Value = "Check DX; Email Questions; Enter, Check, Send claims; etc." Then
        
        Range("g5").Activate
        ActiveCell.Interior.ColorIndex = 0
       
        
Else
If Not Range("E5").Value = "Check DX; Email Questions; Enter, Check, Send claims; etc." Then
        
        Range("g5").Select
        ActiveCell.Interior.ColorIndex = 1
        Range("F5").Select
End If
End If
Else
If Range("D5").Value = "Admin" Then
Range("g5").Select
ActiveCell.Interior.ColorIndex = 1
Range("E5").Select
End If
End If

**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

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 7 Then Cells(Target.Row, 7).Select
With Target
    If .Column = 7 Then
        If Not IsNumeric(.Value) Then
            MsgBox "incorrct entry, please enter a valid number"
            .ClearContents
            .Select
        End If
    End If
End With
If Range("D5").Value = "Claims" Then
    If Range("E5").Value = "Check DX; Email Questions; Enter, Check, Send claims; etc." Then
        Range("g5").Interior.ColorIndex = 0
    Else
        If Not Range("E5").Value = "Check DX; Email Questions; Enter, Check, Send claims; etc." Then
            Range("g5").Interior.ColorIndex = 1
        End If
    End If
Else
    If Range("D5").Value = "Admin" Then
        Range("g5").Interior.ColorIndex = 1
    End If
End If
End Sub

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

With acknowledgements to Simon
Working on this at the same time

Private Sub Worksheet_Change(ByVal Target As Range)

rownum = Target.Row

    If Target.Column = 4 Or Target.Column = 5 Then
    
        If Cells(rownum, 4).Value = "Claims" And Cells(rownum, 5).Value = "Check DX; Email Questions; Enter, Check, Send claims; etc." Then
            Cells(rownum, 7).Activate
            Cells(rownum, 7).Interior.ColorIndex = 0
        Else
            Cells(rownum, 7).Interior.ColorIndex = 1
            Cells(rownum, 6).Select
        End If
        
    End If

End Sub

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:

 Cells(rownum, 6).Select

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"

If Target.Column <> 7 Then Cells(Target.Row, 7).Select
With Target
    If .Column = 7 Then
        If Not IsNumeric(.Value) Then
            MsgBox "incorrct entry, please enter a valid number"
            .ClearContents
            .Select
        End If
    End If
End With

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.