combine worksheet events

[nowfal]

Hi,
I have already 2 worksheet events in one sheet and i wanted to ad
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
nowa

 

[Bob Phillips]

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)

 

[JE McGimpsey]

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

 

[nowfal]

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

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

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]

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

nowfa

 

[Bob Phillips]

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)

 

[Bob Phillips]

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]

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.
got the correct code by changing on the third line and put the en
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 Su

 

[Bob Phillips]

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


Bob