combine worksheet events

Hi,
I have already 2 worksheet events in one sheet and i wanted to add one more the present code are like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Cells(Target.Row, 1) _
.Range("J1,K1,L1,P1,Q1,R1,U1,V1,W1"), Target) Is Nothing Then
If Cells(Target.Row, "I") = "" Then
MsgBox "SORRY THE I COLUMN IS EMPTY FILL IT with P or S"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If

If Intersect(Target, Range("C2:C2")) Is Nothing Then Exit Sub
If Range("C2:C2") > 0 Then CUSTOMER
End Sub

now i wanted to add the following code to the above :


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("D2,G2")) Is Nothing Then
With Target
.Value = Application.Proper(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Any suggestions will be highly appreciated
with best regards
nowal

Not tested

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Cells(Target.Row, 1), _
Range("J1,K1,L1,P1,Q1,R1,U1,V1,W1"), Target) Is Nothing Then
If Cells(Target.Row, "I") = "" Then
MsgBox "SORRY THE I COLUMN IS EMPTY FILL IT with P or S"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
ElseIf Not Intersect(Target, Range("D2,G2")) Is Nothing Then
With Target
.Value = Application.Proper(.Value)
End With
End If

If Intersect(Target, Range("C2:C2")) Is Nothing Then Exit Sub
If Range("C2:C2") > 0 Then CUSTOMER

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"nowfal" <nowfal.1ssm6r_1122408371.8821@excelforum-nospam.com> wrote in
message news:nowfal.1ssm6r_1122408371.8821@excelforum-nospam.com...
>
> Hi,
> I have already 2 worksheet events in one sheet and i wanted to add
> one more the present code are like this:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Application.Intersect(Cells(Target.Row, 1) _
> Range("J1,K1,L1,P1,Q1,R1,U1,V1,W1"), Target) Is Nothing Then
> If Cells(Target.Row, "I") = "" Then
> MsgBox "SORRY THE I COLUMN IS EMPTY FILL IT with P or S"
> Application.EnableEvents = False
> Target.Value = ""
> Application.EnableEvents = True
> End If
> End If
>
> If Intersect(Target, Range("C2:C2")) Is Nothing Then Exit Sub
> If Range("C2:C2") > 0 Then CUSTOMER
> End Sub
>
> now i wanted to add the following code to the above :
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Range("D2,G2")) Is Nothing Then
> With Target
> Value = Application.Proper(.Value)
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> Any suggestions will be highly appreciated
> with best regards
> nowal
>
>
> --
> nowfal
> ------------------------------------------------------------------------
> nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
> View this thread: http://www.excelforum.com/showthread...hreadid=390337
>

Untested, but I think it will combine them the way you want:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_or_Done
Application.EnableEvents = False
With Target
If Not Application.Intersect(.Cells(1), _
Range("J:L,P:R,U:W")) Is Nothing Then
If Cells(.Row, 9).Value = "" Then
MsgBox _
"Sorry, the column is empty, fill it with P or S"
.ClearContents
End If
ElseIf .Cells(1).Address(False, False) = "C2" Then
If .Value > 0 Then CUSTOMER
ElseIf Not Intersect(.Cells, Range("D2,G2")) Is Nothing Then
.Value = Application.Proper(.Value)
End If
End With
Err_or_Done:
Application.EnableEvents = True
End Sub





In article <nowfal.1ssm6r_1122408371.8821@excelforum-nospam.com>,
nowfal <nowfal.1ssm6r_1122408371.8821@excelforum-nospam.com> wrote:

> Hi,
> I have already 2 worksheet events in one sheet and i wanted to add
> one more the present code are like this:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Application.Intersect(Cells(Target.Row, 1) _
> .Range("J1,K1,L1,P1,Q1,R1,U1,V1,W1"), Target) Is Nothing Then
> If Cells(Target.Row, "I") = "" Then
> MsgBox "SORRY THE I COLUMN IS EMPTY FILL IT with P or S"
> Application.EnableEvents = False
> Target.Value = ""
> Application.EnableEvents = True
> End If
> End If
>
> If Intersect(Target, Range("C2:C2")) Is Nothing Then Exit Sub
> If Range("C2:C2") > 0 Then CUSTOMER
> End Sub
>
> now i wanted to add the following code to the above :
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Range("D2,G2")) Is Nothing Then
> With Target
> .Value = Application.Proper(.Value)
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> Any suggestions will be highly appreciated
> with best regards
> nowal

Hi Mr.Bob and Mr. JE McGimpsey,
Thanks for the quick response, but both are not working, i think some small mistake somewhere, so if u gets time, please can u please look through again in those code, i am not getting any error message but simply none of them are working thats all
once again thank u very much
regards
nowfal

Maybe try this

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Range("J1,K1,L1,P1,Q1,R1,U1,V1,W1"), Target) Is Nothing
Then
If Cells(Target.Row, "I") = "" Then
MsgBox "SORRY THE I COLUMN IS EMPTY FILL IT with P or S"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
ElseIf Not Intersect(Target, Range("D2,G2")) Is Nothing Then
With Target
.Value = Application.Proper(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub




--

HTH

RP
(remove nothere from the email address if mailing direct)


"nowfal" <nowfal.1strwl_1122462435.4747@excelforum-nospam.com> wrote in
message news:nowfal.1strwl_1122462435.4747@excelforum-nospam.com...
>
> Hi Mr.Bob and Mr. JE McGimpsey,
> Thanks for the quick
> response, but both are not working, i think some small mistake
> somewhere, so if u gets time, please can u please look through again in
> those code, i am not getting any error message but simply none of them
> are working thats all
> once again thank u very much
> regards
> nowfal
>
>
> --
> nowfal
> ------------------------------------------------------------------------
> nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
> View this thread: http://www.excelforum.com/showthread...hreadid=390337
>

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nowfal" <nowfal.1strwl_1122462435.4747@excelforum-nospam.com> wrote in
message news:nowfal.1strwl_1122462435.4747@excelforum-nospam.com...
>
> Hi Mr.Bob and Mr. JE McGimpsey,
> Thanks for the quick
> response, but both are not working, i think some small mistake
> somewhere, so if u gets time, please can u please look through again in
> those code, i am not getting any error message but simply none of them
> are working thats all
> once again thank u very much
> regards
> nowfal
>
>
> --
> nowfal
> ------------------------------------------------------------------------
> nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
> View this thread: http://www.excelforum.com/showthread...hreadid=390337
>

Hi Bob,
Two lines are missing in your latest code.That is so important.
ie.
If Intersect(Target, Range("C2:C2")) Is Nothing Then Exit Sub
If Range("C2:C2") > 0 Then CUSTOMER
End Sub

nowfal

Yeah, I didn't understand them (so I ditched them <g>).

Where do they figure in all this?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nowfal" <nowfal.1sue3m_1122491283.5475@excelforum-nospam.com> wrote in
message news:nowfal.1sue3m_1122491283.5475@excelforum-nospam.com...
>
> Hi Bob,
> Two lines are missing in your latest code.That is so
> important.
> ie.
> If Intersect(Target, Range("C2:C2")) Is Nothing Then Exit Sub
> If Range("C2:C2") > 0 Then CUSTOMER
> End Sub
>
> nowfal
>
>
> --
> nowfal
> ------------------------------------------------------------------------
> nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
> View this thread: http://www.excelforum.com/showthread...hreadid=390337
>

Perhaps this, although this is now suspiciously like JE's

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Range("J1,K1,L1,P1,Q1,R1,U1,V1,W1"), Target) Is Nothing
Then
If Cells(Target.Row, "I") = "" Then
MsgBox "SORRY THE I COLUMN IS EMPTY FILL IT with P or S"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
ElseIf Not Intersect(Target, Range("D2,G2")) Is Nothing Then
With Target
.Value = Application.Proper(.Value)
End With
ElseIf Not Intersect(Target, Range("C2:C2")) Is Nothing Then
If Range("C2:C2") > 0 Then CUSTOMER
End If

ws_exit:
Application.EnableEvents

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nowfal" <nowfal.1sue3m_1122491283.5475@excelforum-nospam.com> wrote in
message news:nowfal.1sue3m_1122491283.5475@excelforum-nospam.com...
>
> Hi Bob,
> Two lines are missing in your latest code.That is so
> important.
> ie.
> If Intersect(Target, Range("C2:C2")) Is Nothing Then Exit Sub
> If Range("C2:C2") > 0 Then CUSTOMER
> End Sub
>
> nowfal
>
>
> --
> nowfal
> ------------------------------------------------------------------------
> nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
> View this thread: http://www.excelforum.com/showthread...hreadid=390337
>

Hi Bob,
Done, lot of thanks , and i am personally appreciating bcoz, you are not giving up until a person like me to reach the goal. i got the correct code by changing on the third line and put the end sub.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Application.Intersect(Cells(Target.Row, 1) _
.Range("J1,K1,L1,P1,Q1,R1,U1,V1,W1"), Target) Is Nothing Then
If Cells(Target.Row, "I") = "" Then
MsgBox "SORRY THE I COLUMN IS EMPTY FILL IT with P or S"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
ElseIf Not Intersect(Target, Range("D2,G2")) Is Nothing Then
With Target
.Value = Application.Proper(.Value)
End With
ElseIf Not Intersect(Target, Range("C2:C2")) Is Nothing Then
If Range("C2:C2") > 0 Then CUSTOMER
End If

ws_exit:
Application.EnableEvents = True

End Sub

No point in giving up <g> Glad it's sorted.


Bob

"nowfal" <nowfal.1svmin_1122548760.7477@excelforum-nospam.com> wrote in
message news:nowfal.1svmin_1122548760.7477@excelforum-nospam.com...
>
> Hi Bob,
> Done, lot of thanks , and i am personally appreciating bcoz,
> you are not giving up until a person like me to reach the goal. i
> got the correct code by changing on the third line and put the end
> sub.
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Application.Intersect(Cells(Target.Row, 1) _
> Range("J1,K1,L1,P1,Q1,R1,U1,V1,W1"), Target) Is Nothing Then
> If Cells(Target.Row, "I") = "" Then
> MsgBox "SORRY THE I COLUMN IS EMPTY FILL IT with P or S"
> Application.EnableEvents = False
> Target.Value = ""
> Application.EnableEvents = True
> End If
> ElseIf Not Intersect(Target, Range("D2,G2")) Is Nothing Then
> With Target
> Value = Application.Proper(.Value)
> End With
> ElseIf Not Intersect(Target, Range("C2:C2")) Is Nothing Then
> If Range("C2:C2") > 0 Then CUSTOMER
> End If
>
> ws_exit:
> Application.EnableEvents = True
>
> End Sub
>
>
> --
> nowfal
> ------------------------------------------------------------------------
> nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
> View this thread: http://www.excelforum.com/showthread...hreadid=390337
>