How To Add Excel LinkedCell Support to Your ATL ActiveX Control (271736)

MORE INFORMATION

The LinkedCell property is actually implemented by Excel and not by your control. Excel checks to see if your control provides a bindable property named Value and, if it exists, provides the property to the user. The following steps demonstrate how to build and use a control with this feature.

Steps to Build Sample

1. In Visual C++ 6.0, create a new ATL COM AppWizard project called AtlLinkedCell. Select DLL as the Server Type, and then click Finish.
2. On the Insert menu, select New ATL Object.
3. On the Controls category, select the Full Control, and then click Next.
4. Type in LinkedCellCtl as the Short Name. Select the Attributes tab, select Supports Connection Points, and then click OK.NOTE: Adding support for connection points adds IPropertyNotifySink support to the project.
   
   
5. In ClassView, right-click ILinkedCellCtl, and then choose Add Property.
6. Set the Property Type to VARIANT, set the Property Name to Value, and then click Attributes.
7. In the Edit Attributes dialog box, select the defaultbind attribute in the list, and then click OK to dismiss the dialog box. Click OK in the Add Property to Interface dialog box to save the new property.
8. Open LinkedCellCtl.cpp and replace the get_Value and put_Value implementations with the following code:

   STDMETHODIMP CLinkedCellCtl::get_Value(VARIANT *pVal)
   {
       if(pVal) {
           pVal->vt = VT_I4;
           pVal->lVal = m_Value.lVal;
       }
       return S_OK;
   }
   
   STDMETHODIMP CLinkedCellCtl::put_Value(VARIANT newVal)
   {
       VariantChangeType(&m_Value, &newVal, 0, VT_I4);
       
       // Tell container this property changed. This calls
       // IPropertyNotifySink::OnChanged()
       FireOnChanged(1);
       // You are now dirty and should be saved...
       SetDirty(TRUE);
       // Notify container our data has changed...
       SendOnDataChange();
       // Notify container our view has changed...
       FireViewChange();
   
       return S_OK;
   }
   					

9. Open LinkedCellCtl.h, add the m_Value variable to your class, and the appropriate code to initialize it as follows:

   public:
       VARIANT m_Value;
       CLinkedCellCtl()
       {
           m_Value.vt = VT_I4;
           m_Value.lVal = 123456789;
       }
   					

10. Replace the OnDraw function in LinkedCellCtl.h with the following code:

        HRESULT OnDraw(ATL_DRAWINFO& di)
        {
            RECT& rc = *(RECT*)di.prcBounds;
            Rectangle(di.hdcDraw, rc.left, rc.top, rc.right, rc.bottom);
            SetTextAlign(di.hdcDraw, TA_CENTER|TA_BASELINE);
    
            char buf[80];
            wsprintf(buf, "Val=%d (0x%x)", m_Value.lVal, m_Value.lVal);
            TextOut(di.hdcDraw, (rc.left+rc.right)/2, (rc.top+rc.bottom)/2,
                buf, lstrlen(buf));
    
            return S_OK;
        }
    
        // Support Saving & Loading of your property...
        STDMETHOD(Save)(LPSTREAM pStm, BOOL fClearDirty)
        {
            if(pStm) {
                DWORD dwWrite;
                pStm->Write(&m_Value.lVal, sizeof(long), &dwWrite);
                pStm->Write(&m_sizeExtent.cx, sizeof(long), &dwWrite);
                pStm->Write(&m_sizeExtent.cy, sizeof(long), &dwWrite);
                SetDirty(FALSE);
            }
            return S_OK;
        }
    
        STDMETHOD(Load)(LPSTREAM pStm) 
        {
            if(pStm) {
                DWORD dwRead;
                pStm->Read(&m_Value.lVal, sizeof(long), &dwRead);
                pStm->Read(&m_sizeExtent.cx, sizeof(long), &dwRead);
                pStm->Read(&m_sizeExtent.cy, sizeof(long), &dwRead);
            }
            return S_OK;
        }
    					

11. Build the project.

Steps to Test Sample

1. Start Microsoft Excel.
2. On the View menu, select Toolbars, and then choose Control Toolbox.
3. On the Control Toolbox, click More Controls, select LinkedCellCtl class in the list, and then draw the control on the worksheet.
4. Right-click the control and choose Properties.
5. Type B2 in the LinkedCell property, and then press the Enter key.
6. Change the contents of cell B2 on the worksheet to various numeric values, and note that each time you change cell B2, the control updates automatically.
7. Save the workbook and close it. Open the saved workbook, and note that the control provides persistence support so that dimensions and property values of the control are saved along with the workbook.