MS Office-Access/Excel/Word
Listed below are the questions that we are asked quite often. Before you write us, be sure to check here.

Programming

Programming

How can I get the list of members ( intellisense )?

IntelliSense is Microsoft's implementation of autocompletion, best known for its use in the Microsoft Visual Studio integrated development environment. In addition to completing the symbol names the programmer is typing, IntelliSense serves as documentation and disambiguation for variable names, functions and methods using reflection.

Let's say you need the IntelliSense for the eXGrid component. In your code add this:
Dim g As EXGRIDLib.grid
Set g = Grid1.Object

If you type g and then type . (dot) the properties and methods available for the g object will be displayed to a popup listbox.

I can't get any of your example VBA code to work. What could be?

You need to install the COM/ActiveX version of the control. Based on your MS Office, you need to choose 32 or 64 bit version of the component as explained in What configuration should I install so I can use in MS Access/Excel?

Once you have installed the product, you should file the MS Office samples here:

  • C:\Program Files\Exontrol\Ex...\Sample\Access2007, for 32-bit machines, or Office 64-bit on x64 machines.
  • C:\Program Files (x86)\Exontrol\Ex...\Sample\Access2007, for Office 32-bit on x64 machines.

When you open the accdb file you may be prompted for an MS Access security as shown bellow:

You have to choose Enable this content, else you may not be able to run any of installed samples. 

What configuration should I install so I can use in MS Access/Excel?

You need to install the COM/ActiveX version of the control. Choose the product's installer from from the ActiveX/COM column in the download page, or at Point 1 in the product's download page. The MSI installer installs the 32-bit ANSI version of the component.
  • 32-bit/64-bit, indicates 32-bit or 64-bit edition of the Microsoft Access. The 64-bit version works only on 64-bit Windows system.
  • ANSI is the most popular character standard used by personal computers. Because the ANSI standard uses only a single byte to represent each character, it is limited to a maximum of 256 character and punctuation codes. Although this is adequate for English, it doesn't fully support many other languages.
  • UNICODE is a character-encoding scheme that uses 2 bytes for every character. The International Standards Organization (ISO) defines a number in the range of 0 to 65,535 (216 – 1) for just about every character and symbol in every language (plus some empty spaces for future growth). On all 32-bit versions of Windows, Unicode is used by the Component Object Model (COM), the basis for OLE and ActiveX technologies. Unicode is fully supported by Windows NT.

None of the samples installed work. What should be done?

The most probably you installed the x64 version of the component on your Windows 64. The x64 version of the component is for x64 applications only. The MS Access/Excel is still a 32-bit application, and so it runs on WOW64 on Windows 64, so only 32-bit components are visible. 

In order to fix or run any of samples you need to download and install the /COM (32-bit) version of the component. The 64-bit version of the component is visible for 64-bit applications only, and so the component is actually not visible for 32-bit applications such as MS Access/Excel.  

An ActiveX event is not triggered during the Form_Open event, in MS Access/Excel.

The Open event occurs when a form is opened, but before the first record is displayed. The Open event occurs before the Load event, which is triggered when a form is opened and its records are displayed. If you're trying to decide whether to use the Open or Load event for your event procedure, one significant difference is that the Open event can be canceled, but the Load event can't. Another significant difference is that the events of an ActiveX control are not triggered during the Open event, but they are fired during the Load event. You can do the following test using a simple Microsoft Calendar Control like: 

  • create a form
  • insert a Calendar Control to the form
  • paste the following snippet of code in the form
Option Compare Database

Dim strEvent As String
Private Sub ActiveXCtl0_NewYear()
    MsgBox strEvent
End Sub

Private Sub Form_Load()
    strEvent = "NewYear event during Form_Load"
    ActiveXCtl0.Year = 1971
End Sub

Private Sub Form_Open(Cancel As Integer)
    strEvent = "NewYear event during Form_Open"
    ActiveXCtl0.Year = 1971
End Sub
  • save the form
  • run the form

The message "NewYear event during Form_Load" will be displayed when you are running the form, which means that the ActiveX events are not fired during the Open event. They are fired during the Form_Load event.

The ActiveX control is destroyed if the Visible property is called, in MS Access/Excel

The MS Access/Excel extended control implements the Visible property, and other properties such as Width, Height, Left, Top and so on. It's up to the container how the Visible property is implemented. In a MS Access/Excel container, calling the Visible property on False destroys or closes the ActiveX control. This is valid for any ActiveX control, not only of ours. For instance, the VB6 which is a similar container, the Visible property hides the control as it should

The solution to hide an ActiveX control in MS Access/Excel container, is one of the following:

  • Adds or subtract from the Left or  Top property a value such as 16000 so the visible part of the control is not displayed on the form, like in the setVisible method listed bellow.
  • Call control's Width or/and Height  property on 0, that makes the size of the control to be empty, so nothing is displayed. This has the disadvantage that it resizes the control.

The setVisible method adds 16000 to Left property if the control gets hidden, or subtract 16000, if the control gets visible:

Private Sub setVisible(ByVal o As Object, ByVal v As Boolean)
    With o
        .Left = IIf(v, -16000, 16000) + .Left
        .Enabled = v
    End With
End Sub

So any time, you need to call the Visible property, just call the setVisible( object, true/false ) method.

I am using your control on a Tab control. However, when I change to a second page, and back again, the control is empty and effectively appears to be totally uninitialised. What could be the problem?

Due old implementation of the MS Access/Excel's Tab control, the control hosted by a page is destroyed instead hiding, when a new page is selected or unselected. The solution is removing the control from the Tab's Page, so the control's container will be the Form itself, and calling the Width or Left property of the control to change the width or the position of the control when a page is selected or unselected. Select the control on design mode, right click, and select Cut from its context menu. Click the Form, and then Paste. This way the control is hosted by the form instead Tab's page. Click the Pages in design mode, and if the control is still visible, that means that the control is properly hosted by the form as it should for the solution. If the control is hidden, when selecting a new page, it means that the control is still hosted by the page. Also, you can use the Tab order to change the Z-Order of the controls in the form, so the control will be shown over the Tab. 

The following Access sample hides the control when a new page is selected:

Private Sub TabCtl1_Change()
    With Tree1
    If (.Width > 0) Then .Tag = .Width
        .Width = IIf(TabCtl1.Value = 0, .Tag, 0)
    End With
End Sub

The sample saves the control's Width to the Tag property and change the Left property to 0 or restore back the value when a new page is selected. This code does not require re-initialization of the control. Instead, if using the Visible property of the control is not a solution, as it destroys the control on Access, so a new initialization is required.

I get the message "User-defined type not defined." when loading the form. Am I doing something wrong?

If you pasted some VB code in your Access code, please check the syntax for each event in your form. In MS Access/Excel the syntax for the events is a bit different than VB syntax. For instance, in VB the syntax for OleDragDrop event is Private Sub Grid1_OLEDragDrop(ByVal Data As EXGRIDLibCtl.IExDataObject, Effect As Long, ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) and in Access the right syntax  is Private Sub Grid1_OLEDragDrop(ByVal Data As Object, Effect As Long, ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Long, ByVal Y As Long). As you can notice the type for Data parameter differs. 

In order to find out the right syntax for the control's event in your Access environment, please do the following:

  • create a new form
  • insert the component to the form
  • select the 'Build Event...' from the control's context menu in design mode
  • select the component from the list of components ( upper left combobox )
  • select the event from the list of events ( upper right combobox )
  • Access adds the an empty handler for the selected event

The properties Screen.TwipsPerPixelX and Screen.TwipsPerPixelY are missing in MS Access/Excel, is there any way to replace them?

Usually you need the Screen object to convert the coordinates from twips to pixels as they are required for some properties such as ItemFromPoint, ColumnFromPoint, WordFromPoint, AnchorFromPoint Chart.DateFromPoint, Chart.LevelFromPoint, Chart.NoteFromPoint, Chart.LinkFromPoint, Chart.TimeZoneFromPoint, Chart.HistogramValueFromPoint and so on. 

All of these properties support -1 for X and Y parameters, so instead calling the 

  • ItemFromPoint(X / Screen.TwipsPerPixelX, Y / Screen.TwipsPerPixelY,...) 

you should use the 

  • ItemFromPoint(-1,-1, ... ) 

as in the following sample:

The following Access sample displays the caption from the point being double clicked:

Private Sub Grid1_DblClick(ByVal Shift As Integer, ByVal X As Long, ByVal Y As Long)
    With Grid1
        Dim c As Long, hit As Long, h As Long
        h = .ItemFromPoint(-1, -1, c, hit)
        If (h <> 0) Then
            With .Items
                MsgBox .CellCaption(h, c)
            End With
        End If
    End With 

However, if you really need a converter from twips to pixels, please use the following code. 

The article ACC2000, How to convert Twips to Pixels, explains what you have to do. Anyway, here's the Twips2Pixels function that converts X and Y twips coordinates to pixels coordinates.

I get 'Type mismatch' error, when casting the control's object to the control's type. What's wrong?

Usually it is happen when you are trying something like:
Dim o As EXGRIDLib.Grid
Set o = Grid1

instead

Dim o As EXGRIDLib.Grid
Set o = Grid1.Object

The Access, as VB or VFP and other environments, implements an extended control that hosts the original component. For instance, the Visible, Top, Left, ... or Object property is implemented by this extended control, NOT by component. The component is implemented by us, the extended control is implemented by the container. The Object property gets back the original component, without its extended control. In the first sample the message occurs, because the type of the Grid1 object is not EXGRIDLib.Lib, it is _Control, in most of the cases. You can find exactly the name of the type for sure using the Interfaces property o f the Exontrol's eXPropertiesList control, like follows:

MsgBox P1.Interfaces(Grid1)

where the P1 is the eXPropertiesList control, and the Grid1 is the object being queried.

I get "Type mismatch" error, if I use the ItemFromPoint property. How can I use it?

Usually it is happen when you paste code from the eXHelper
Private Sub Grid1_Click()
    With Grid1
        h = .ItemFromPoint(-1, -1, c, hit)
        Debug.Print (.Items.CellValue(h, c))
    End With
End Sub

and this is happens because the h, c and hit parameters are not declared (properly)

Private Sub Grid1_Click()
    With Grid1
        Dim h As Long, c As Long, hit As Long
        h = .ItemFromPoint(-1, -1, c, hit)
        Debug.Print (.Items.CellValue(h, c))
    End With
End Sub

or

Private Sub Grid1_Click()
    With Grid1
        Dim h As EXGRIDLib.HITEM, c As Long, hit As EXGRIDLib.HitTestInfoEnum
        h = .ItemFromPoint(-1, -1, c, hit)
        Debug.Print (.Items.CellValue(h, c))
    End With
End Sub

I get 'Procedure declaration does not match description of event or procedure having the same name.' error. What's wrong?

This is usually happen when pasting VB6 code for events in your Access code.

For instance, the VB6 code event is as follow

Private Sub G2antt1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

while in Access the code should look like:

Private Sub G2antt1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Long, ByVal Y As Long)

The VBA Access can automatically generate the event you need to handle, so please follow the steps:

  1. Select the component in design mode,
  2. Right Click, and then Select the Build Event.
  3. Select the G2antt1 component in the first drop down list,
  4. and in the second drop down list, select the MouseMove.

The right syntax for the selected event is being generated and added to the code.

Please pay attention when you copy the code from help files, as sometimes the VB6 code is not exactly the syntax Access is required, as sometimes it gives "User-defines type not defined.", when using wrong syntax for events.

For instance, in VB6 the syntax for BarParentChange event is:

Private Sub G2antt1_BarParentChange(ByVal Item As EXG2ANTTLibCtl.HITEM, ByVal Key As Variant, ByVal NewItem As EXG2ANTTLibCtl.HITEM, Cancel As Boolean)
End Sub

while in Access, the syntax should be:

Private Sub G2antt1_BarParentChange(ByVal Item As Long, ByVal Key As Variant, ByVal NewItem As Long, Cancel As Boolean)
End Sub

Drag-and-Drop operations for /COM components

Drag-and-drop is one of the fundamental metaphors underlying the Microsoft® Windows® family of operating systems. Users understand that some items can be moved around by holding the mouse down on them, and that they'll get appropriate visual feedback when they're over a spot where the item can be dropped. They expect to be able to move data and images from one spot to another this way. There are two steps required in order to be able to drag and drop items/objects/bars from our /COM components as follows:

Beginning a Drag-and-Drop Operation

To begin a drag-and-drop operation, you have to set the control's OLEDropMode property on 1 and to handle the OLEStartDrag event. The AllowedEffects parameter must be set on 1 or 2, and you need to call the SetData method of the Data parameter so you specify the data to be dragged.

Accepting Data From a Drag Operation

The control fires the OLEDragDrop event, when the user drags data to the control. The OLEDragDrop event occurs when a drag-and-drop operation is in progress (that is, some control has initiated a drag and drop operation) and the cursor enters the control.

The following Access sample shows the requirements in red:

Private Sub Form_Load()
    Grid1.OLEDropMode = exOLEDropManual
End Sub

Private Sub Grid1_OLEDragDrop(ByVal Data As Object, Effect As Long, ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Long, ByVal Y As Long)
    Dim hI As HITEM
    hI = CLng(Data.GetData(exCFText))
    If (hI <> 0) Then
        With Grid1
            Dim c As Long, hit As HitTestInfoEnum
            Dim h As Long
            h = .ItemFromPoint(-1, -1, c, hit)
            If (h <> 0) Then
                .BeginUpdate
                With .Items
                    .SetParent hI, h
                    .ExpandItem(h) = True
                    .SelectItem(hI) = True
                End With
                .EndUpdate
            End If
        End With
    End If
End Sub

Private Sub Grid1_OLEStartDrag(ByVal Data As Object, AllowedEffects As Long)
    With Grid1
        Dim c As Long, hit As HitTestInfoEnum
        Dim h As Long
        h = .ItemFromPoint(-1, -1, c, hit)
        If (h <> 0) Then
            AllowedEffects = 2
            Data.SetData h, exCFText
        End If
    End With
End Sub

The sample lets user moves an item from another by drag and drop using the SetParent method of Items object. The OLEStartDrag event initiates the OLE Drag-Drop event, by carrying the handle of the item being moved from a parent to the item where the cursor is released.

The MS Access/Excel crashes if passing a local variable to a method. What can be done?

You may encounter a crash of MS Access/Excel when passing a local variables to methods of the control. The crash is generally caused due accessing a memory variable that is not valid anymore. The local variables in the event handler are passed by reference instead value. For instance, if you have a Dim s as String, and later you passing this variable to some method, instead the value s, the MS Access passes the reference of the s, which later is not accessible as being most usual declared in the stack. The solution is passing the variables by values, using the CStr function like explained in the following samples.

Let's say that you are adding a new bar when OLEDragDrop event occurs, so your code is:

Private Sub G2antt0_OLEDragDrop(ByVal Data As Object, Effect As Long, ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Long, ByVal Y As Long)
    Dim h As HITEM
    Dim c As Long, hit As HitTestInfoEnum
    Dim s As String
    If Data.GetFormat(exCFText) Then
        s = Data.GetData(exCFText)
        With G2antt0
            .BeginUpdate
            h = .ItemFromPoint(-1, -1, c, hit)
            If h > 0 And hit = exHTItemChart Then
                With .Items
                    Dim d As Date
                    d = G2antt0.Chart.DateFromPoint(-1, -1)
                    .AddBar h, "Task", d, DateAdd("d", 2, d), s, s
                End With
            End If
            .EndUpdate
        End With
    End If
End Sub

Everything seems to be correct, excepts that MS Access/Excel passes the s local variable as a reference, so when the control is trying to access later, it is not longer valid ( as it is declared in the event's stack), so when the event is done, the address can not be referred anymore. Simple converting the s value to a CStr will create a copy of the string, that for sure will be passed by value, so the code will run just fine.

So finally the change required is replacing the s with CStr(s) as in the following snippet:

Private Sub G2antt0_OLEDragDrop(ByVal Data As Object, Effect As Long, ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Long, ByVal Y As Long)
    Dim h As HITEM
    Dim c As Long, hit As HitTestInfoEnum
    Dim s As String
    If Data.GetFormat(exCFText) Then
        s = Data.GetData(exCFText)
        With G2antt0
            .BeginUpdate
            h = .ItemFromPoint(-1, -1, c, hit)
            If h > 0 And hit = exHTItemChart Then
                With .Items
                    Dim d As Date
                    d = G2antt0.Chart.DateFromPoint(-1, -1)
                    .AddBar h, "Task", d, DateAdd("d", 2, d), CStr(s), CStr(s)
                End With
            End If
            .EndUpdate
        End With
    End If
End Sub

IN the same context, we can use the Data.GetData directly, as in the following sample:

Private Sub G2antt0_OLEDragDrop(ByVal Data As Object, Effect As Long, ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Long, ByVal Y As Long)
    Dim h As HITEM
    Dim c As Long, hit As HitTestInfoEnum
    If Data.GetFormat(exCFText) Then
        With G2antt0
            .BeginUpdate
            h = .ItemFromPoint(-1, -1, c, hit)
            If h > 0 And hit = exHTItemChart Then
                With .Items
                    Dim d As Date
                    d = G2antt0.Chart.DateFromPoint(-1, -1)
                    .AddBar h, "Task", d, DateAdd("d", 2, d), Data.GetData(exCFText), Data.GetData(exCFText)
                End With
            End If
            .EndUpdate
        End With
    End If
End Sub

 

I get the error "Can't assign to a read-only property", if I try to assign a value to a property. Is this normal?

For instance, let's say that the control provides the Layout property, which saves or loads the control's layout, such as positions of the columns, scroll position, filtering values. The extended control in MS Access adds properties like Visible, Left, Top, ..., Layout and so on. The Layout property returns the type of layout for the specified control, so it's a read only property. If you try to assign a value to a read-only property the "Can't assign to a read-only property" error occurs. In order to solve the problem you need to use the Object property of the extended control to access the contained/original object like in the following sample:

Use the following syntax:

List1.Object.Layout = ....

rather than:

List1.Layout = .... 

No other event is fired while the code is executed during an event

This is a problem of MS Access environment. While MS Access is executing code during the object's event, all other events of the object are locked, so they will not be fired.

Let's say we have the Microsoft Calendar Control 8.0 ( mscal.ocx ) placed to a form and we have the code:

Private Sub Calendar1_Click()
    Calendar1.Month = 1 + (Calendar1.Month) Mod 12
End Sub

Private Sub Calendar1_NewMonth()
    MsgBox "Calendar1_NewMonth event" & Calendar1.Month
End Sub

The sample should displays a message box when the user clicks the control. In MS Access, the message box never occurs, as the NewMonth event is called during the Click event. If the exactly the same code is called on VB6, VB/NET the MessageBox is shown each time the user clicks the control. In conclusion, the MS Access can not handle events inside other events.

How can we solve this? An idea could be using the form's TimerInterval property which will solve the problem like in the following sample:

Private Sub Calendar1_Click()
    Me.TimerInterval = 10
End Sub

Private Sub Calendar1_NewMonth()
    MsgBox "Calendar1_NewMonth event" & Calendar1.Month
End Sub

Private Sub Form_Timer()
    Me.TimerInterval = 0
    Calendar1.Month = 1 + (Calendar1.Month) Mod 12
End Sub

If we run the sample, the MessageBox occurs each time the user clicks the control, and this is what we intended to do.

An error occurred while loading 'Form_...'. Do you want to continue loading the project?

Unfortunately, this error may occur due missing code in the MS Access database. If you got the "An error occurred while loading 'Form_...'. Do you want to continue loading the project?" error, click Yes, and the most probably you get "Compiler Error" right-after at the end of the code, and if you look closely, the code is not complete. In order to fix this, please contact us, and we will provide a different database to include the code for all samples.

'The expression Event you entered as the event property setting produced the following error: There was an error loading an ActiveX control on one of your forms or reports'. Why it occurs?

The following error may occur:

if you have installed a newer version of the control, while the form you run has been created / saved with a previously version of the control.

In order to solve this error, open the form in design mode, and press CTRL + S (save). This way the new control is serialized on the form, and running again the form will go smootly.

Copyright 1999-2017 Exontrol. All rights reserved.