Worksheet_Calculate code in same module as Worksheet Change event goes astray

Hi, I'm trying to get some code to fire, based on a formula in cell E2 
changing value to equal 1.

Private Sub Worksheet_Calculate()
    If Cells(5, 2) = 1 Then MsgBox "Fires ok"
   'Application.EnableEvents = False
End Sub

I can get it to work properly on a blank spreadsheet. However I need to 
use it on a worksheet that already has a Worksheet Change code. Since it 
seems like each worksheet can only have one module (page? not sure of 
the right name) for code, the above code is in the same module as the 
Change code.

Thus, when the entry is made that changes E2 to equal 1, it starts in 
the above code, then jumps to the Change code and begins running some of 
that code. If I EnableEvents=False it stops the straying, but still 
doesn't bring up the MsgBox.

Any help would be appreciated.
Harold
0
Harold
12/18/2009 4:59:00 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
958 Views

Similar Articles

[PageSpeed] 54

Try your code this way...

  If Cells(5, 2) = 1 Then
    Application.EnableEvents = False
    MsgBox "Fires ok"
    Application.EnableEvents = True
  End If

-- 
Rick (MVP - Excel)


"Harold Good" <hcgood@hotmail.com> wrote in message 
news:eUtbmNAgKHA.6096@TK2MSFTNGP02.phx.gbl...
> Hi, I'm trying to get some code to fire, based on a formula in cell E2 
> changing value to equal 1.
>
> Private Sub Worksheet_Calculate()
>    If Cells(5, 2) = 1 Then MsgBox "Fires ok"
>   'Application.EnableEvents = False
> End Sub
>
> I can get it to work properly on a blank spreadsheet. However I need to 
> use it on a worksheet that already has a Worksheet Change code. Since it 
> seems like each worksheet can only have one module (page? not sure of the 
> right name) for code, the above code is in the same module as the Change 
> code.
>
> Thus, when the entry is made that changes E2 to equal 1, it starts in the 
> above code, then jumps to the Change code and begins running some of that 
> code. If I EnableEvents=False it stops the straying, but still doesn't 
> bring up the MsgBox.
>
> Any help would be appreciated.
> Harold 

0
Rick
12/18/2009 5:31:14 PM
For some reason this did not work. It works fine on an independent 
worksheet with standalone code, but not when entered as a separate 
procedure below the Worksheet_Change procedure. It still jumps up into 
that other code, and does not bring up the msgbox.

Thanks for any further thoughts you may have.
Harold

Rick Rothstein wrote:
> Try your code this way...
>
>  If Cells(5, 2) = 1 Then
>    Application.EnableEvents = False
>    MsgBox "Fires ok"
>    Application.EnableEvents = True
>  End If
>
Harold Good wrote:
> Hi, I'm trying to get some code to fire, based on a formula in cell E2 
> changing value to equal 1.
>
> Private Sub Worksheet_Calculate()
>    If Cells(5, 2) = 1 Then MsgBox "Fires ok"
>   'Application.EnableEvents = False
> End Sub
>
> I can get it to work properly on a blank spreadsheet. However I need 
> to use it on a worksheet that already has a Worksheet Change code. 
> Since it seems like each worksheet can only have one module (page? not 
> sure of the right name) for code, the above code is in the same module 
> as the Change code.
>
> Thus, when the entry is made that changes E2 to equal 1, it starts in 
> the above code, then jumps to the Change code and begins running some 
> of that code. If I EnableEvents=False it stops the straying, but still 
> doesn't bring up the MsgBox.
>
> Any help would be appreciated.
> Harold 
0
Harold
12/18/2009 6:13:22 PM
What do you mean "entered as a separate procedure below the Worksheet_Change 
procedure"? You have to incorporate my posted code into your existing 
Worksheet_Change procedure's code... where might depend on when you need it 
to execute relative to the other code you have. Without seeing the rest of 
your code, it is kind of hard to give you any more direction than this.

-- 
Rick (MVP - Excel)


"Harold Good" <hcgood@hotmail.com> wrote in message 
news:%23kDQK3AgKHA.3552@TK2MSFTNGP06.phx.gbl...
> For some reason this did not work. It works fine on an independent 
> worksheet with standalone code, but not when entered as a separate 
> procedure below the Worksheet_Change procedure. It still jumps up into 
> that other code, and does not bring up the msgbox.
>
> Thanks for any further thoughts you may have.
> Harold
>
> Rick Rothstein wrote:
>> Try your code this way...
>>
>>  If Cells(5, 2) = 1 Then
>>    Application.EnableEvents = False
>>    MsgBox "Fires ok"
>>    Application.EnableEvents = True
>>  End If
>>
> Harold Good wrote:
>> Hi, I'm trying to get some code to fire, based on a formula in cell E2 
>> changing value to equal 1.
>>
>> Private Sub Worksheet_Calculate()
>>    If Cells(5, 2) = 1 Then MsgBox "Fires ok"
>>   'Application.EnableEvents = False
>> End Sub
>>
>> I can get it to work properly on a blank spreadsheet. However I need to 
>> use it on a worksheet that already has a Worksheet Change code. Since it 
>> seems like each worksheet can only have one module (page? not sure of the 
>> right name) for code, the above code is in the same module as the Change 
>> code.
>>
>> Thus, when the entry is made that changes E2 to equal 1, it starts in the 
>> above code, then jumps to the Change code and begins running some of that 
>> code. If I EnableEvents=False it stops the straying, but still doesn't 
>> bring up the MsgBox.
>>
>> Any help would be appreciated.
>> Harold 

0
Rick
12/18/2009 6:20:35 PM
You're right, I don't really know what proper terms to call things. I'll 
insert the code here.

On the Project Explorer both the procedures below (Worksheet_Change, and 
Worksheet_Calculate) are in the Sheet 5 (Categories) Object. The bottom 
one (Worksheet_Calculate)  is the problem one. I've set a trap to stop 
it on the /"If Cells(5, 2) = 1 Then MsgBox "Fires ok"/" line, then I F8 
step thru it. When it finishes the End Sub, it jumps to the top of this 
Worksheet_Change code and starts going thru it. So I'm trying to get it 
to work properly, then build code around it, ultimately so if it equals 
1, then unhide some rows.      Thanks again for your kind help.  Harold

Private Sub Worksheet_Change(ByVal Target As Range)
'Code below catches any changes made to the green
'table on the Categories page. When any change is made
'the code is triggered and it hides the unused rows of the
'budget on the Budget page.
Dim r As Range, cell As Range
Set t = Target
ActiveSheet.Unprotect Password:="budg"
Range("E10:N29").Interior.Color = RGB(213, 255, 215)
Range("F10").Interior.Color = RGB(255, 255, 189)
Range("E10:N29").Locked = False
Range("F10").Locked = True
If Not Intersect(t, Range("E10:N29")) Is Nothing Then
Dim rngEval As Range
Dim rngHide As Range
Dim rngCell As Range
Set rngEval = Sheets("Budget").Range("BudgetRowsForHiding")
Application.ScreenUpdating = False
For Each rngCell In rngEval.Cells
    If rngCell.Value = "" Then
        If rngHide Is Nothing Then
            Set rngHide = rngCell
            Debug.Print rngHide.Address

        Else
            Set rngHide = Union(rngHide, rngCell)
            Debug.Print rngHide.Address

        End If
    End If
Next rngCell

'Debug.Print rngHide.Address
rngEval.RowHeight = 12.75
Sheets("Budget").Outline.ShowLevels RowLevels:=1
'Because Hidden rows do not remain hidden on the Budget page when I
'expand the Outline, the only other way to make unused rows remain
'hidden is to use a rowheight of .6. This keeps these unused rows
'out of sight while keeping them also out of sight when expanding
'the Outline on Budget page.
Sheets("Budget").Unprotect Password:="budg"

'If there are 30 Account Categories, then don't do the rngHide below.
If rngHide Is Nothing Then
Else
rngHide.RowHeight = 0.6
End If

End If
Sheets("Budget").Shapes("Group Charts").Visible = True
Sheet1.Select
Sheet1.Range("NotesRows").Select
Selection.EntireRow.Hidden = False
Sheets("Budget").Range("H7").Select
Sheet5.Select
Sheets("Budget").Protect Password:="budg"
Application.ScreenUpdating = True
Application.EnableEvents = True
'ActiveSheet.Protect Password:="budg"
End Sub

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    If Cells(5, 2) = 1 Then MsgBox "Fires ok"
    Application.EnableEvents = True
End Sub



Rick Rothstein wrote:
> What do you mean "entered as a separate procedure below the 
> Worksheet_Change procedure"? You have to incorporate my posted code 
> into your existing Worksheet_Change procedure's code... where might 
> depend on when you need it to execute relative to the other code you 
> have. Without seeing the rest of your code, it is kind of hard to give 
> you any more direction than this.
>
0
Harold
12/18/2009 6:59:11 PM
Reply:

Similar Artilces:

Lookup Codes
We are having some difficulties determinig a strategy for lookup codes. Specfically, we are trying to determine the best way to configure lookup codes for scalability, ease of data entry, as well ease of use at cash out. The customization for my POS system is a clothing boutique. We have scattered inventory, different venders from season to season, and limited stock for the items we do carry. I really wondering if its better to simply make lookup codes numeric, and print them out on our price tags. Is there some better strategy or approach to handling lookup codes? This is a multi...

List of Codes and Display Names for Codes #2
thanks that seems to be working thank you .... :) -- hoganc ------------------------------------------------------------------------ hoganc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14980 View this thread: http://www.excelforum.com/showthread.php?threadid=266031 ...

where to find mfc datagrid samples/code for vc++ 2005
Does anyone know where I can find some datagrid samples and code for MS VC++ 2005 using MFC and unmanaged code? I don't want older VC++ samples (ie 6.0 or .net 2003). support.microsoft.com/?kbid=229029 you should be able to add the functionality to your project after reading the sample. -- Regards Sheng Jiang Microsoft Most Valuable Professional in Visual C++ http://www.jiangsheng.net http://blog.joycode.com/jiangsheng/ "arch" <tony_ooooo@yahoo.com> д���ʼ� news:1157294966.36437@angel.amnet.net.au... > Does anyone know where I can find some datagrid samples and ...

VBA code go to specific location in embedded document
From Excel I'm opening an embedded OLE object (Word document). Once the document is open, I need to be able to go to a location in the document (preferably by keyword search). Is this possible? Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) If Target.Range.Address = "$A$4" Then Set Obj = Sheets("report").OLEObjects("Object 1") Obj.Activate End If End Sub ...

PivotTable 2003 Code is not working
HI guys, I have added code to show all items in my pivottable when a bitmap is clicked. The code needs to first show all items in the pivottable and then go back to the scorecard sheet. THe code to show all itmes does not work at all. The hyperlink works fine. I am hoping this code will solve another problem. On the scorecard, I have a button to click that filters the pivottable for a specific page item and specific row item. If they go back to the scorecard and choose another filter choice, it does not filter properly. So I thought if each time they go back to the score...

Worksheet_Calculate
I need to use comboboxes to update several pivot tables. What event should i use to loop thru the changes to the comboboex and update the respective fields of the different pivot tables? I'm trying Worksheet calculate but it will keep going even though if i turn the diableevents to false Thanks in advance Isn't diableevents a typo? Maybe you meant Application.EnableEvents -- Regards! Stefi „LuisE” ezt írta: > I need to use comboboxes to update several pivot tables. What event should i > use to loop thru the changes to the comboboex and update ...

Order form
I would like to crete an order form that once you add the code of the product all the other information will appear eg Price, description, colour and so on... I have seen another programme that did it but cant' find any information on how to do it.. Thanks in advance for your help :) -- mich72 ------------------------------------------------------------------------ mich72's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19280 View this thread: http://www.excelforum.com/showthread.php?threadid=468455 Hi mich72 Look in the help for the Vlookup function If...

changing email mscrm
hi, I've the changed the email adresses in the user profile and now i dont get any mail - not in the crm and not even in outlook, ive added a new user with the same email definitions and didnt add the user to the crm and with this user i got mail. what should i do. I guess my simple question is how to change to users email adresses? tnx Hi, Make sure you have changed the mail address everywhere. In the Active Directory, in CRM (if it is not updated). After that, re-run the CRM Exchange Routing wizard for you user. If it still doesn't work. Remove your user from CRM and add it as...

How to activate a quote in code?
How should I activate a quote in code using the sdk? Does anyone have a code sample? Hi Purple, Use the SetStateQuote request to activate a quote. Sample code exists in the sdk for changing the state of account. Modify that to suit your pupose. Hope this helps! Sam _______________ Inogic Innovative Logic Innovative solutions for your SME ERP/CRM products E-mail: crm@inogic.com Web: www.inogic.com -------------------------- "Purple" <Purple@discussions.microsoft.com> wrote in message news:68BA049B-8805-43F1-98FE-EC0C65256589@microsoft.com... > How should I activate a...

Search Code Problem
I have a search that I would like to have run from three tables. 'strSQL = "SELECT tblHouses.RollNumber, tblHouses.PID, tblHouses.FirstOfHouseAddress, tblAccounts.AccountNo, tblListNos.MainListNo1, tblListNos.MStatus, tblListNos.ProjectID, tblHouses.Strata, tblHouses.Inactive, tblHouses.FirstOfPostalCode, tblHouses.FirstOfHouseNumber, tblHouses.FirstOfRoadName, tblHouses.LotNo, tblHouses.Telephone1, tblHouses.Telephone2, tblListNos.MPriority, tblListNos.MaintenanceComments, tblListNos.MainIssuedDate " _ '& "FROM (tblHouses LEFT JOIN tblListNos ON tblHouses.R...

Outlook changing 'g' to 'q' when replying
Hi, When I reply to a message from a user anna.aguilarbiggs@domain.com Outlook changes the 'g' to a 'q' so that the reply address is anna.aquilarbiggs@domain.com. Does anyone have any idea why this might be happening? Thanks for your advice Shaun <nospam@nospam.com> wrote: > When I reply to a message from a user anna.aguilarbiggs@domain.com > Outlook changes the 'g' to a 'q' so that the reply address is > anna.aquilarbiggs@domain.com. Does anyone have any idea why this > might be happening? Since it doesn't touch the Gs in "...

code 80070020
when i update KB972145 i am getting: installation status failed error details: Code 80070020 i have tried disabling the "Real Time" thing and still gives me problems i am able to update everything else but KB972145 has anyone had this problem and was able to resolve it? Does anybody have any ideas on resolving this?? The update is meant to address the 2 scenarios shown here: http://support.microsoft.com/kb/972145 If neither of them exist on your system, suggest you just hide the update instead of running around in circles. If they do exist then please, *...

i-Phone pix default to Publisher, how to change?
I must have chosen the 'do this every time' box. How do I get back to the multiple option menu and eliminate Publisher as the default? Thanks. Change your file associations. How to change file associations in Windows XP http://support.microsoft.com/?kbid=307859 How do I... Change file extension associations in Windows Vista? http://articles.techrepublic.com.com/5100-10878_11-6172036.html?part=rss&tag=feed&subj=tr -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "WSuman" <WSuman@discussions.mic...

Command Button disappears from worksheet
I copied an excel workbook that had command buttons on the worksheets to a co-worker's computer. I transferred the file on a jump drive (also tried emailing it using Novell Groupwise). When the co-worker opened the workbook, the buttons momentarily displayed, them disappeared. Is there some option that needs to be set in Excel for the buttons to show. I created the buttons using the control box toolbar. Is there a difference between that and the controls on the Forms toolbar? -- SLB ...

Change mailbox name and X.400 address replication
Hello. We are using Exchange 5.5 SP4 on NT 4 SP6A. How does X.400 addresses work when mailbox name changes?. When A mailbox is created for a user with name "Steve Petersson" the mailbox X.400 address gets replicated to a central place with other mailservers so every mailserver sees this account. If this person change the name to "Steve Fendersson" and the mailbox X.400 Surname are changed to the Fendersson. Will this X.400 adress be uppdated on the central server?. Or do I have to delete the mailbox and create a new mailbox with new name?. //Anders Andersson &...

Integrating Pay Codes
Hi I'm trying to set up a data conversion for a payroll client. At this point in my testing, when I try to integrate the pay codes, I will sometimes get an error that the code needs a SUTA state. I am pulling the SUTA state from the source file and in some cases it does work. Any ideas as to what it is REALLY looking for? Thanks! -- Debbie from Wipfli ...

How do I selectively import data from another Excel worksheet?
I have a long database, that contains complete leads data with many columns. I have another, shorter database that contains no data except one common field between the two. I want to fill-in the info from the large database into the smaller database. I'd sure appreciate help! Thanks. Take a look at the vLookup worksheet function. HTH, -- Gary Brown gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com "Emmanuel" wrote: > I have a long database, that contains complete leads data with many columns. > I have another, shorter database that contains no data except one common...

Change mortgage / loan payment frequency?
I have money 2006, how do you change a loan (mortgage) payment frequancy from monthly to bi-weekly? Only way I have found is to have two monthly payments called something like "mortgage pay #1" and "mortgage pay #2". -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Jeff G" <Jeff G@discussions.microsoft.com> wrote in message news:ADDBF118-C8B8-46AB-8C96-48322EABBE17@microsoft.com... >I have m...

How to Auto UPC codes?
The company I work for purchased RMS about 6 months ago and nothing has been setup. In fact, I think I'm the 1st person to actually use the system. The company deals in retail furniture and our problem is that a majority of our merchandise doesn't have a UPC code. I've entered about 1,000 items with random codes, but it's taking too long. Is there any way to get the system to automatically make a UPC code for new merchandise? Kind of like how the account numbers will automatically use the next number in line? Thank You, -Jayson I can set this up for you. Contact me off...

BP Project Timesheet Changes
Is there a way to modify the Cost Category of project time after it has been submitted & approved in Business Portal? Hi Adam, Since it is approved it cannot be updated.I hope you have not posted the Timesheet In Dynamics GP? If it is not posted you can open the Timesheet it in GP and modify accordingly. Lets wait for others input on this. -- Thanks, Shan "Adam" wrote: > Is there a way to modify the Cost Category of project time after it has been > submitted & approved in Business Portal? The timesheet is approved in BP & not posted in GP. When I tried ...

Sales Module and later Service Module on the same Database
If possible to install the sales licence ... use it... and later install the other module using the same database?? When you install CRM you can add any license (sales, service or suite), you can also remove or add licenses as needed. You will be using the same databases regardless of your license type. Thanks - Annie >-----Original Message----- >If possible to install the sales licence ... use it... and later install the >other module using the same database?? >. > That is true. But when you install the server... you use a licence that it's different between Sales...

Folder List
How do you change the font size in the folder list in outlook? you don't unless you change it in Windows, which affects all programs. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips mailing list: http://www.onenote-tips.net/ "jch" <JCH@nospam.com> wrote in message news:Hgsjd.50653$...

error code COOD10D9
I have this error code COOD10D9 cannot detect length of file, a few files have no info on them anymore just song title, any suggestions? also when I click these on to play they play fine What's the file type (extension) of these files (MP3, WMA, WAV...)? Regards -- Tim De Baets http://www.bm-productions.tk analogtape2 wrote: > I have this error code COOD10D9 cannot detect length of file, a few files > have no info on them anymore just song title, any suggestions? also when I > click these on to play they play fine ...

DBA Job Transition / Change
After working for years as a DBA for my company I would like to transition to another position within the corporation. The company has a large IT department, however I'm not sure what I can transition to being a DBA. I'd like to hear from other DBA's out there that have transitioned to other jobs within or outside their company and if they successfully made the transition. What did you transition to? What IT jobs would a DBA qualify? Thank you very much in advance!! -- Message posted via http://www.sqlmonster.com ...

How change MS mailing list address? MS login fails to recognize old--or new--address
Without signing up for Microsoft's Passport, how do I get the company to change my email address on its mailing list? At the bottom of the email messages I get from MS, it has a link to subscription changes. When I get there and attempt to login, it does not recognize my email address/password combination. So I entered my old email address to which MS is still sending its list mail and requested that my password be sent to me. It does not recognize the address after I type in the several characters the window displays. If I type in my new email address, the same thing happens I'm ...