Jump to content

Worksheet Formatting Scripts


Recommended Posts

I do a lot of formatting of worksheets in my work and I've often been frustrated by how many clicks it can take to center text in a worksheet cell (6!).

With the infinite patience of the denizens of the Vector Script forum I managed to write a collection of scripts that greatly speed up worksheet formatting for me by (mostly) eliminating the need to click the little triangle and navigate through all the tabs in the Format Cells... menu. The scripts allow you to immediately see the effects of a format change without closing the dialog box. It feels much more interactive.

I've been using them for a while in both VW 2012 and 2011 with no ill effects. I've shared them with some other users who I'm sure would have mentioned if their computers started smoking after running one of the scripts. So far, so good. But there are no guarantees. I'm not very good at this. I should probably learn how to check to make sure a worksheet exists in the drawing and that it's open, and a cell is selected. Use at your own risk.

I find it helpful to have these palettes open next to a worksheet when formatting. In general, just select a cell or range of cells in a worksheet and double click on a script from one of the palettes that looks like it will do what you want. Whenever appropriate, I've tried to make them work on both individual cells and ranges of cells. Whenever possible, attributes other than the one being called on will be unaffected.

If you've never used a script before, they live in the resource browser in folders called Script Palettes. Right click on a palette and choose OPEN. A little palette will appear with the names of the scripts. Double click on a name in that list to execute the script.

Hope you find these useful.

mk

The attached file contains the following 45 scripts:

WORKSHEET TEXT FORMATTING

AlignCenter - Aligns text in the horizontal center of the cell or range of cells.

AlignGeneral - Default alignment of the cell or range of cells.

AlignLeft - Aligns text to the left of the cell or range of cells.

AlignRight - Aligns text to the right of the cell or range of cells.

AlignVBottom - Aligns text to the bottom of the cell or range of cells.

AlignVCenter - Aligns text to the vertical center of the cell or range of cells.

AlignVTop - Alignts text to the top of the cell or range of cells.

FontSize-1 - Indexes text in each of the selected range of cells or single sell down one size.

FontSize+1 - Indexes text in each of the selected range of cells or single sell up one size.

MergeCells - Merges the selected cells into one cell.

SplitCells - Splits a merged cell back into individual cells.

TextAngle0 - Sets text to horizontal in a cell or range of cells.

TextAngle90 - Sets text to vertical in a cell or range of cells.

TextBold - Toggles bold on and off in a cell or range of cells. Leaves other text formats unaffected.

TextItalic - Toggles italics on and off in a cell or range of cells. Leaves other text formats unaffected.

TextPlain - Sets text format to plain in a cell or range of cells.

TextUnderline - Toggles underline on and off in a cell or range of cells. Leaves other text formats unaffected.

The following two formatting styles should only work on Macs. I don't have a PC version to test it, but PC users should get a message and the cells will remain untouched. Mac users won't see anything happen to the text format until the worksheet is closed and placed on a page. Even then, the effect is less than impressive.

TextOutline - Toggles outline on and off in a cell or range of cells. Leaves other text formats unaffected.

TextShadow - Toggles shadow on and off in a cell or range of cells. Leaves other text formats unaffected.

UnWrapText - Turns text wrapping off in selected cells.

WrapText - Turns text wrapping on in selected cells.

WORKSHEET BORDERS FILLS AND TEXT COLOR

CellBorder_Bottom - Sets the color, line weight, and line style for the bottom of a selected single cell or a range of cells.

CellBorder_Left - Sets the color, line weight, and line style for the left side of a selected single cell or a range of cells.

CellBorder_Outline - Sets the color, line weight, and line style for the outline of a selected single cell or a range of cells.

CellBorder_Right - Sets the color, line weight, and line style for the right side of a selected single cell or a range of cells.

CellBorder_Top - Sets the color, line weight, and line style for the top of a selected single cell or a range of cells.

CellColor&Text - Sets the cell fill color and text color for a selected single cell or a range of cells.

WORKSHEET NUMBER FORMATING

numBOOLEAN - Sets number type to boolean.

NumDate - Sets number type to date.

numDate_- - Indexes date format type down one. (It will cycle through all the formats and eventually return to the original).

numDate_+ - Indexes date format type up one. (It will cycle through all the formats and eventually return to the original).

numDECIMAL_1 - Sets the number of decimal places to 1.

numDECIMAL_2 - Sets the number of decimal places to 2.

numDECIMAL_3 - Sets the number of decimal places to 3.

numDIM_ANGLE - Sets the value to an angle dimension.

numDIM_AREA - Sets the value to an area dimension.

numDIM_LINEAR - Sets the value to a linear dimension.

numDIM_VOLUME - Sets the value to an volume dimension

numFRACT/16 - Expresses fractional values with a denominator of 16.

numFRACT/2 - Expresses fractional values with a denominator of 2.

numFRACT/3 - Expresses fractional values with a denominator of 3.

numFRACT/4 - Expresses fractional values with a denominator of 4.

numFRACT/8 - Expresses fractional values with a denominator of 8.

numGENERAL - just what it says.

numTEXT - Treats a number as text.

Not done (yet):

Font chooser. Haven't gotten to it yet. Sounds hard.

Number Leader and Trailer. There's a bug that prevents it.

Close database headers for all worksheets placed in drawing. Haven't gotten to it yet. Wish it was an option in the print dialog box.

  • Like 1
  • Love 1
Link to comment
  • 2 months later...
  • 10 months later...
  • 2 years later...

James

It's super easy:

# Close All Open Database Headers in Worksheets
# Badly scripted by Michael Klaers

# December 25, 2013
# © 2013, Small Group, Inc - Michael Klaers michael.klaers@gmail.com

# Licensed under the GNU Lesser General Public License



def CloseThatHeader(h):
WkShtResource = vs.GetWSFromImage(h)
vs.SetObjectVariableBoolean(WkShtResource, 82, False)
vs.RecalculateWS(WkShtResource)

vs.ForEachObject(CloseThatHeader, "T=WORKSHEET")

I made it a menu command right next to print, so I can run it before printing or publishing.

I also made menu commands to open the worksheet formatting palettes and mapped them to keyboard shortcuts. Really useful.

I'll attach them here.

mk

  • Like 1
Link to comment
  • 1 year later...

Felipe

 

Yeah, the boys and girls in Columbia are still working on getting the old uploads restored.  Jim says they should have it in the next update.  I hope it's coming soon!!

 

In the mean time, here's a slightly updated and augmented group of scripts.  These are part of the glob of scripts from the Killer Worksheets talk from the 2016 Design Summit, so they should all be functioning (and not displaying random debugging messages!)

 

Hope you find them helpful

 

mk

 

Worksheet Formatting Scripts 2017.vwx

  • Like 1
Link to comment

I don't think there is an automatic way to do it. Unfortunately, worksheets are not like PIOs that have a default record where you can set that kind of data. It would be nice.

 

As a work around, the following script will change the class of all the worksheet images on the drawing to a class named whatever you put into the MyClass line in place of None.  Be careful with your spelling. It will create a new class if one my that name does not already exist.

 

Copy and paste everything in the code block below into a new blank script created with the Resource Manager.

 

Procedure SetWorksheetClass;

{December 1, 2016}
{© 2016, Pat Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{No Warranty Expressed or Implied. Use at your own risk}

{Changes the class of all worksheet images in the drawing to a class}
{with the name set in the MyClass= line. Creates the class if it}
{does not exist. Only changes worksheets already placed on drawing.}

Const MyClass='None';

Procedure Change(Hd:Handle);

	Begin
		SetClass(Hd,MyClass);
	End;
	
Begin
	ForEachObject(Change,((T=WORKSHEET)));
End;

Run(SetWorksheetClass);

 

  • Like 1
Link to comment
  • 3 months later...
  • 3 months later...

Hi Michael.

I've been trying to get my head around worksheets and reports and found your vidoes which are excellant! I'd like to get the worksheet scripts if possible but can't see them on this page. Am I missing something or have they been removed?

Thanks

Link to comment
  • 2 weeks later...
  • 2 years later...
On 11/30/2016 at 7:56 PM, michaelk said:

Felipe

 

Yeah, the boys and girls in Columbia are still working on getting the old uploads restored.  Jim says they should have it in the next update.  I hope it's coming soon!!

 

In the mean time, here's a slightly updated and augmented group of scripts.  These are part of the glob of scripts from the Killer Worksheets talk from the 2016 Design Summit, so they should all be functioning (and not displaying random debugging messages!)

 

Hope you find them helpful

 

mk

 

Worksheet Formatting Scripts 2017.vwx

I was wondering if someone could upload a vectorworks 2013 version of this file, please.

Link to comment
  • 2 weeks later...

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...