Monthly Solution Examples

I hope everyone had a great summer. Mine was spent architecting an addition to a spiritual center in New Hampshire. Of course after the design I ended up helping with the construction, so no vacation trips except to Southern New Hampshire.

As we enter fall I figured it was time to talk about Real Time Visual Indicators in Calc
Sometimes a spreadsheet is just a blur of numbers and if there is a computation error or inputs that
are out of bounds you need to visually and in real time let the user know there is a issue....More

Here is a late July programming example on building Complex Dialogs. The code is based on a task manager tool I created in Calc, but the discussion is on a programming technique to use flags....More


In May I offered a simple calc example on how to program a macro to label a set of columns and how to set the column widths....See More

Using SW flags to manage complex dialog actions

Managing a complicated macro dialog? Use flags
When you are just filling in a text box or selecting a list item, your OpenOffice macro coding is pretty straight forward. You write some routines and attach them to dialog events.

But this dialog has a bit more complexity:

In this little task management tool, I can move tasks from one listbox to another by selecting a listbox item and then clicking the command button between the lists. But if I double click on a list item in a listbox then a submenu pops up

The submenu allows me to promote or demote the selected item within the current listbox, delete the task item or to save the task on another spreadsheet as a completed or dropped task.

Now here is the rub, Humans change their minds on what to do next.

If the user clicks on an item in ListBox1 and then decides to work instead with an item in ListBox2, how will the program know whether or not the user doubled clicked the last listbox selection or clicked once in the new location?  On top of that, you'll probably have two items highlighted in two different Listboxes at this point.

We need to keep track in real time what the user has clicked on and how many times. So, we create a flag variable for each listbox event. When a different listbox is accessed we clear the click count on all the other listbox flags. If we have two consecutive clicks on the same item we start up the submenu dialog.

First, I created three integer variables called  ItmSlct1, ItmSlct2, and ItmSlct3. These three flag variables correspond to ListBox1, ListBox2, and ListBox3, respectively.
These are set to zero when the main dialog is initially displayed.

The next routines are the three event routines when the user clicks an item in one of the ListBoxes and they are called List1PromTst, List2PromTst, and List3PromTst and they manage the state of flags ItmSlct1, ItmSlct2, and ItmSlct3, respectively.

As an example. If a user clicks on a list item from ListBox1, the click event calls List1PromTst
below is the code:

Sub List1PromTst
   Ctrl1=oPkgDlg.getControl("ListBox1")
   x=Ctrl1.getSelectedItemPos  'Test for a selected ListBox item
   if  x>=0 then
      if ItmSlct1 = 0 then     'Test if this is a first click in this ListBox1
         ClearPrevStates(1)  'Clear all other flags except ItmSlct1
         ItmSlct1 = ItmSlct1 + 1   'Increment ItmSlct to one
         exit sub
      else
         call DsplyPromoDlg  'Item has been selected twice, call submenu dialog
      end if
   end if
end sub

If the flag is zero, this routine calls a routine called ClearPrevStates, which zeros out all the other flags except ItmSlct1, which is incremented to one. If it is greater than one, then the routine DsplyPromoDlg is called to display the submenu.
Now if the user clicks on an item in ListBox1 and then an item in ListBox2, then the event routine ListPrompt2 will be called and ListBox2's flag (ItmSlct2) will be zero by the previous ListBox1 event. The code in ListPrompt2 will clear all the ItmSlct flags and set ItmSlct2 to one by calling routine ClearPrevStates(2)

Here is the code for clearing the all flags except the one specified in "NewState". You'll note the first test is to see that the dialog package reference "oPkgDlg" is active before the rest of the code is run. It then tests for which flag is going to be incremented and which flags are
to be cleared

sub ClearPrevStates(NewState)
    'clear any previous selected listbox states
    if Not(IsNull (oPkgDlg)) then 'is notnull
       for x=1 to 3
          if x<>NewState then
             'This flag does not match the current flag, clear any selected item in the ListBox
             Ctrl1=oPkgDlg.getControl("ListBox" & format(x))
             CurSelectPos=Ctrl1.getSelectedItemPos
             if CurSelectPos >=0 then
                oResetSelection = Ctrl1.SelectItemPos(CurSelectPos, False)
             end if
             select case x
                case 1
                   ItmSlct1 = 0
                case 2
                   ItmSlct2 = 0
                case 3
                   ItmSlct3 = 0
             end select
          end if
       next x
   end if
end sub

Then I created a routine called ClearSelctStates. This routine is called when you close a submenu dialog and it clears all the flag variables and resets any highlighted (selected) listbox items.

sub ClearSelctStates
   'clear any previous selected listbox states
   if Not(IsNull (oPkgDlg)) then 'is notnull
      for x=1 to 3
         Ctrl1=oPkgDlg.getControl("ListBox" & format(x))
         CurSelectPos=Ctrl1.getSelectedItemPos
         if CurSelectPos >=0 then
            oResetSelection = Ctrl1.SelectItemPos(CurSelectPos, False)
         end if
      next x
  end if
  ItmSlct1 = 0
  ItmSlct2 = 0
  ItmSlct3 = 0
end sub

A Calc Example for labeling columns

Labeling Columns macro examples

An Excerpt From my book OpenOffice Calc Solutions CookBook

Sometimes, if you are building a large spreadsheet system that you may want to duplicate the setup over and over again. It's worth the time to have a macro routine create and name sheets and label areas of the individual sheets.

I actually documented this coding example using three different methods. The method below will increment through a series of column positions using the .getCellByPosition. In my book I  demonstrate two other ways to label the columns using the .getCellRangeByName command.

Labeling Columns Examples

Method 1 Accessing Cells with  .getCellByPosition

The subroutine below demonstrates how a macro can access a sheet called “Sheet6” and pre-label a set of cells for the columns “D” through “G” on row 2. To run this code, edit this code into a module. Open a spreadsheet and make sure you actually have a Sheet6 or modify the code for a sheet name that already exists. NOTE: Sheet names are case sensitive. From the spreadsheet click on tools-run macros and from the module tree and your macro lists pop up. Select the macro. 

sub SheetSetupTest1
   'first create variable as an array of label strings
    Labels=array("","Product Name","Model Number","Unit Cost","Time to Install")
    dSprdSht=ThisComponent 'call the already opened spreadsheet
    CurrentSht=dSprdSht.getSheets().getByName( "Sheet6" ) ' Access calc sheet by its name
    'loop thru and label the cells 
    for x=1 to 4
       'place labels on columns D2 through G2
       oCell=CurrentSht.getCellByPosition( x+2, 1 )
       oCell.setString(Labels(x))
   next x
end sub

Here is the result:

 You'll notice that cell spacing is too good, but that can be fixed by keeping an array of cell sizes, using fixed size fonts, or a couple of other techniques.

Method 2 Accessing Cells with  .getCellRangeByName

Another method is to use two arrays. One array for the labels and one for the column letters.

This method uses the .getCellRangeByName command

sub SheetSetupTest2
   'create variable as an array of label strings

   Labels=array("","Product Name","Model Number","Unit Cost","Time to Install")
   'create variable as an array of column names
   Columns=array("","D","E","F","G”)
   'loop thru and label column heading cells
   for x=1 to 4
      oCell=CurrentSht.getCellRangeByName(Columns(x) & "2")
      oCell.setString(Labels(x))
   next x
end sub

Method 3 Incrementing the Column name string

The third method uses a custom function I created and describe in my book. The function

is called nxtColLtr (ColumnString)   The next-column-letter function takes a column letter or

letters as an argument and returns the next column letter(s) value.  The advantage to this method is you just need to specify a starting column value. This is handy if you have to shift the labels a few times in your design. The nxtColLtr function was tested to increment from column "A" through column "AMJ".

nxtColLtr(columnString)  function Examples:

nxtColLtr("A")  returns "B"

nxtColLtr("Z")  returns "AA"

nxtColLtr("AZ") returns "BA"

Here is the example using the nxtColLtr function

sub SheetSetupTest3
   'create variable as an array of label strings 

   Labels=array("","Product Name","Model Number","Unit Cost","Time to Install")
   'Set StartCol variable for first column heading to label
   StartCol="D"
   for x=1 to 4
      oCell=CurrentSht.getCellRangeByName(StartCol & "2")
      oCell.setString(Labels(x))

      StartCol=nxtColLtr(StartCol)   'get the next column name string
   next x

end sub

Solution Books Table of Contents

- Just to give you an idea of what kinds of solutions and design topics I've explained and documented in my Solution Books, The Table of Contents for each book is listed below:

OpenOffice Calc Macro Solutions Book

OpenOffice Writer Macro Solutions Book

OpenOffice Draw Macro Solutions Book

Appendix A talks about dialog components and is a common appendix to all the books

Calc Solutions Table of Contents

My OpenOffice CookBooks

How did the series come about?

    This OpenOffice Calc Macro CookBook 

What is Calc

Cell functions in Calc versus Excel

Calc API calls you need to know about

Macro code to access calc files

ThisComponent method

Creating a New Calc document method

Opening an Existing Calc document method

Accessing sheets and Cells

Accessing Cells

Examples of Access Cells by CellName or by Column/Row

Labeling Columns Example - By .getCellByPosition

Labeling Columns Example - By .getCellRangeByName

Labeling Columns Example – Adjusting Column Widths

Labels with Fixed width Font

Hiding Columns

Incrementing column names – nxtcolLtr function

Dynamic Test Indicators in Calc

A Calc Cell Style Workaround

Custom Cell Functions

Findnth (item within a) cell function

Personhours cell function

Array or cell reference?

Converting values with labels to standard Units

StatRollUp Cell Function

The formulaLocal Property – accessing the cell range string within the cell function call

AbsoluteName = changing functionality by cell function's location

Writing Calc Subroutines with Dialogs

A Sheet Group Navigator

Sheet Navigator Macro Example

Our first Dialog

Openoffice Bug or Feature

Custom Search Routines

Client Search

ClientSearch dialog and code

Access to Dialog and calc sheet

Basic Search Loop

Aligned columns in a listbox

Adding wildcards to the Search

Birthday Search

Access to Dialog and calc sheet

Search Setup and Preload

BirthDate Search Loop

Adding a search option

Using desktop functions to cut and paste sheets

dispatchURL

CopySheetToNewDoc

The Classic MS Office Example – an interactive two listbox Dialog

Finding an online solution and an API bug

Loading ListBox1 (AddItem and AddItems methods)

The => CommandButton(getSelectedItemPos method)

The <= CommandButton

Iterating Items within a ListBox

Appendix A - Dialog Controls Overview

Appendix B – Cell Formatting Fonts, Bolding, and Color Effects

Using the Debugger to find cell values

Left, Right, and Center Justify Cell text:

Setting Cell Font

Cell Font Height

Setting Font Color

Setting Background Color

Bolding Cell Text

Setting Line Wrapping

Underlining Cell Text

Two Underlining methods

Table Borders

Appendix C – Client Search and Birthday Search Tools

Client search Dialog box

Birthday search Dialog box

Back to top of Page

Writer Solutions Table of Contents

My OpenOffice CookBooks

How did the series come about?

This OpenOffifce Writer Macro CookBook 

Macro code to access writer files

ThisComponent method

Creating a Writer document method

Opening an Existing Write document method

Writer Document's collection paradigm

writer Tables - Mailing Labels Macro

Enumeration objects

Manipulating Documents with Cursors

Using text cursors

MoveCursorToText Example

MoveCursorToText

Automating Documentation

Module Mailer

LoadMailerOptionsDialog

Interactive Dialog Control routines

Basic MoveClient2MailList operation

Accessing Tables within Writer

Globals and main routine setup

Testing for Paragraphs and Tables

Retrieving Table Cell strings

Macro Book Construction

OpenOffice API calls to build writer documents

Using text cursors

BuildNewDoc code

LoadTempltFile

LoadContentAnHdrs

WriteContentLines

InsertingTableOfContent

Working with Frames – The Writer portion of the two listbox label making example

SOAP Example Extended

Dialog SoapSetup

Module Create Soaps

CopySheetToNewDoc(TempltType as Integer)

Appendix A - Dialog Controls Overview

Appendix B – Contract Automation Example and Code 59

Scrubbed Contract Document 61

Back to top of Page

Draw Solutions Table of Contents

My OpenOffice CookBooks 4

How did the series come about? 4

This OpenOffice Draw Macro CookBook 5

What is OpenOffice Draw 5

Vector versus Bitmap drawing systems 6

Draw and Impress Applications 6

Macro code to access draw files 6

ThisComponent method 7

Creating a New Draw document method 7

Opening an Existing Draw document method 7

Drawpages and Masterpages 8

DrawPages linked to MasterPage 8

Pages can have the same name 8

Draw Macro Basics 9

Portrait orientation and dimensions 9

Basic draw macro Routines 10

Adding Shapes to a page 10

Creating shapes routine 11

Erasing Shapes from the drawpage 15

Drawing Metadata 16

ConnectorShapes 16

Scanning a drawing for Metadata 18

Which Gluepoint? 21

How About Drawing Objects with Property Supports 23

Object Description Field 2

Appendix A - Dialog Controls Overview 28

Back to top of Page
 

Appendix A - Dialog Controls Overview

X-Ray or Debugger

Setting the Date in DateField

DateField Options

NumericField Control

CurrencyField Control

TimeField Control

ListBox Control

Aligned Columns in a ListBox

The Global Dialog reference

ComboBox Control

CheckBoxes and Option Controls

OptionButton Control

CheckBox Control

PatternField Control

ImageControl Controls

FixedLine Controls

ScrollBar Controls

ListBox and value list storage decisions

Dynamic ListBox item loading

Back to top of Page