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.
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.