Worksheet Formatting Scripts

[michaelk]

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.

[Matt Panzer]

Wow!

These are great Michael! Thank you for sharing these!

[MullinRJ]

Very nice, Michael. Thank you.

Raymond

[Richard Bopp]

Very nice! Keep up the good work.

-Richard

[michaelk]

Hi Richard -

I was hoping you would help with the 3 missing scripts!

mk

[Keith W]

Bump: these are the scripts for the webinar today

[Neil Barman]

Thank you for these Michael!

-Neil

[James Russell]

Firstly Wow.

Secondly can someone sticky this for the month (if it's not done so already)?

That's pretty cool MK. Close all headers shouldn't be too hard, ObjectVariable 82 should take care of that from memory... or was it 84...

Amazing.

J

[michaelk]

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

[michaelk]

PS. I haven't checked in the last 3 years to see if the leader / trailer bug is fixed. I might go back and finish that. Maybe I'll finally figure out how to properly do dialog boxes and do the Font Picker script.

mk

[FELIPE]

I'd appreciate a lot this scripts but I can't find them attached. Am I missing something?

 

Thanks,

Felipe.

[michaelk]

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

[Lisa L]

Is there a way to auto-class any/ all worksheets when they are placed on the drawing?  I just want to be able to set the default class for worksheets.

 

Thanks,

 

Lisa

[Pat Stanford]

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);

 

[bcd]

You could save each Worksheet as a Symbol, with Other Options>Convet to Group & Assign to Class selected.

Then when you place these Symbols of your WS's in the drawing rather than the WS's themselves all will be well.

 

[Pat Stanford]

Neat idead bcd.  This is great is you have standard worksheets that you reuse.

 

I was thinking more about one off worksheets either created manually or through the Report command.

[Lisa L]

Thank you Pat and bcd for the help!

[Christian Fekete]

Thanks Michael, just saw your webcast and I was looking for these resources, can't believe I found them here,

Thanks again, great help

 

[michaelk]

Glad you found them helpful.  

 

I saw in another post you were trying to learn Vector Script.  These are the first (useful) scripts I wrote.  With a lot of help from the folks in the VS Forum!  

[Boh]

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

[michaelk]

Glad you found the webinars helpful!

 

Look above at post from November 30 2016 above in this thread.

 

There's a link to download a VW file with the scripts.

 

mk

[Boh]

Cool got it. Thanks

[AVC]

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.

[michaelk]

Hope this works!

 

 

Worksheet Formatting Scripts 2017 v2013.vwx

[AVC]

It works wonderful, thank you very much.