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

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

--
brunce

-----------------------------------------------------------------------
brunces's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=759
View this thread: http://www.excelforum.com/showthread.php?threadid=27491

0
11/3/2004 3:58:48 PM
excel 39879 articles. 2 followers. Follow

2 Replies
606 Views

Similar Articles

[PageSpeed] 8

Cuz you can only have one per worksheet so combine


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"brunces" <brunces.1f5k5z@excelforum-nospam.com> wrote in message
news:brunces.1f5k5z@excelforum-nospam.com...
>
> 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, the
> 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 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
>
> **************
>
> If anyone could tell me how make them work, I appreciate it. :)
>
> Thanks a lot.
>
> Hugz,
>
> Bruno
>
>
> -- 
> brunces
>
>
> ------------------------------------------------------------------------
> brunces's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=7598
> View this thread: http://www.excelforum.com/showthread.php?threadid=274917
>


0
Don
11/3/2004 4:31:57 PM
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
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"brunces" <brunces.1f5k5z@excelforum-nospam.com> wrote in message news:brunces.1f5k5z@excelforum-nospam.com...
>
> 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, the
> 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 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
>
> **************
>
> If anyone could tell me how make them work, I appreciate it. :)
>
> Thanks a lot.
>
> Hugz,
>
> Bruno
>
>
> -- 
> brunces
>
>
> ------------------------------------------------------------------------
> brunces's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7598
> View this thread: http://www.excelforum.com/showthread.php?threadid=274917
>


0
dmcritchie (2586)
11/3/2004 4:53:16 PM
Reply:

Similar Artilces:

Worksheet_Change() and range question 12-02-09
On my spreadsheet, I merged the cells N10, O10, and P10 into a single cell. I then put the following code in my Worksheet_Change() function: Private Sub Worksheet_Change(ByVal Target As Range) MsgBox CStr(Target.Address(0, 0)) End Sub Now, if you enter data into cell N10 on the spreadsheet, the message box will return with "N10". However, if you DELETE the contents of cell N10, then the message box will say "N10:P10". Why is this?? How can I tell the "Target" variable to just give me "N10" string??? (I guess I'm too l...

execute code in worksheet_change
Dear all, I need to execute some code in worksheet_change then I did it: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Call return_accounting_code End If If Not Intersect(Target, Range("A7:A65536")) Is Nothing Then Call return_accounting_code_description End If End Sub But it doesn't work, I think here we have like a "circular reference", because first I need to return only the accounting code (When I change the cell B3 (that contains the profit center) excel returns from access all accounting code for the profit ce...

Worksheet_Change Not Working
This code is not executing when I put a 1 or 2 in Cell A1. Isn't it suppose to? What have I done wrong? Thanks, Bernie Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$A$1" Then Select Case Target.Value Case Is = 1: MsgBox "A1 changed to 1" Case Is = 2: MsgBox "A1 changed to 2" End Select End If End Sub Hi have you put this code in your worksheet module? -- Regards Frank Kabel Frankfurt, Germany bw wrote: > This code is not executing when I put a 1 or 2 in Cell A1. Isn&#...

Problem with Worksheet_Change
I have a "Home" worksheet with a hyperlink to another hidden "Test" worksheet. The test is timed and will display "STOP" in cell "u1". In the sheet code module, I have a subroutine to recalculate so the time elapsed is updated: Private Sub Worksheet_SelectionChange(ByVal Target as Range) ActiveSheet.Calculate End Sub I want to use "STOP" as the trigger to return to "Home" worksheet which will display the test results. I've read some of the other postings and tried the procedures but it doesn't work. If this isn't...

Worksheet_Change when a point is moved on a graph
Hi all, just a quick question: Does someone know how can I make the Worksheet_Change routine automatically activates for a changing on a data series in a plot? I mean: I 've a data series on a graph, I use the mouse pointer to move one of the points, I need that for such an event some routines automatically run. Thanks ...

Fire Worksheet_change event from module
Hello, Is there a way to trigger a worksheet_change event from code placed in a regular module and not in the sheet itself? Or what is the approach to trigger a worksheet change event when I don't know in advance which sheet will be involved and don't want to insert code at each created sheet? Thanks a lot Avi Change Private to Public, eg Public Sub Worksheet_Change(ByVal Target As Range) and to call it from elsewhere in your project, eg Sheet1.Worksheet_Change Range("A1") Worksheets("Sheet1").Worksheet_Change Range("A1") ...

Problems with "Worksheet_Change"
Hello, I have noticed some problems with the function "Worksheet_Change" between different MS Excel versions. Personally, I have the version: MS Excel 2000 (9.0.6926 SP-3) and in this version there is NO problem. A customer, though, works with the two versions: MS Excel 2002 SP-1 and MS Excel 2003 SP-1 and he has noticed that when he selects a cell and changes it, the focus (the frame is marked) wil not be moved to the next cell after the change (and ENTER). It is strange but it works perfectly fine in the older version. Has anyone else experience...

Worksheet_Change for a Range?
Column B (or a range within Column B) has a Data Validation List, that can be one of 4 values (Test1, Test2, Test3, Test4). When I change a cell in column B, I want to know which Row has been changed. The following works when I change Row 2. How do I make it work for the specific cell that was changed? Thanks, Bernie Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim C, R C = 2 R = 2 If ActiveSheet.Cells(R, C).Value = "" Or IsEmpty(ActiveSheet.Cells(R, C).Value) Then MsgBox "Delete the User Template" Else If ActiveSh...

Worksheet_Change
Hi all, In sheet1 I have numbers in column A, the same numbers in sheet2 column B. I want that doubleclicking on a number in column A of sheet1 results in going to the cell of sheet2, column B that is right of the cell in column B that has the same number in it (without highlighting anything) If I click on the tab of sheet1 I should see the cursor still on the cell where I doubleclicked, nothing highlighted. I thought the following code would work, but it doesn't. It stops at Sheets("Blad2").Columns("B:B").Select with an errormessage that says that the method select w...

Need help merging two Worksheet_Change modules
I have been give the two modules below, both of which perform some validation on certain cells. The first one stops anybody from trying to remove set data validation by way of pasting into the cell (one of data validation's flaws) the second one stops anybody from deleting or leaving a cell blank when they should be selecting a value from a list. As you can see, the first part of each module starts "Worksheet_Change" so VB doesn't like this. How do I go about merging both my modules so it works? thanks _First_Module_ Private Sub Worksheet_Change(ByVal Target As Range) &...

Need help merging two Worksheet_Change modules #6
Hi Dave, I am afraid this does not work. I pasted all your code into the modul (over the top of the old code) but only the INVALID part works. Th pasting over validation does not and the code regarding 1 cell at time stops me from including one validated cell I have which spans tw cells (merged) I really don't know why this doesn't work. Thank -- fuzzyfrea ----------------------------------------------------------------------- fuzzyfreak's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1465 View this thread: http://www.excelforum.com/showthread.php?...

Need help merging two Worksheet_Change modules #5
Hi Dave, further to the last entry I have discovered where the module is tripping up. It works on three of my cells but any one of a further three cells that I have data validation in renders the code not to work - what it does is, on any change to any cell with validation it throws up the message box in the code (MsgBox "Your last operation was canceled. " & _ "It would have deleted data validation rules.") and doesn't allow you to do anything else in those cells. What is it about these particular three cells that causes this? They are pretty normal validated c...

Need help merging two Worksheet_Change modules #4
The message I am referring to is the "You have an invalid entry, pleas try again." Message. You OK this and the cell entry has changed t invalid but you can then click away from this cell and leave �invalid in the cell � I don�t want them to be able to come away from the cel unless they have successfully selected a valid entry from the list. I removed A6 and instead used a named range because I have a few Dat Validation cells which I want to include in the module. Further to this though, my biggest problem is that though I got this t work on a fresh sheet, I then copied it into m...

Endless looping with Worksheet_change event (XL2003)
I have a data validation list on Sheet1. I have code that needs to trigger each time that cell selection is changed. No problem so far. When the value changes, I use that value to find corresponding records on sheet3, and copy them into Sheet1 in the desired columns. The base code seems to run pretty fast (when I run with breakpoints), but when I run the whole thing, the machine locks up and I get an 'out of memory' error. Is suspect it is because each time I paste in a cell value, this same sub (worksheet_change) is retriggered. Is there a way to temporarily block wo...

How to make two codes "Worksheet_Change" work together in same sheet code page
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: ...

Worksheet_Change Event
Hi, In an attempt to protect my data validation from being deleted, I hav written the following code. However, it seems to run into a 1004 erro and a circular reference that Excel cannot list. Does anybody kno where I am going wrong? More importantly, is this the right approach? code: -------------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim DataRange As Range Dim c As Range Dim mycount As Integer Worksheets("Sheet1").Unprotect "123456" If Range("F12:G21").SpecialCells(xl...

Need help merging two Worksheet_Change modules #2
Thanks Dave, this is almost exactly what I want! The only thing I nee in the module now is that when they try to delete a validated cell an the INVALID appears, I want the cursor to return to that cell to forc them to select from the list - at the moment they can continue leavin INVALID as the value. Perhaps a message box telling them what the have done wrong would also be useful? Thanks in advance Fuzz -- fuzzyfrea ----------------------------------------------------------------------- fuzzyfreak's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1465 View thi...

Workbook locks up after Worksheet_Change operation
Column "D" is subtracted from "E" the previous total and simulaneously "F" is added to the mix whenever columns "D,E or F is changed. Private Sub Worksheet_Change(ByVal Target As Range) Dim oTarget As Range Dim oIntersect As Range Set oTarget = Range("D1:F100") Set oIntersect = Application.Intersect(oTarget, Target) If Not oIntersect Is Nothing Then With Sheets("Sheet1") For n = 2 To 100 .Cells(n, 5) = .Cells((n - 1), 5) + .Cells(n, 6) - .Cells(n, 4) ...

More then 1 Private Sub Worksheet_Change
HI, can i have ore then 1 "Worksheet_Change" sub? i.e I havea sub, Private Sub Worksheet_Change but, Private Sub Worksheet2_Change will not work -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200912/1 No, you will just have to combine the code you want in your two Change events into the Change event that VB provides. You can use If..Then blocks to separate the functionality you wanted in your proposed separate Change events. -- Rick (MVP - Excel) "FIRSTROUNDKO via OfficeKB.com" <u1563...

How to Stop Worksheet_Change event from running during code execution
I'm sorry If I've asked this before but I can't find an answer to it. I have the following 2 events in a worksheet module.... Private Sub Worksheet_Change(ByVal Target As Excel.Range) Private Sub Worksheet_SelectionChange(ByVal Target As Range) I run some codes, either in UserForms or general procedures that make changes to this worksheet. Q. Is there a way to by-pass those two events somehow? As an example I have a uf that comes up if a cell is selected in that sheet. So I need those worksheet_Change events to work at that point. But then, the uf does some stuff like inputing ...

Need help merging two Worksheet_Change modules #3
OK, I have managed to get this far. It all works other than after th messaage appears and it selects the cell again, you can click away fro the cell - Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo errHandler: 'Does the validation range still have validation? If HasValidation(Me.Range("ValidationRange")) = False Then With Application .EnableEvents = False .Undo End With MsgBox "Your last operation was canceled. " & _ "It would have deleted data validation rules.", vbCritical Else If Intersect(Target, Me.Range("Va...

Worksheet_Change?
Hi, I'm trying to automatically open a Form when a target cell contains an exact text. Example: IF cell A1 says: "Oranges" THEN Open Form1. I've only opened Forms using command buttons. I was thinking maybe using Sub Worksheet_Change to do this? If cell A1 says anything else, (false) Do not open Form1. Thank-you in advance Amy Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("A1"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Va...

lookup in worksheet_change event
Hi All Worksheet1 contains below columns ( price list ) Company season price Worksheet 2 contains sales Company season amount price cost Company and season are picked from another list. What I want is ( if possible ) when I put amount price is picked from sheet1 and multiply with amount for cost. I know this too much but how can I put such formulas in worksheet_change event. Can you direct me to such postings. Thank you in advance open event code for SHEET 2 and try this code Private Sub Worksheet_Change(ByVal Target As Range) Dim rng, ref As Range Application.EnableEve...

Worksheet_Change() and Range question 12-12-09
If someone highlights a block of cells and presses "delete" to delete all the data in those cells, Excel will pass all those changed cell addresses to "Worksheet_Change()" into the "Target" variable. My question is, how do I enumerate through all the cells contained in the "Target" range variable?? The "Target" variable seems to contain a single string of all the cell addresses that were changed, and I'm looking for an easy way to extract each cell address from the "Target" variable. I initially thought I could use t...

worksheet_change becoming active just by entering the cell
I have the following code in my worksheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' Watching for changes in cells {H,L,O,T}{2,5,8,...} If (Target.Cells.Count = 1 And _ Target.Row Mod 3 = 2 And _ Target.Column Mod 4 = 0 And _ Target.Column >= 8 And _ Target.Column <= 20) Then If (Target.Value > 0) Then On Error GoTo ErrHandler Application.EnableEvents = False Call CheckDosage(Target) ErrHandler: Application.EnableEvents = True ...