by David Thompson - GUI Computing

We use Formula One a lot, and it's a very flexible and useful tool. But, as with all tools, there are sometimes things you need to do which the designers never thought of... which is where using Callback functions can come in very handy.

Auto-Resizing Columns

The Formula One grid allows users to resize columns when the cursor is between two columns at the top of the grid. What it doesn't do is automatically resize the column immediately to the left of the cursor when the user double clicks. And it doesn't even generate an event in this situation to allow you to do it yourself. This article describes how to get this behaviour into your Formula One grids.

Firstly, we need to generate an event when the user double clicks on the grid. To do this, I used mhCallBack from VBTools. Any control, from MSGBLAST to SpyWorks, will do so long as it allows you to get at windows messages that VB normally doesn't allow. I just happened to be using some other VBTools controls, so mhCallBack made sense.

To set it up, I first put a Formula One grid and an mhCallBack control on a form and set the Style property of the Callback control to '3 - MsgFilter Hook.' On the load event of the form, we tell the callback control which window and which messages we are interested in.

sub Form_Load()

   gSS = Sheet.SS

   CallBack.hWndSubclass = Sheet.hWnd
   CallBack.MsgList(0) = WM_LBUTTONDBLCLK

End Sub

Now each time our user double clicks on the grid, we get a SubclassMsg event on the callback control. In this event, we have to determine if a column is currently being resized. The easiest way to do this is to look at the cursor, with the GetCursor Windows API function. The value of the resize cursor handle in Formula One is 5230. If GetCursor returns this value, then we know a column is being resized. The only thing left to do is to work out which column the cursor is on, and to use the SSSetColWidthAuto function to do the auto resize.

Sub CallBack_SubclassMsg (Msg As Integer, wParam As Integer, lParam As Long, ReturnVal As Long)
    
   Dim iCol As Integer
   Dim iResult As Integer

   If Msg = WM_LBUTTONDBLCLK Then
      If GetCursor() = 5230 Then
         iCol = GetColAtPoint()
         iResult = SSSetColWidthAuto(gSS, -1, iCol, -1, iCol, True)
      End If
   End If

End Sub

The GetColAtPoint function returns the column immediately to the left of the cursor. The GetCursorPos function gets the coordinates of the cursor and places them into a point structure. The ScreenToClient function adjusts the coordinates so that they are relative to the top left corner of the grid. The resulting X value is then converted to Twips so that it can be compared using the SSRangeToTwips function in Formula One.

Function GetColAtPoint () As Integer

   Dim xTwips As Long
   Dim lOffsetX As Long
   Dim lTwipsX As Long
   Dim iCol As Integer
   Dim lp As POINTAPI

   GetCursorPos lp
   ScreenToClient Sheet.hWnd, lp
   
   lTwipsX = lp.X * Screen.TwipsPerPixelX

   For iCol = 1 To Sheet.MaxCol
      SSError SSRangeToTwips(gSS, 1, iCol, 1, iCol, lOffsetX, 0, 0, 0, 0)
      If Abs(lOffsetX - lTwipsX) < 500 Then
         GetColAtPoint = iCol - 1
      End If
   Next iCol
   
End Function

And you might need these.

'Types
Type POINTAPI
    x As Integer
    y As Integer
End Type

Declare Sub GetCursorPos Lib "User" (lpPoint As POINTAPI)
Declare Function GetCursor Lib "User" () As Integer
Declare Sub ScreenToClient Lib "User" (ByVal hWnd As Integer, lpPoint As POINTAPI)

Global Const WM_LBUTTONDBLCLK = &H203

Enabling and Disabling Paste

Another thing we need to do quite regularly with Formula One is to catch the WM_INITMENU message. WM_INITMENU is sent when a menu is opened and before it is displayed. This is a good time to enable and disable menu items. - no surprise there. We need to disable paste for lots of reasons, all related to the way Formula One handles the clipboard - but that's another story.

Again, we first set up the callback control.

Sub Form_Load()

   CallBack2.hWndSubclass = hWnd
   CallBack2.MsgList(0) = WM_INITMENU

End Sub

Again, ensure that the style property of the callback is set to '3 - MsgFilter Hook'. Formula One has a really handy function called SSCanEdit Paste which we can use to enable or disable the paste menu

Sub CallBack2_SubclassMsg (Msg As Integer, wParam As Integer, lParam As Long, ReturnVal As Long)
   
   Dim iResult As Integer
   Dim iPaste As Integer

   If Msg = WM_INITMENU Then
      iResult = SSCanEditPaste(gSS, iPaste)
      mnuEditPaste.Enabled = iPaste
   End If

End Sub

Now your paste menu will be correctly enabled or disabled, even if your user copies data from other applications. This technique can also be used for cut and copy as well as setting fixed rows with SSGetFixedRows and SSSetFixedRows.

---

[HOME] [TABLE OF CONTENTS] [SEARCH]