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

Similar Articles

[PageSpeed] 15

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:

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

Data Entry very slow ... Slow to start WOrksheet_Change
I'm working on a workbook where the data entry is VERY SLOW. It didn't used to be, but today it is. I've changed calculation to MANUAL and it's no better. I've set EnableEvents = False and it's faster but still slow. It's just very slow to allow me to select a cell. FWIW, the worksheet has some calculations, but does not have a lot of calculated cells. It's currently filtered and it's taking a long time to allow me to select a filter. Has anyone seen this and what do I need to do to fix it. -- HTH, Barb Reinhardt ...

Programmatically Adding a Worksheet_Change Event Procedure to a Pr
I am using Excel 2003 and have added a script to add a Worksheet_Change Event to an Added Worksheet. I receive the following error: Error -2147417848 (80010108): The object invoked has disconnected from its clients. I have referenced the Microsoft Visual Basic for Applications Extensibility 5.3. I understand this script would run in the background and naming VBProject as an Object and using the CreateObject as Microsoft describes in Article ID: 319832 - Last Review: February 1, 2007 - Revision: 5.3 INFO: Error or Unexpected Behavior with Office Automation When You Use Ea...

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

Worksheet_change won't run
I am running Excel 2000. I have a spreadsheet that I am trying to add some data-mining instructions to the Worksheet_change macro. The spreadsheet is a means of tracking manufacturing progress and is linked to other spreadsheets. I have run a macro to make sure the Application.EnableEvents = True has been set. Yet the Worksheet_change macro will not fire when any cell on the sheet containing the macro is changed. Is there a switch I am not aware of? Any suggestions outside of 'give up and go home' will be much appreciated :). Please help. Thanks in advance. Where have you pu...

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

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

Can you have variations to Worksheet_Change event?
Sorry to repost....www problems and can't find a reply! Rob Is it possible to have separate or variations to the Worksheet_Change events for different procedures on the same sheet as I'm trying to do quite a number of things on the same sheet and it all becomes rather confusing doing it all under the one Worksheet_Change event. ( I am also using a Worksheet_SelectionChange event on this sheet.) Hi no you can't. You have to put all your code in one worksheet_change event. You may consider using a Select Case statement (if you haven't implemented it this way) -- Regards Fra...

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

Putting formulas in Private Sub Worksheet_Change(ByVal Target As Range)
Instead of dragging down your formulas the standard way on the spreadsheet itself. How would you inplement the code in the Worksheet itself. I'd like for... Once A3 has a date input, for all the formulas to be automatically pasted into that same row. Once A4 has a date and so on. "D" thru "K" all have formulas in them. Thanks in advance!! You could do that but might be easier if you use no code at all. In D1:K1 enter =IF($A3="","",your formula) Drag all down as far as you wish. Only have to do this once. Gord Dibben MS Excel MVP On Sun, ...

Using "Select Case" inside Worksheet_Change() subroutine.....
I would like to monitor when any of the data in my group of cells gets modified or deleted. Here is how my code looks: Private Sub Worksheet_Change (ByVal Target As Range) Select Case Target Case Range("A1") MsgBox ("A1 was just modified") Case Range "B1") MsgBox ("B1 was just modified") '..... etc. etc.. etc... Case Range ("M20") MsgBox ("M20 was just modified") Case Else ' Do nothing End Select End Sub Does anybody see anything wrong with this approach?? Is my code correct?? How can I code t...

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

use of Worksheet_Change(ByVal Target As Range)
Dear friends the following code of hoja2 hides a combined picture (drop down 3), when j3 is different from 1, and works perfectly, but when I want to hide another combined picture (drop drown 16) does not work. help please FUNCTIONAL Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("j3")) Is Nothing Then Exit Sub Me.DropDowns("Drop Down 3").Visible = CBool(Target.Value = 1) End Sub IT DOES NOT WORK Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count >...

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

Worksheet_Change Function
I am using the worksheet_change event to invoke logic when an updat occurs to a cell. I also use the worksheet_beforedoubleclick event to open a form an allow a user to select from a list of values. My problem is that if cancel out of the user form, I am placed into the cell where th doubleclick event was initiated. When I move off the cell, the chang event occurs even though I have not changed the value of the cell. I there a way to prevent this from happeing -- Message posted from http://www.ExcelForum.com Hi try adding the line Cancel = True in the worksheet_beforedoubleclick eve...

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

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") ...

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

cells with formula fire worksheet_change when navigating thru them
hi, i have cells that contain diff. formula and the first time i enter any one of them then leave it (eg. use arrow key to scroll across columns in a given row) the worksheet change event is fired. is this a bug because nothing has changed in the cell (perhaps some recalculate event has fired?). the reason the above noted behaviour is a problem is because in my worksheet_change event i execute an Application.Undo and this line of code throws an error because nothing there is nothing to undo because nothing has actually changed. i don't know how to test for whether there...

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

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

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