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