Controlling Excel Automatic Calculation???

Hi

I=92m trying to make a simple routine in excel VB so, when a command
button is pressed, the whole workbook is calculated (I have automatic
calculation set to manual).  I also want to display a warning message
to the end user when input data is changed but the =93calculate=94 button
has not been pressed.

Can anyone point me in the right direction?

Any help would be very much appreciated.
0
9/3/2008 10:40:49 AM
excel 39879 articles. 2 followers. Follow

5 Replies
446 Views

Similar Articles

[PageSpeed] 24

It is never a good idea to set calculation to manual. nevertheless,
this is a normal setting. Put the following code in the Command Button
Click function

    With Application
        .Calculation = xlAutomatic
    End With

Gr,
Ricardo
0
9/3/2008 10:53:55 AM
Could you give me any pointers on how to display a warning message if
the =93calculate=94 button has not be pressed but input data has
changed??

0
9/3/2008 10:59:05 AM
I would consider changing the color of the text in the CommandButton as a 
warning to the user that the sheet needs to be recalculated (for example, 
red text warns the user the sheet need calculating, black text means it 
doesn't). To do this, set the text to black in the CommandButton's Click 
event and set the text to red in the worksheet's Change event. How you set 
the color depends on where the CommandButton came from.

From the Forms toolbar
========================
With Worksheets("Sheet1")
   .Shapes("Button 1").TextFrame.Characters.Font.Color = vbRed
End With

From the Control Toolbox toolbar
==================================
With Worksheets("Sheet1")
   .CommandButton1.ForeColor = vbRed
End With

You can use the predefined constant vbBlack to make the text black again.

-- 
Rick (MVP - Excel)


<ChrisAmies@gmail.com> wrote in message 
news:bacce0df-ab1f-4ac5-b2cb-d9cc3c1b04c4@e39g2000hsf.googlegroups.com...
Could you give me any pointers on how to display a warning message if
the �calculate� button has not be pressed but input data has
changed??

0
9/3/2008 4:29:47 PM
Hi

I=92ve put together the following routine.  It basically does what I
originally wanted.  I=92ve also added code to make sure the =93warning=94
sub routine is only run on the 1st change of the monitored range of
cells.

I=92m having problems when a group of cells are changed in one go (i.e.
a selection).  This results in the =93warning=94 sub routine being run for
however many cells are originally selected and changed.

I=92m really new to Excel VBA, can anyone offer any suggestions?

CODE:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim VRange As Range
    Dim Error_run As String
    Dim myRange As Range

    Error_run =3D Range("$Z$1").Value
    Set myRange =3D ActiveCell
    Set VRange =3D Range("custom_pipe")

    'Check for sheet changes
    For Each cell In Target
        If Union(cell, VRange).Address =3D VRange.Address Then
            If Error_run =3D "YES" Then

            Exit Sub

            Else

            'Run Warning Sub
            warning

            Sheets("pipe data").Activate
            Range("$Z$1").Value =3D "YES"
            myRange.Select

            End If
        End If
    Next cell

    Sheets("pipe data").Activate
    myRange.Select

End Sub
0
9/5/2008 2:45:35 PM
I think this will do what you want... remove the warning from inside the 
loop and add this code immediately before the For..Next statement (so it run 
before the loop starts)...

If Error_run <> "YES" And Not Intersect(Target, VRange) Is Nothing Then
    ' Run Warning Sub
    warning
End If

Also, if I understand it correctly, this line...

If Union(cell, VRange).Address = VRange.Address Then

is usually written like this...

If Not Intersect(cell, VRange) Is Nothing Then

-- 
Rick (MVP - Excel)


<ChrisAmies@gmail.com> wrote in message 
news:4032c9f1-8a11-433a-8b79-490bc07ab4f1@e39g2000hsf.googlegroups.com...
Hi

I�ve put together the following routine.  It basically does what I
originally wanted.  I�ve also added code to make sure the �warning�
sub routine is only run on the 1st change of the monitored range of
cells.

I�m having problems when a group of cells are changed in one go (i.e.
a selection).  This results in the �warning� sub routine being run for
however many cells are originally selected and changed.

I�m really new to Excel VBA, can anyone offer any suggestions?

CODE:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim VRange As Range
    Dim Error_run As String
    Dim myRange As Range

    Error_run = Range("$Z$1").Value
    Set myRange = ActiveCell
    Set VRange = Range("custom_pipe")

    'Check for sheet changes
    For Each cell In Target
        If Union(cell, VRange).Address = VRange.Address Then
            If Error_run = "YES" Then

            Exit Sub

            Else

            'Run Warning Sub
            warning

            Sheets("pipe data").Activate
            Range("$Z$1").Value = "YES"
            myRange.Select

            End If
        End If
    Next cell

    Sheets("pipe data").Activate
    myRange.Select

End Sub 

0
9/5/2008 3:10:49 PM
Reply:

Similar Artilces:

bound control or inbound control
Hi Katherine, There are differences between using bound versus unbound controls. Where on the form do you want to use an unbound control? Jeanette Cunningham "Katherine" <Katherine@discussions.microsoft.com> wrote in message news:23BBC192-0A74-4B9F-9513-5E5923A76658@microsoft.com... > ...

why can't i insert a chart in excel worksheet?
I have created a worksheet and formattted it according to our textbook in class. i am up to the point of inserting a chart, but when i go on the ribbon to insert a chart the choices are greyed out and i can not insert a chart. i opened a new worksheet to check, and the chart choices are not greyed out. i also opened a previous worksheet and i can still insert a chart, but i can not insert one in this worksheet for some reason. HELP!! Is the worksheet protected? Is it shared? Is it grouped (are more than one sheet selected)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials ...

scalable headings in Microsoft Excel when editing long formulas
I use Excel a lot (2000 and 2003) and frequently create long formulas which have 4 or more rows. When I have the size of my workbook maximized, these formulas hide my column headings (A,B,C,etc.). When formulas become more than 1 row, the size of the maximized window should always modify so that a user can always see the column references while editing. Is there a way to do this without constantly having to re-size my active workbook window (i.e. taking it off maximized)? It is so aggravating ... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to th...

why won't word 2007 release control of a .rtf file after exiting w
I AM HAVING TROUBLE WITH ACCESS TO RICH TEXT FILES BY PROGRAMS OTHER THAN WORD------ AFTER THE FILE OPEN IN WORD IS CLOSED AND EVEN AFTER WORD HAS BEEN SHUT DOWN (EXITED) ---------- I GET THE ERROR MESSAGE THAT THE FILE IS "IN USE" BY WORD WHEN (FOR EXAMPLE) AN ATTEMPT IS MADE TO DELETE IT ---- SOMETIMES WHEN RE-OPENING THE FILE AFTER RE-STARTING WORD THE FILE CANNOT BE SAVED BECAUSE IT IS IN "READ ONLY" MODE ------- AFTER MUCH FIDDLING AROUND I FINALLY MANAGED TO DELETE THE PROBLEM FILE ONLY TO HAVE THE SAME PROBLEM RETURN WITH A DIFFERNT FILE ----- WORD 200...

RE: Control
Can some one point me right direction to achive this following. Need to dispaly a data in a table format. some of the column data may have to display a image/icon based on a value from the table (1 :- Red color icon, 2:-yellow color icon...) Once the data is loaded and displayed on the format , able to search particular row by column index and update the values including the images(change different image). I tried with Listview control, with the report view I am not able to search row and update the columns with the List vew I am not able to show the image/icon. do not want to use ...

Excel display Fault
Out of about 50 people using an excel program I wrote, three people exhibits a problem after running a macro, where a worksheet shows parts of other worksheets. Moving a mouse over the areas clears the problem area. I believe some update from Microsoft has created this fault as it has only started to fault last week. All 3 people are running excel 2003 professional Turning auto calculate off fixes the fault, however turning auto calculate back on, immediately produces error I use the following commands trying to overcome problem with no positive results Application.Cal...

MSCRM login and the Users Control Panel
Is anyone aware of any way that the settings stored under Control Panel - Users - Password Management might interfere with the process of Single Sign On into MSCRM? It seemed to have somehow cached someones login and prevented logging in as the logged in user, until cached credentials had been "deleted". However I have never seen this mentioned as being relevant. I am not too sure what the architecture model is there, but it appears to be a single domain. If this area has any relevance, it would be useful to know....thanks ...

How do I create mail merge labels from excel.
Whatever I try, I am unable to open the data source. Jackie, For help on Word mail merge using Excel as the data source have a look here http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm http://www.mvps.org/dmcritchie/excel/mailmerg.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "jackie" <jackie@discussions.microsoft.com> wrote in m...

Excel creating tmp files
I have office 2007 installed on my new PC which has windows 7 and when I make a change to an excel file it wants to create a separate file labled with a tmp extension ie f5de4221.tmp. Why is it doing this and what can I do to stop it. Jim S Hi Jim, Have you solved this problem? I'm also facing the same problem but can't solve it. If you've found the solution please let me know steps to follow. Best regards, Gus "Big Jim" wrote: > I have office 2007 installed on my new PC which has windows 7 and when I > make a change to an excel file it...

EXCEL chart legend formatting
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Can anyone tell me how to get superscript/subscript formatting to show in a chart legend? I've got it working in the excel data cells, but the formattin doesn't transfer to the legend and I can't directly change the legend text. I am having the same problem. Did you ever find a solution? Thanks! tauntonuse wrote: EXCEL chart legend formatting 28-Oct-09 Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Can anyone tell me how to get superscript/subscript formatting to show in a chart legend? I h...

Spam control - MS Outlook
Hi! I am using MS Outlook 2000 and use 2 profiles. I recently installed a firewall package (Fsecure = Telia S�ker Surf), including Spam control. After install, MSO hangs and I cannot change to the other profile (if I not removed MSO by ctrl/alt/del) Somebody want to comment this? /Sven-Erik Intergrated AV/Spam is the cause of frequent issues in Outlook Is the FSecure you installed compatible with this old version of Outlook? "Storfille" <storfille@ebrev.net> wrote in message news:b1Ixm.11987$U5.164765@newsb.telia.net... > Hi! > > I am using MS Outlook 200...

Releasing memory after creating OCX control
Hi, I try to create an instance of an activex control in my MFC dialog based app. Though my application works with out any error, if i check the memory after calling "delete pMyControl" it keeps on occupying the same memory it occupied when the control was alive. Could anyone help me where I am missing to clear the memory? Thank you. My Code here ---------------m_pxWmp= new CWMPPlayer4();if( m_pxWmp->Create("replay", WS_CHILD | WS_BORDER | WS_VISIBLE , CRect(100, 100,320,240), this, 2)){ m_replay = true; m_pxWmp->SetUrl("C:\\video.avi"); m_pxWmp->Set...

Generating a excel document
I'm exporting text contained within a HTML table to an Excel file. The text within a single td tag at times contains the br tag. My problem is when this data is opened in Excel. I would expect to see a group of text in a single cell broken up on multiple lines. But what actually happens is that the br tag in excel actually forces the text to be in another cell on the next row. Example: <td>This is some text<br>that is broken<br>up on multiple lines</td> when opened in Excel doesn't display in a single cell, it ends up being in two cells on two rows.... Wh...

Excel Formula #8
I am trying to format spreadsheet. Need 3 counts. 1st count is for 90% and above =COUNTIF(H5:M16,">89.99"). Second count is for Under 80% =COUNTIF (H5:M16,"<79.99") . Can anyone help me with a formula for 3rd count, between 80% and 89.98? I can't seem to formulate it. Thanks in advance Hi Barb, Try =COUNT(H5:M16) - COUNTIF(H5:M16,">89.99") - COUNTIF(H5:M16,"<79.99") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Barb" <...

Adding a control variable
Hello all, Under VC++ 7.1, I've added a checkbox to a dialog. Using the dialog editor, I am not trying to add a control variable for this check box. However, when I right-click on the check box and select "Add Variable", the resulting wizard dialog does not allow me to select "Control Variable" (it is grayed out). What do I need to do to get a control variable for this check box added to my class? Thanks, Dave I've never seen that before - are you able to duplicate this problem with other projects too? -- Regards, Nish [VC++ MVP] "Dave" <bett...

Excel 2003 VBA program kills itself in Japan
Hi, I wrote a VBA program for an Excel workbook. This program works just fine on my PC (US English setup), but my japanese collegues have problems. We observed that the VBA program changes on a japanese PC to the extent that syntax errors occur. This is how it happens: My code looks like this: ------------------------------------ Function ReplIllegal(ByVal txt As String) As String Dim ill As String ill = "´`@€²³°^<>\*./[]:;|=?,""" ' list of illegal characters ReplIllegal = "" txt = Repla...

what control can display characters from txt files in a dialog?
I want to read lines from txt files and display them on a dialog.I wonder which control can be put into the dialog to hold the characters from txt? Something like :- while(m_file.ReadString(tmpstr)) { str += tmpstr; } m_edit.SetWindowText(str); -- Regards, Nish [VC++ MVP] "liao_xf" <sclxf@sina.com> wrote in message news:001401c3652f$177b9830$a001280a@phx.gbl... > Thanks first! > I know that Edit control can receive and edit input. > and I know the filestdio can read lines from txt. > but can you tell me how to attatch the lines to the edit? > Thanks!...

stock control system
trying to see up a 'basic stock control sytem' for a warehouse that contains building materials. -- Thank you. You can use multiple ways of doing, there's 3 ways to come to m head... 1) Use a worksheet as database an save all the information on the cell of that worksheet using some macros to access it and handle. 2) You might want to use Access instead of Excel, I think would b easier to handle the database portion and you still have the Macro available. 3) You can use VB with an Access to create an application -- fanay ------------------------------------------------------...

Creating dialog controls at run-time
I've been researching how to do this, but whenever I try what other people do, I get very bad results. But my challenge doesn't end there. The number of controls I want to create is only known at run-time. The problems I face are manifold: 1. The code most people use fails badly. Not only is the font unconventionally big, but when I click the checkbox I create, the whole dialog disappears. If that's not enough, the pointer to the control is NULL when the dialog's destructor is called, so I can't delete the instance, and get a memory leak message when the program ends: CADi...

Use VBA to design Excel report
Hello - I've got an Access application that uses VBA to programmatically create a series of Excel reports. I'm having a little trouble formatting the cells correctly. The way you would code it in Excel isn't always working in Access. Does anyone know where I can find a good reference on the proper format? I've figured out basic font changes, but I'm having trouble with number formats and borders. Thanks! Sorry, I should also include sorting. Can't seem to get the wording correct for that either. Thanks again! "Michael" wrote: >...

Office97 home edition Excel
My new Vista operated Dell inspiron does not run the Server add on from my Office 97 disk. It was fine with the previous XP machine. Is there anything I can do to get it working? The Help files don't work with Vista it seems. Thanks Alan C r.combellack@nspmexplorenet.com -- Posted via a free Usenet account from http://www.teranews.com ...

Sort data in Excel
I am having some tag numbers and it is entered in random. I want to sort this according to the group which starts alphabetically. How can this be done? I know that filter is one way but it doest work the way i want. Eg. I am having tags pg-1, , pg-5, pg-2, he-1, xza-2, pza-3, fia-1 etc. I want to sort it such a way that all the tag starts with p should come together and h should come together .... and so on .. how can this be done . thanks in advance Can you use an extra column and sort by that, eg. if the tags are in column A =LEFT(A2,1) This will parse the first letter into a new ...

MDI support in ATL composite control
Hi, I have components created in ATL. The components are ATL composite controls. Entire work has been done till now using ATL, however I now need to give MDI type of work area (based o doc-view architecture) within the control. I have class derived from CMDIFrameWnd. However when I try to initialize instance of this class by calling "Create" method of CFrameWnd (base class in CMDIFrameWnd), I get error and creation fails. Tracing the code, I found that base class create method calls "AfxGetInstanceHandle" to get handle to the DLL. Now since this is ATL Composite control, ...

How to enable "enable automatic update" security setting by VB cod
Hi All, I wanted to enable "enable automatic update for all workbook links" radio button through VB code (macro), As my current macro code gets link references from other excel workbook, so it is important to enable this setting by code itself. Default location of thatradio button is Excel Options-->Trust Centre-->Trust Center Settings-->External Content. ...

View Filtering and Access Controls for Business Units
I am trying to implement MS-CRM for my company. We have multiple Business Units . I need to give views to each Business Units so that they see only their own Contacts/Accounts/Opp lists. How do I accomplish this ? Also, If I gave inter business unit acesses , how do I make sure that the one business unit users can not modify any fields on other's Business Unit's records ? Thanks in advance for any help, Kishore This is quite easy to do with CRM. Assuming you have already created your Business Units in CRM, you can view a security role within one of the Business Units and a...