combine worksheet events
 

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

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=39033

combine worksheet events
 

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" wrote in
message ...

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

combine worksheet events
 

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

combine worksheet events
 

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

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=39033

combine worksheet events
 

--

HTH

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


"nowfal" wrote in
message ...

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

combine worksheet events
 

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" wrote in
message ...

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

combine worksheet events
 

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

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=39033

combine worksheet events
 

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" wrote in
message ...

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

combine worksheet events
 

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" wrote in
message ...

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

combine worksheet events
 

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

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=39033

combine worksheet events
 

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


Bob

"nowfal" wrote in
message ...

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