worksheet_calculate event changes worksheets, but shouldn't

Hi,

The code below is in the Sheet1 (Budget) Module: The user is entering 
data into the "Budget" worksheet. When this fires, it should hide some 
rows on the Categories worksheet. It does work ok, however, everytime a 
user enters a new figure on the Budget worksheet, it fires and 
momentarily flashes over to the Categories worksheet. It is quite 
distractive, especially when one has hundreds of figures to enter.

I set a trap to stop it on the top line of this code - the Private Sub 
Worksheet_Calculate() line.

Then, when I enter a new number on the Budget page, it fires, and stops 
on this top line of code. At that point, before any of the code below 
has run, the screen has already flashed over to the Categories page and 
waits there until I hit F8. Then when I hit F8 twice, after it executes 
the second line (screen updating = false), then it switches back to the 
Budget worksheet where it should be and remains there.

To be more precise, when it flashes to the Categories worksheet, what 
actually shows on the screen is:
Top half of screen is the Budget page
Bottom half of screen is the Categories page.
It's almost like when a partial refresh happens sometimes. The Budget 
tab is still active, not the Categories tab.

I'd surely appreciate any ideas you may have to stop this flashing 
over.  Thanks, Harold

Private Sub Worksheet_Calculate()
'this hides the message rows on Categories page if Budget Total is 
greater than 1
    Application.ScreenUpdating = False
    Sheets("Categories").Unprotect Password:="xxx"
    Application.EnableEvents = False
    If Sheets("Budget").Range("J111").Value > 1 Then
    Sheets("Categories").Rows("32:33").EntireRow.Hidden = False
    Else
    If Sheets("Budget").Range("E30").Value = 0 Then
    Sheets("Categories").Rows("32:33").EntireRow.Hidden = True
    End If
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = False
    Sheets("Categories").Protect Password:="xxx"
End Sub
0
Harold
12/23/2009 11:05:58 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
894 Views

Similar Articles

[PageSpeed] 6

I re-created your file and put the macro in another sheet module and did not 
see a problem although I would have written it differently

You are not resetting screen updating to True at the end. Do you really want 
this to fire on EVERY calculation or just when you do____________
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Harold Good" <hcgood@hotmail.com> wrote in message 
news:ueaA%23RChKHA.3792@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> The code below is in the Sheet1 (Budget) Module: The user is entering data 
> into the "Budget" worksheet. When this fires, it should hide some rows on 
> the Categories worksheet. It does work ok, however, everytime a user 
> enters a new figure on the Budget worksheet, it fires and momentarily 
> flashes over to the Categories worksheet. It is quite distractive, 
> especially when one has hundreds of figures to enter.
>
> I set a trap to stop it on the top line of this code - the Private Sub 
> Worksheet_Calculate() line.
>
> Then, when I enter a new number on the Budget page, it fires, and stops on 
> this top line of code. At that point, before any of the code below has 
> run, the screen has already flashed over to the Categories page and waits 
> there until I hit F8. Then when I hit F8 twice, after it executes the 
> second line (screen updating = false), then it switches back to the Budget 
> worksheet where it should be and remains there.
>
> To be more precise, when it flashes to the Categories worksheet, what 
> actually shows on the screen is:
> Top half of screen is the Budget page
> Bottom half of screen is the Categories page.
> It's almost like when a partial refresh happens sometimes. The Budget tab 
> is still active, not the Categories tab.
>
> I'd surely appreciate any ideas you may have to stop this flashing over. 
> Thanks, Harold
>
> Private Sub Worksheet_Calculate()
> 'this hides the message rows on Categories page if Budget Total is greater 
> than 1
>    Application.ScreenUpdating = False
>    Sheets("Categories").Unprotect Password:="xxx"
>    Application.EnableEvents = False
>    If Sheets("Budget").Range("J111").Value > 1 Then
>    Sheets("Categories").Rows("32:33").EntireRow.Hidden = False
>    Else
>    If Sheets("Budget").Range("E30").Value = 0 Then
>    Sheets("Categories").Rows("32:33").EntireRow.Hidden = True
>    End If
>    End If
>    Application.EnableEvents = True
>    Application.ScreenUpdating = False
>    Sheets("Categories").Protect Password:="xxx"
> End Sub 

0
Don
12/23/2009 11:29:31 PM
Reply:

Similar Artilces:

How do I retrieve changes to an unsaved excel document
I closed a excel document without saving changes. Is there a way to retrieve my old worksheet which would have the changes I made. I'm using excel 2000 which is part of my Microsoft Office 2000 program. No it's not possible -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Jim" <Jim@discussions.microsoft.com> wrote in message news:4FABAC1F-A402-4C87-BCDA-1124E072D9D3@microsoft.com... >I closed a excel document without saving changes. Is there a way to >retrieve > my ...

Change color for each serie?
Hi all, Is it possible to to change the color in a chart where 3 dataseries is represented? Eg. one color representing one region I have tried to create 3 series in the chart unsuccesfully. It seems that excel dosent allow it although I only have about +60 data in each serie... The data is also sorted (by size and not by region), so it is not possible just to select the data and assign them a specific value representing one color.. Hope that my question is clear. :) Christian ...

Excel 2000 Shared Workbook Missing Worksheets
I am experiencing a problem in an Excel 2000 shared workbook. Worksheets are continously missing from the shared workbook. Users are connecting to the shared workbook using a Citrix connection. They enter their data into the worksheet and then save the workbook. The shared workbook is password protected so that users can not get exclusive rights and delete or move sheets. I've also tried tracing the changes and looking at the history to find the problem. Any help or advice would be greatly appreciated. --- Message posted from http://www.ExcelForum.com/ ...

Can I only recalculate a part of a worksheet?
Dear all, My .xls file consists of several worksheets. Manual recalculation of these worksheets is slow. May I know if it is possible to make a button which only recalculates a part of a worksheet, say A1:B100 of Sheet 1? Thanks. Best Regards, Chris Hi AFAIK this is not possible -- Regards Frank Kabel Frankfurt, Germany Chris wrote: > Dear all, > > My .xls file consists of several worksheets. Manual recalculation > of these worksheets is slow. May I know if it is possible to make a > button which only recalculates a part of a worksheet, say A1:B100 of > Sheet ...

Combining multiple click events
Is there a way to combine multiple click events into one relatively short function? What i have right now is a form with two sets of checkboxes. Each set is about 25 check boxes. When the form loads, the most commonly selected ones are automatically checked. But to save time, I also added a "select all" button for each set of check boxes. When you click it, it will auto check each of the 25. Uncheck it and all 25 are unchecked. What I want to happen is if I click the "select all" checkbox and then uncheck one of the 25 options, the "select all&qu...

Change font size based on value of a cell
If the value of A1>0, I need the font size in a merged cell to change from the default 10 to 16. It needs to return to the default size when A1 returns to a value of 0. Can someone help with this? Thanks. Michael Here's a little macro that will do it....... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Range("a1").Value = 0 Then Range("B1:E3").Font.Size = 10 Else Range("B1:E3").Font.Size = 16 End If Range("B1").Select End Sub Vaya con Dios, Chuck, CABGx3 "MichaelRLanier@gmail.com" wrote: > ...

Chart Value Changes in VBA
When you have say a bar chart and drag the bar downwards this will change the value in the reference source cell. Is there a way through VBA to identify the source and the cell changed by when dragging the bar. That is, when you drag the bar in the chart let me know the cell that ahs changed! Any help would be appreciated. Thanks in advance. Howard John, If your objective is to prevent the cell changes via the chart, you can protect the worksheet (Tools - Protection). The cells should be locked (Format - Cells - Protection). Or you can use the Chart_SeriesChange event macro for the cha...

Change font color for row
If A1 is numbered 1 thru 4, representing Black, blue, green and red, is there a way to change the font color for an entire row based on this? i.e. if I place a 2 in cell A1, I would like the entire row of numbers and statements to change to a blue font. Thanks in Advance Mac "Mac" <Mac@discussions.microsoft.com> wrote in message news:12170EBC-BF99-4759-9171-C8B319364690@microsoft.com... > If A1 is numbered 1 thru 4, representing Black, blue, green and red, is > there > a way to change the font color for an entire row based on this? i.e. if I > place a ...

how can I fix the size of frame? once I fixed ,the user cannot changed its size
handle WM_GETMINMAXINFO see http://www.codeguru.com/forum/showthread.php?t=318933 "msnews.microsoft.com" <on_lixing@163.com> д���ʼ� news:OgxZDM45FHA.472@TK2MSFTNGP15.phx.gbl... > > Handle CFrameWnd::OnGetMinMaxInfo and specify whatever size you want for your frame to be. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "msnews.microsoft.com" <on_lixing@163.com> wrote in message news:OgxZDM45FHA.472@TK2MSFTNGP15.phx.gbl... > > ...

Change base language
Hi I need to change the base language of CRM 4.0 I think I have to uninstall CRM and delete the CRM databases and then install again in the other language, it's not a big problem because there is no data in CRM but what about the registration would there be any problems registering the same server again ? /Jack There will be no problem in registeration .Never seen it . ------ Aamir Blog = http://mscrmsupport.wordpress.com/ No it all went well, uninstalled and deleted the databases and a new registration with same information and we are up running the right base language :) /J...

(C)
Does anyone know of a permanent fix (cell formatting or otherwise) to make (C) not appear as the copyright symbol © when entered into a cell? A temporary fix is to enter ('C') or ( C ), then tab out of cell and then delete the spaces or single quotes. -- Jim Schmidt Jim, Go to the Tools menu, choose AutoCorrect Options, and delete the entry that changes (c) to the copyright symbol. It is typically the first entry in the list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jim Schmidt" <JimSchmidt@discussi...

change pop/smtp entry via GPO?
My question is this: is it possible to broadcast pop/smtp entry changes to all users in a domain via group policy or by any other means? We have all XP machines with a mixture of Office 2k3 and 2k7 on them. We are making some changes to our mail service and it is necessary to change the pop/smtp entries on all users. We have approx. 500 accounts to change and are not relishing the idea of having to touch each one. Any thoughts or ideas are welcome. Thanks in advance for and help offered! You can deploy a prf-file with the correct mail server settings. You can crea...

Import Data from the different worksheet in the same excel file.
-I would like to create a table. -The table will have two columns. -The data in each cell is a summation of a number of cells in another worksheet. -In my table, there are quite a few rows and hence I can't create this table manually. -I created the first value by writing the formula for summation and clicking on each different cell values in another worksheet and tried to copy the formula in the other cells for all the worksheets. -Its not switching the worksheets automatically, it just switches the column in the same worksheet. -Below is the table I am trying to create.And I want to gen...

Changing password problem
my users are not able to change their outlook passwords from their computers. I am running Exchange 2000, the clients are running Outlook 2000 and XP, on Windows NT/2000/and XP. Any ideas Eric Are your clients use exchange version of outlook or POP version of outlook? "Eric Graham" <egraham22@yahoo.com> wrote in message news:#Nl0C$$nEHA.800@TK2MSFTNGP14.phx.gbl... > my users are not able to change their outlook passwords from their > computers. I am running Exchange 2000, the clients are running Outlook 2000 > and XP, on Windows NT/2000/and XP. Any ideas >...

Outlook Error after URL change
We have already configured our internal DNS records to support crm.domain.com to point to the proper machine, and created the host headers on the web server. How do we get CRM to switch from "server1" to crm.domain.com? In a nutshell, we renamed the server from an internal name to a FQDN. Internally the CRM server still believes it is on http://server1 We are trying to access it now on http;//crm.domain.com. Our outlook worked properly before we made this change. After the change, only the web works and the outlook after a uninstall and reinstall, switching the server name whil...

change null to a value of 0
I have a control on a form with a default value of "0" that users have occasionally started to change the value in the control, but then exit the form with a Null value. This affects various reports that include this data. I'd like to have the value return to 0 either in the event they exit the control without typing in a value. I am assuming it would require code in the On Exit Event of that control, but I am not sure what code would accomplish this. I would appreciate any help!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-f...

Cannot change order state
Hello, Can anybody tell me why i cannot change order state? I create order, its state is Active Then i create invoice, and close it with Paid is full but state of my order does not change (i use test version of MS CRM) Marina Milanina ...

Executing a Macro after a cell changes due to a ''new" calculation
Goodday, I tried to use the worksheet change functions, yet they only seem to act when you actively enter into a certain cell. However i need to call a certain macro when the output in a certain cell changes due to the change in the specific if function. Therefore without user intervention. Is there any way to do this? Kind regards, Ivo Geijsen i.geijsen@chello.nl You have one guess at your other post: Take a look at the worksheet_calculate event. Tornados wrote: > > Goodday, > > I tried to use the worksheet change functions, yet they only seem to act > when you acti...

How to change date as general "200306" to date "06/2003"
I recieve data with a date formated as general that looks like "200306". I need to change it to a date format that looks like this "06/2003" How do I do this =DATE(LEFT(A2, 4),RIGHT(A2,2),1) This will actually return a date for Jun 1, 2003, which you can format for "06/2003" with Format - Cells - Number - Custom: mm/yyyy. Or if you don't want a for-real Excel date, you can just convert the text string: =RIGHT(A2,2) & "/" & LEFT(A2,4) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------...

Changing content of attachments in received mails
In Outlook 2007 I can change the content of a mail attachment and save the changed mail including mail attachment. In Windows Mail I can change the content of a mail attachment (e.g. Word-file) but I cannot save the changed mail including mail attachment. It even does not create any warning message that I loose the changes that I made in the attachment when I close the mail. Does anybody have an idea how this behaviour of MS mail can be improved (or made similar to the outlook behaviour)? You cannot do it. Save the attachment then change it and then send it. Outlook is p...

Changing cell value from another cell
First of all , Hello everyone . My question is this ..Regarding a fantasy Football program i have. I use Vlookup to gather information about a player (from a named range "players" in a1 i input his code (through to k1) cells b1,c1,d1,e1,f1 give me his Position,Name,Team,Value and current score This happens 11 times to form the 11 players We have 2 transfers a year where we swap 3 players and i use the same vlookup in cells m1,n1,o1 to register players out and in q1,r1,s1, 3 players in ..I hope this makes some sense what i want to do is put the 3 players transferred in into...

Event trigger in Excel?
I have 2 columns. Column 1 is text. Column 2 says: =IF(A1="","",Today()). So if column 1 has data, column 2 gets today's date. Question: Is there a way to have column 2 update itself based on a change to column 1. My preference is that column 2 sets itself to the date an update was made, and then changes ONLY when another update is made. I can do it easy enough in Access with an event trigger and some simple VB, but not sure if the same can be accomplished. If there is a way to do this, can someone point me in the right direction? Thanks Tim http://ww...

Changing Entity Name
We need to change the wording from "Account" to "Customer". can we do that? On Wed, 27 Jul 2005 02:15:03 -0700, Shawki <Shawki@discussions.microsoft.com> wrote: >We need to change the wording from "Account" to "Customer". can we do that? Not possible in CRM I'm afraid! You will be able to do this in the next version of CRM, 3.0. -- Matt Wittemann http://icu-mscrm.blogspot.com "Shawki" wrote: > We need to change the wording from "Account" to "Customer". can we do that? This is possible. Unsupported t...

OnChange event error "Does not support this object or property"
Hi all, I'm trying to test the OnChange event on the Opportunity form - I added the following JScript code to the Sales Stage field on the form: if (crmForm.salesstagecode.value=="2-Qualified Lead") { crmForm.SetFieldReqLevel("Solicitation_Number", 1); } But when I publish these changes and run CRM,and select the '2-Qualified Lead' value on the Sales Stage picklist, I get the error: "Does not support this object or property" Anyone know how to fix this? Thank you! What is the data type in the db schema of the salesstagecode field? If it is...

Changing button region in Dialog
HI, Is there any way to assign my button a region, after dialog has been displayed? I dont want to use PreCreateWindow() Actually, I have 2 buttons, when user clicks first button, the second button's region changes, I did the following void CMyDialog::OnFirstButton() { CButton *pButton = (CButton*) GetDlgItem(IDC_SECOND); CRGN myrgn; myrgn.CreateEllipticRgn(10,10,33,39); pButton->SetWindowRgn(myrgn,TRUE); } but above code doesnt change the button's region. Any idea? I need to change the region of control after Dialog has been displayed. Thanks in advance ...