I want to create a macro that changes the calculation when opening excel to calculating without updating tables. When I first open excel, the file takes a while to update because it is updating tables, and I want to prevent this. Any ideas? Also, I already have the following code, but this only changes the workbook once its already opened:
Private Sub Workbook_Open()
Application.Calculation = xlSemiautomatic
End Sub
I have a file with numerous data tables that slows down the file significantly due to it's large size. I would like to set the calculation method to Manual, but also be able to manually turn the tables on and off so that they don't slow the file down when I hit F9 to calc. Is there a formula or method to accomplish this? BTW, I can't use VBA.
View 5 Replies View RelatedI saw a previous post regarding using "with (nolock)" in the FROM statement with each table to prevent SQL from locking the tables. I'm not sure exactly what's going on, but when I edited a query to add an extra criteria, the query is grinding the server practically to a halt (such power!!). Our IT guy tells me that my query is taxing the server. Maybe I'm not using the NOLOCK correctly?
Here is the query as I'm running it that runs slow. (The additional WHERE statement that I added that created the slow down is this one: ((cicmpy.AccountTypeCode='TSH') AND (oehdrhst_sql.ord_dt>={ts '2013-01-01 00:00:00'}))
Prior to adding this statement and the NOLOCK, the query runs in a flash.
Full query:
SELECT
oehdrhst_sql.ord_type,
oehdrhst_sql.bill_to_no,
cicmpy.AccountTypeCode,
[Code]....
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
View 1 Replies View RelatedI'm working on a financial reporting project that should be in Access but unfortunately it must be in Excel. Some of the formula are complex and I have a UDF to calculate these values. I added the line
Application.Volatile
to each UDF but when I change the current month in a dropdown box, the UDF's do not recalculate. The dropdown box sets a period number on one of the worksheets - this same value is passed to each UDF. I tried using this code in my dropdown box :
Sub DropDown4_Change()
Application.CalculateFull
End Sub
but the PC just hangs. I have hundreds (more likely thousands) of formula in the spreadsheet and the recalc is recalcing everything whereas I just want it to recalc the UDF's. I even changed all of the sumproduct formulae to array sum if formulae which sped things up - that is until I forced the full recalc on the drop down change event.
So my question is: is it possible to just recalc the UDF's on 3 worksheets when the user selects a different period in a dropdown box?
And a supplementary question : if {sum(if(...))} formula are faster than sumproduct formula, would a (well written) UDF perform faster than a {sum(if(...))} formula?
My spreadsheet is very large & takes sometimes 5 to 10 seconds to recalculate. The problem is that is was wanting to recalc every time I edited a cell, which I do constantly all day long. Due to this, I have turned off automatic recalculation. Is there is a way I can use VBA to force just one cell or just one row to recalculate? Keep in mind that currently no cells recalculate until I hit F9 or go in & manually hit Calculate Sheet in tools > options > calculation.
View 6 Replies View RelatedTwo questions: is there a way to not have the formulas in a range of cells, say from B2:AX20, evaluated during normal recalculations? I would like the formulas evaluated only if a form button is pressed.
How do I link the recalculation of the disabled cells to the form button?
The range of cells do not require updating unless certain other cells on a different worksheet are changed which happens rarely. However, other cells in the workbook do change so I would like to keep recalculation set to automatic. I searched the site as well as C. Pearson’s site and some others and have not found a solution. Any help would be appreciated because at present my workbook is extremely slow.
I have many formulas on my (inherited) spreadsheet in row 4 through 10000 Then in row 2 there are sumproduct and subtotal formulas
When I hide and show rows only the formulas in row 2 need to be recalculated. Yet, it recalculates ALL formulas
Is there any way to control which formulas get recalculated or is it locked to recalculate all of them
It really slows down the process
I have just written a function that sums all the values in the cells in a range that are not green. This works however if one of the non green cells is changed to green the function does not work. i have to re input it into the cell that i put it into.
Function SumNotGreen(SelectedCells As Range)
' Adds the values of the cells where the font colour is not green(35).
Dim Cell As Object
Dim x As Double
x = 0
For Each Cell In SelectedCells
If Cell.Interior.ColorIndex <> 35 Then
x = x + Cell.Value
End If
Next Cell
SumNotGreen = x
End Function
How can i make the function recalculate i.e. go back into the loop every time a change is made.
I have this sheet full of random data and I want to recalculate extra fast so I keep my finger on F9 which causes the random data to randomize really fast of course. Now, in B1:AT1 I have numbers that change with every recalculation but here is the problem. I want the recalculation to stop when excel identifies a zero in that range which doesn't happen often.
View 1 Replies View RelatedI have written a custom function which is called in lots of cells. It had an error so I modified it. But the modification did not automatically trigger recalculation in the cells where it is used. Neither did F9 (manual recalculation). The only thing that did is hitting F2 for the cell then ENTER. But what a pain to do that for every cell it's used.
View 5 Replies View RelatedI have a workbook with a button to get updated data. The button simply opens the other workbook and then closes it. THe problem is it opens the book and then closes it before the opened book has a chance to finish it calculation resulting in lots of #value errors. The book that is being opened works fine but because it is shared it can't be left open and needs to be closed asap, though it should be allowed to finsih calcs before it gets closed again. If anyone know a way to make the following temporarily halt until the opened book finishes calculating before it closes it.
Workbooks.Open Filename:= _
"X:gas daily pricingGD pricing weather call active summer.xls", ReadOnly:=True
ThisWorkbook. Saved = True
ActiveWindow.Close False
I have just set up a conditional format to change the colour of my cells in column F based on a yes or no value in column E. I have a colour function formula working to sum the totals of the cells coloured the 2 different colours seperately. Since applying the conditional format. the formula for colourfunction will only total 0.
Is there a way i can apply the colourfunction formula to cells coloured by using conditional formatting
I am looking for a solution which will highlight a cell if it has changed as a result of another cell changing (i.e. formula recalculate).
i.e.
A B C
3 2 (a*b)
I want cell c1 to be highlighted if either a1 or b1 are changed manually.
I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded).
I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore non-working days or weekends. (I haven't even gotten to inputting holidays yet.)
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
i have a challenge i need to prevent the value is more than A1 cell value in B1.
Example: if A1 value is 10 then unable to enter morethan 10 in B1 cell.
I am trying to write a VBA code that prevents the user from deleting row 8.
I do not want to use the sheet protection with password approach.
I simply want a code that prevents the user from deleting row 8 and notifies him via MSGBOX that such action is prohibited.
When user presses "ESC" key on an open userform, data he has changed in controls (simple textbox for example) gets resetted. Right? I'm seeing such behavior in Excel 2003. How about other versions?
I'd like to see some documentation about what exactly is going on, and how to prevent such unexpected feature (that forces data loss), but could not find.
I mean that i have no commandbutton with cancel=true. By default I want to keep the data user has entered/changed. Unfortunately this data reset finds place before any trappable event, or am i wrong?
I have not found any working way to capture keypresses on userform level. Userform has _KeyPress and other events, but those are not firing? If there is one, that would be helpful. Perhaps even on system level with some APIs? Altough that sounds way too complicated for a problem that should need only one little-known flag set. Perhaps there is such property somewhere?
I made up a small subprocedure that searches through a large list of zipcodes (These zipcodes are in a separate sheet). Whenever I execute my subprocedure in another sheet, it jumps to the zipcode sheet and then goes back to where I originally executed the subprocedure.
Is there any way that I can prevent the sheets from switching back and forth?
am trying to create a workbook template which collects data from users with user forms and some macros. Users assign a project name when workbook opens and then they are able to save as the workbook with name of project name. But i don't want them to save on my template mistakenly. How do i prevent this? I tried workbook_before close sub but i can manage that it recommends to save workbook with project name and if you don't want, it still asks you if you want to save workbook before close
View 7 Replies View RelatedI've got the following code which opens/gets the excel files from a specified directory and imports them into an open workbook. I've added a GoTo Error before my loop which checks if there are any files in the directory and shows an error message if it's the case. Problem is since the loop goes on till there are no more files in the directory, it goes back to the error. How to change that ?
View 3 Replies View RelatedI have 2 workbooks open. In the workbook that I am working in I run a macro from a userform to copy a worksheet from the other workbook that is idle in the back ground. I also display another user form that says "please wait" while the macro is running.
The problem I have is even though screen updating is set to false the screen switches over to the other workbook while it is copying the desired sheet and switches back to the workbook I am copying to after the macro completes. Also the "Please Wait" userform disappears while the other workbook is displayed and reappears when the initial workbook returns.
Below is my code which works without issue but perhaps there's a better way to copy between workbooks that would prevent the screen changes?
[Code] ........
Is there a code that can be input to prevent a document from being printed?
View 8 Replies View RelatedI use a few Functions in my VBA code. All these functions, are declared as "Public", and reside in a single module. However, they are called from many different modules during code execution. (i.e. many functions called from many modules - hence the "Public" declaration).
My issue is that in addition to being available to different VBA Modules, these Functions are also available on the worksheet as a UDF (so if a user presses "=" in the formula bar, the auto-complete shows these functions when the first characters match). Is there a way to remove the availability of the function on user worksheet? i.e. to allow a Function to be called from different modules in VBA, but prevent it from being available on the worksheet.
My current work-around is to prefix all Public Function names with letter "j" - as no excel formula seems to begin with it - none of them show up as auto-complete options. Nevertheless, the Functions are still available to the user - which is what I would like to prevent.
Is it possible to prevent a particular sheet from being deleted?
I have a workbook that contains sheets (of course). One of the sheets
serves as a "Help" sheet. I don't want users to accidentally (or intentionally)
delete just this sheet.
Is there anyway to turn off hyperlink. Everytime I put in an @ symbol in the cell and hit enter, it changes it to a hyperlink and changes the font and size. Is there anyway to turn this off or prevent this without having to click on the cell everytime and choose, remove hyperlink.
View 3 Replies View RelatedI display a message box in Workbook before Close event.
If user click yes I shouldnt close the workbook if NO i should close the workbook.
In the Yes part, How can I stop excel from closing the workbook.
If i dont write any ocde it automatically closes in the event.
New here, I wanna do a "Prevent Duplicate Entries" into my column i try the following: http://www.mrexcel.com/articles/prev...duplicates.php
http://www.theexceladdict.com/_t/t040818.htm
They all work well but if I'm using copy and paste It doesn't work! why is tat so?
Second issue will be i'm wanna do prevent duplication for the whole column not just one cell.
I want to allow users to select a cell in a column, but not allow them to change the contents of the cell. Also, don't want to allow them to select all the cells on the sheet.
I'm guessing that I need to use the Change event, and restore the contents back to what they were before the change.
Is there a better way to do this?