Sam Jones Posted February 8, 2021 Share Posted February 8, 2021 I want to create a worksheet script that will write some of the contents of the worksheet to a tab delimited file. The contents of the script is pretty straight forward as is specifying the path to the script in the RunScript function call. My question is, when does the script run? I am assuming that the function call must be in a cell of the worksheet "=RunScript()", but I could be wrong. Is entering and/or leaving the cell required? Is a recalculation of the worksheet required. Ideally, the function would be in a button on the worksheet that the user could press. Perhaps a cell could be made to look like a button, TBD, but also keep in mind that the entire worksheet will be created by a script, so any cells or objects that facilitate this functionality need to be able to be created by a script. So, when does a script called by the RunScript() function run? Quote Link to comment
Pat Stanford Posted February 8, 2021 Share Posted February 8, 2021 The script runs on Recalculation. On every recalculation. I don't think there is a way to get your "button" idea to work using RunScript. Do you want the worksheet to be in Edit mode when the script runs? If you can deal with the button just being on the drawing, you can probably use the Hyperlink on an object that looks like a button to run a script. Quote Link to comment
Peter Vandewalle Posted February 8, 2021 Share Posted February 8, 2021 Worksheet scripts only run when the worksheet is recalculated. And only if the users allows it. Quote Link to comment
Sam Jones Posted February 8, 2021 Author Share Posted February 8, 2021 2 questions so close in meaning: What causes a recalculation? Is there a way to force a recalculation? Quote Link to comment
MullinRJ Posted February 8, 2021 Share Posted February 8, 2021 Recalculate: any time you manually update a cell and press ENTER (or click the green √) the worksheet recalculates. Force Recalculate: under the FILE menu > Recalculate Active Worksheet, or Recalculate All Worksheets. Quote Link to comment
MullinRJ Posted February 8, 2021 Share Posted February 8, 2021 Or from a script: PROCEDURE RecalculateWS ( worksheet: HANDLE ); Quote Link to comment
Pat Stanford Posted February 8, 2021 Share Posted February 8, 2021 Recalculation is basically a manual function. You can choose Recalculate this worksheet or recalculate all worksheet from the Worksheet File menu. Per Raymond, you can also turn off Automatic Recalculation so that it does not recalculate every time you make a change and only when you force a recalculation. I believe some functions like printing and publishing will also force a recalculation so the data is updated. And as Raymond says you can for a recalculation via script. Quote Link to comment
Sam Jones Posted February 8, 2021 Author Share Posted February 8, 2021 Kind of a catch 22. I can force a recalculation via a script, but I cannot run the script until the sheet is recalculated. Quote Link to comment
Pat Stanford Posted February 8, 2021 Share Posted February 8, 2021 Yep. There are no "button" objects in VW. Yet. Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.