I have a 4 sheet workbook that has a chart and some formulas tied to sheet 4. I want calculations to not run for this workbook unless the viewer chooses to look at sheet 4 and then they should only run once.
I have a 4 sheet workbook that has a chart and some formulas tied to sheet 4. I want calculations to not run for this workbook unless the viewer chooses to look at sheet 4 and then they should only run once.
Hi, blackspiral,
if you only have this one workbook open the codes go into ThisWorkbook:
Ciao,Please Login or Register to view this content.
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Thanks, that seems to do the trick.
What would happen if I had other workbooks open?
Hi, blackspiral,
as I assume that you only want the calculation turned off in this one workbook you would need 2 more codes for ThisWorkbook:
Ciao,Please Login or Register to view this content.
Holger
Ok, this is not working past the first time for me. I tried the second one and I get the same thing. With this it doesn't update at all after opening.
Hi, blackspiral,
just to clarify: all 5 listed procedures should be located in ThisWorbook.
Ciao,
Holger
I have a user defined function that I'm trying to get to run, and it is not located there, but in Module1... Should I move it to ThisWorkbook also? This is still not working for me
More information...
the Function I wrote looks at a range of cells on a different sheet, counts all of the cells that contain specific entries, and builds a string that is a list of those entries separated by commas until it has listed 10.
it is a function I got help on from here.
Last edited by blackspiral; 05-30-2013 at 03:14 PM.
Hi, blackspiral,
depending on whether the UDF is used as a WorksheetFunction or not depends on where it should be placed. If it is used inside a Worksheet the UDF must be placed in a standard module because it wonīt work otherwise. But I donīt get the idea why anybody would need a function for this and the disable automatic calculation which should lead to the UDF not functioning properly. And as I donīt know the structure I doubt that you would need an UDF (that would be useful if data is changing a lot which should not be the case with users on a sheet, I would rather use the Worksheet_Change or Worksheet_Activate event here instead of an UDF and leave the calculation on itīs own).
Ciao,
Holger
Ok, this is what I have... in Module1 I have a UDF that accepts a range of cells, an integer representing the offset between the field whose status I check and the field whose value I retrieve, and up to two statuses to check for.
it is called from within the field like this
=GetString('Sheet1'!A:B,9,"Completed","")
I placed it in Module1 as a UDF because unfortunately I need to use it to get data from several sheets, and the offset between the data I need, and the status is not the same on each sheet due to other information being prevalent on those sheets.
I'm not sure how I could call this function from a Worksheet_Change or Worksheet_Activate sub.
Hi, blackspiral,,
how often does the data in the range you use the UDF for change? Every 10 seconds? Then an UDF would make sense for me. But with user rights I would abandon the idea and just use plain VBA for the collecting the output.
Assuming your data to be checked is in Sheet2 beginning with A1 and the outcome should go into Sheet4 Range("A1") I would only use the Worksheet_Activate-event of Sheet 4 with the following code:
No turning off of the calculation would be needed here.Please Login or Register to view this content.
Ciao,
Holger
Thank you for this HaHoBe, it works great!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks