| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine 2 worksheet change events | Excel Discussion (Misc queries) | |||
| Worksheet Events | Excel Discussion (Misc queries) | |||
| Workbook and Worksheet Events | Excel Programming | |||
| Worksheet events | Excel Programming | |||
| Worksheet events | Excel Programming | |||
![nowfal[_23_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
