Worksheet_Change looking for 2 changes 1

[mleosu]

2 questions:
First, can you have 2 worksheet_change subs for a worksheet? I tried, but it only ran one.

Second, I currently have 1 Worksheet_Change and it is performing 2 functions. The first looks to see if it needs to run the bankemp macro, based on cell information.

the 2nd function it performs pops up a userform if data is entered into column E.

My problem is that the first function keeps repeating no matter what cell i am in and I only need it to perform the bankemp macro once. how do i get it to stop when the user enters data into other cells?

I am pretty sure it runs over and over because the value of A2 doesnt ever change... I tried using a for loop, but either i didnt enter it correctly or vba just doesnt allow 2 for loops because i received an error message "for already in use" when it got to the 2nd function

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Cells(2, 1) = 1 Then bankemp


For Each cell In Range("E16:E45")
   If Not IsEmpty(Cells(Target.Row, 5).Value) Then
      Application.EnableEvents = False
      WebOrderRole.Show
      Application.EnableEvents = True
    ElseIf IsEmpty(Cells(Target.Row, 5).Value) Then
      Application.EnableEvents = False
    Cells(Target.Row, 6).Value = ""
    Cells(Target.Row, 7).Value = ""
    Cells(Target.Row, 8).Value = ""
    Cells(Target.Row, 9).Value = ""
    Cells(Target.Row, 10).Value = ""
    Cells(Target.Row, 11).Value = ""
      Application.EnableEvents = True

   End If
Exit For
Next cell

End Sub

 

[PHV]

Have a look at Intersect

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

For Each cell In Range("E16:E45") does NOTHING. You never use Cell.

You have an Exit For so you never loop.

What is that supposed to accomplish?

What does bankemp do? Does it CHANGE values in this sheet? If so, then that's your problem.

Skip,

_[red][/red]

 

[mleosu]

the "For Each cell In Range("E16:E45")" and everything below it works perfectly and is not in question... that was another thread.

the problem is that I need excel to look at another cell (particularly C34 which are merged) to see if there is text there and if there is text, then run the macro bankemp which uses a mesage box to ask the user if they are an employee of the bank name they just typed in, if so, then mark checkbox1 on the spreadsheet as true. the macro bankemp works perfectly, and using "If Cells(2, 1) = 1 Then bankemp" works by having formula in A2 (if C3<>"",1,0)

the problem is that the worksheet_change keeps looking at "If Cells(2, 1) = 1 Then bankemp" and I only need it to look at it once. i realize that i never told excel to quit looking at A2, but I havent figured out how.

instead of "If Cells(2, 1) = 1 Then bankemp", I had the following code, but it doesnt run bankemp and i get the compile error "For Control variable already in use":

For Each cell In Range("C3:D4")
   If Not IsEmpty(Cells(Target.Row, Target.Column).Value) Then
    bankemp
   End If

 

[SkipVought]

It it's merged, use the MergeArea property.

Skip,

_[red][/red]

 

[PHV]

You wanted something like this ?
If Target.Address = Cells(2, 1).Address And Cells(2, 1) = 1 Then bankemp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mleosu]

okay, now I have the following code and on the positive, it only runs "bankemp" once when it is supposed to run, but now it is not running through the For so the "WebOrderRole.show" is not running. I can type information into any of the cells in E16:E45 and nothing happens. it was working before.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Cells(3, 3).Address And Not IsEmpty(Cells(3, 3).Value) Then
    bankemp
    
For Each cell In Range("E16:E45")
   If Not IsEmpty(Cells(Target.Row, 5).Value) Then
      Application.EnableEvents = False
      WebOrderRole.Show
      Application.EnableEvents = True
    ElseIf IsEmpty(Cells(Target.Row, 5).Value) Then
      Application.EnableEvents = False
    Cells(Target.Row, 6).Value = ""
    Cells(Target.Row, 7).Value = ""
    Cells(Target.Row, 8).Value = ""
    Cells(Target.Row, 9).Value = ""
    Cells(Target.Row, 10).Value = ""
    Cells(Target.Row, 11).Value = ""
      Application.EnableEvents = True

   End If
Exit For
Next cell
End If
End Sub

 

[PHV]

If Target.Address = Cells(3, 3).Address And Not IsEmpty(Cells(3, 3).Value) Then
bankemp
[!]End If[/!]
For ...
...
Next cell
[!]End If[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mleosu]

okay, that worked... but why when i type something into the merged cell C910 it runs WebOrderRole? WebOrderRole is only to run off of information changed in E16:E45

 

[PHV]

And what about this ?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(3, 3).Address And Not IsEmpty(Cells(3, 3).Value) Then
  bankemp
End If
If Not (Application.Intersect(Target, Range("E16:E45")) Is Nothing) Then
  Application.EnableEvents = False
  If Not IsEmpty(Cells(Target.Row, 5).Value) Then
    WebOrderRole.Show
  Else
    Cells(Target.Row, 6).Value = ""
    Cells(Target.Row, 7).Value = ""
    Cells(Target.Row, 8).Value = ""
    Cells(Target.Row, 9).Value = ""
    Cells(Target.Row, 10).Value = ""
    Cells(Target.Row, 11).Value = ""
  End If
  Application.EnableEvents = True
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mleosu]

YES!! THANKS!!