How to make two codes "Worksheet_Change" work together in same sheet code page

  • Thread starter Thread starter brunces
  • Start date Start date
B

brunces

Friends,

Please, anybody knows how to make these two VB codes work together?

When I put them together in the same "sheet code page" in VBA, th
second one doesn't work. Why?

*** Code 1 ***

Private Sub Worksheet_Change(ByVal Target As Range)
* * On Error GoTo QuitCode
* * If Intersect(Target, Range("c1:c15")) Is Nothing Then
* * * * Exit Sub
* * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then
* * * * MsgBox "You haven't typed the name of the client yet."
* * * * Target.Offset(0, -1).Activate
End If
QuitCode:
End Sub

*** Code 2 ***

Private Sub Worksheet_Change(ByVal Target As Range)
* * On Error GoTo QuitCode
* * If Intersect(Target, Range("c1:c15")) Is Nothing Then
* * * * Exit Sub
* * Else:
* * * * varSave = MsgBox("Do you want to save this document now?"
vbYesNo)
* * * * If varSave = vbNo Then GoTo QuitCode
* * * * ActiveWorkbook.Save
* * End If
QuitCode:
End Sub

**************

I tried to unite them like this... But it also didn't work, the text
become red.

*** Code 3 ***

Private Sub Worksheet_Change(ByVal Target As Range)
* * On Error GoTo QuitCode
* * If Intersect(Target, Range("a1:c15")) Is Nothing Then
* * * * Exit Sub
* * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then
* * * * MsgBox "You haven't typed the name of the client yet."
* * * * Target.Offset(0, -1).Activate
* * Else:
* * * * varSave = MsgBox("Do you want to save this document now?"
vbYesNo)
* * * * If varSave = vbNo Then GoTo QuitCode
* * * * ActiveWorkbook.Save
* * End If
QuitCode:
End Sub

**************

If anyone could tell me how make them work, I appreciate it. :)

Thanks a lot.

Hugz,

Brun
 
I think perhaps you should look up if then else
in the VBA Help (that means use F1 from within VBE).

The simple way is to remove the Exit Sub
and use the insides within one wrapper. But there is so
much common code that you really would want to rewrite it.

Those asterisks were not helping anything, you could enter
extra spaces if you are trying to make things indent more than
you would normally see in a proportional font.
If you just want to check that your are in Column C
If Target.column <> 3 then exit sub
Everytime you make a change to column C (actually c1:c15)
do you really want to be prompted to save.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo QuitCode
If Intersect(Target, Range("c1:c15")) Is Nothing Then Exit Sub
If Target.Value <> "" And Target.Offset(0, -1).Value = "" Then
MsgBox "You haven't typed the name of the client yet."
Target.Offset(0, -1).Activate
goto quitcode
End If
varSave = MsgBox("Do you want to save this _
document now?", vbYesNo)
If varSave = vbYes Then ActiveWorkbook.Save
QuitCode:
End Sub

*** Code 2 ***

Private Sub Worksheet_Change(ByVal Target As Range)
* * On Error GoTo QuitCode
* * If Intersect(Target, Range("c1:c15")) Is Nothing Then
* * * * Exit Sub
* * Else:
* * * * varSave = MsgBox("Do you want to save this document now?",
vbYesNo)
* * * * If varSave = vbNo Then GoTo QuitCode
* * * * ActiveWorkbook.Save
* * End If
QuitCode:
End Sub

**************

I tried to unite them like this... But it also didn't work, the texts
become red.

*** Code 3 ***

Private Sub Worksheet_Change(ByVal Target As Range)
* * On Error GoTo QuitCode
* * If Intersect(Target, Range("a1:c15")) Is Nothing Then
* * * * Exit Sub
* * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then
* * * * MsgBox "You haven't typed the name of the client yet."
* * * * Target.Offset(0, -1).Activate
* * Else:
* * * * varSave = MsgBox("Do you want to save this document now?",
vbYesNo)
* * * * If varSave = vbNo Then GoTo QuitCode
* * * * ActiveWorkbook.Save
* * End If
QuitCode:
End Sub
 
Back
Top