Dutch Gemini's Weblog Pages

August 22, 2012

VBA Add-Ins stop working after installing Security Patch MS12-060

Filed under: Excel,VBA,Windows — dutchgemini @ 11:34 am
Tags: , , , , , , ,

Last Wednesday August 15, Security Patch MS12-060 from Microsoft hit the community and created quite some panic, because all of a sudden Add-Ins developed in VBA for Excel as well as for other Office applications stopped working. Depending on the Add-In you could either get error messages, crippled command bars or ribbons, or no warning at all.

In this patch, Microsoft replaced the ActiveX Windows Common Controls file MSCOMCTL.OCX with a more secure version. The same file has caused trouble earlier this year (April, 10).

Luckily, it appears that the problems are being caused by a faulty registration of this file into the Registry and that re-registering this file makes them disappear. This is a copy from TechNet. Mind that you must register the file as an Administrator otherwise the registration will fail.

The fix for this problem is to re-register MSCOMCTL.OCX. To do this, follow these steps:

  1. Open an administrative-level command prompt. Click START, and type RUN in the run command line. On Windows Vista and Windows 7 you will see CMD.EXE appear in the quick search list. Right click CMD.EXE and left click Run As Administrator. A black command prompt opens.
  2. If on a 64-bit machine, type the following:

regsvr32.exe /u  C:\Windows\SysWOW64\MSCOMCTL.OCX

Press ENTER, then type

regsvr32.exe  C:\Windows\SysWOW64\MSCOMCTL.OCX

If on a 32-bit machine, type the following:

regsvr32.exe /u C:\Windows\System32\MSCOMCTL.OCX

Press ENTER, then type

regsvr32.exe C:\Windows\System32\MSCOMCTL.OCX

Each time you press ENTER you will see a small informative message. This is normal. The first ENTER unregisters the file, the second registers the file.

According to this same link, you may also need to remove a key in the registry (I did not add it because for me this was not necessary). You may have to repeat the above actions.

I also re-registered MSCOMCT2.OCX, although not mentioned in the article, but only after I did my VBA running Office 2007 on W7 resumed full operation.

Your Office program should work again after doing this.

Dutch

January 5, 2011

VBA Run-time error ‘Automation error Library not registered.’

Filed under: Programming,VBA — dutchgemini @ 4:49 pm
Tags: , , , ,

If you encounter the  VBA Run-time error ‘Automation error Library not registered.’ then between your VBA project’s references there is a reference to a file that is “MISSING”.

This is the case when for example a DLL, OCX or another VBA project used while developing in your application is not installed on the computer the application is being used. Obviously, you should never distribute applications without making sure that the components used are available on the target computers, but there are cases in which VBA takes care of resolving the problem automatically, one example are the Microsoft Office applications, where each version installs in a separate folder containing a number (for instance for Office 2003 (Release 11) you have C:\Program Files\Microsoft Office\Office11)

In general, each reference is correctly mapped to the right -and probably also most recent- component, since the GUID of each application does not change. But there are cases where the component does not correctly remap (because the GUID has changed or because it is not available). If this occurs, then you will see such “MISSING” reference.

To correct the problem, open the VB-IDE, go to your project references (Tools -> References), either scroll the list and put or remove a checkmark in front of the desired component or browse for a file on your disk.

Ah, if you have access to the project, otherwise you’re out of luck.

Tip: before you distribute your application, run a compile (Debug -> Compile). If there is a missing reference, VBE may make this problem evident by issuing a compile error “Can’t find project or library” on a function that you know is correct (I had that on the String() function, not to confuse with VBA.String()). When you close the compiler error, VBE will open the references dialog for you.

When the ‘Automation error Library not registered.’ triggers on registered libraries

The funny thing is that a missing reference impacts on your code when addressing the other references in the project. Suppose you want to set a reference to “Visual Basic for Applications” then you would write the following statement (“VBA” is the codename for this reference):

Dim oReference As Object
Set oReference = ThisWorkbook.VBProject.References("VBA")

Using a named index with missing references triggers the error. However, if you use a numeric index to obtain a reference object, the error does not trigger.

Dim oReference As Object
Set oReference = ThisWorkbook.VBProject.References(1) ' Usually VBA is number 1

Even next code may trigger the error. Note the use of ‘may’ and not ‘will’. This is because the ‘.Name‘ property does not exist for missing references, so as long as the item’s counter is smaller than the missing reference item you are indeed able to query this property. The code stops running as soon as ‘lItem’ matches the index of the missing reference:

Dim oReference As Object, lItem As Long
For lItem = 1 To ThisWorkbook.VBProject.References.Count
    If (ThisWorkbook.VBProject.References(lItem).Name = "VBA") _
    Then Set oReference = ThisWorkbook.VBProject.References(lItem)
Next lItem

If you really want to play safe, use the ‘.Guid‘ property. This property does always exist, but is also means that you need to know it:

Dim oReference As Object, lItem As Long
For lItem = 1 To ThisWorkbook.VBProject.References.Count
    If (ThisWorkbook.VBProject.References(lItem).Guid = _
       "{000204EF-0000-0000-C000-000000000046}") _
    Then Set oReference = ThisWorkbook.VBProject.References(lItem)
Next lItem

Of course, you can always iterate through the references using For…Each, but remember to use the ‘.Guid‘ property:

Dim oReference As Object
For Each oReference In ThisWorkbook.VBProject.References
    If (oReference.Guid = "{000204EF-0000-0000-C000-000000000046}") _
    Then Exit For
Next lItem

The Guid is needed when you want to programmatically add references (the 0,0 at the end instructs VBA to load the most recent reference for this Guid):

Dim oReference As Object
For Each oReference In ThisWorkbook.VBProject.References
    If (oReference.Guid = "{000204EF-0000-0000-C000-000000000046}") _
    Then Exit For
Next lItem
If oReference Is Nothing _
Then Set oReference = ThisWorkbook.VBProject.References.AddFromGuid("{000204EF-0000-0000-C000-000000000046}", 0, 0)

You can get a list of the Guids used by your application by iterating the collection of references.

Dutch.

December 30, 2008

Popup Menu on a Userform in Excel with VBA

Filed under: Excel,Programming,VBA — dutchgemini @ 8:52 pm
Tags: , , , , , ,

Article reviewed on July 05, 2012 – Contains working project at the end (link).

Ever thought possible to show a popup menu on an MSComCtl.ListView placed on a UserForm within an Excel Add-In (the ones ending with .XLA) using a right-click of the mouse, “just” VBA and no other Win32 function, class or wrapper? No? Then continue reading. If you think it is possible but you never tried or do not know how, read as well.

In my daily struggling with VBA I came finally on the “Golden Tip” that made it all possible. And, this method works on any type of control you place on the UserForm, as long as it can intercept mouse clicks (left, middle or right button is a matter of your choice).

History
I am developing an Add-In for Excel connecting to a database. The Add-In retrieves the data in pure or in aggregated form and lets you place the result as a matrix (records are rows and fields are columns) on a worksheet. The definition of what the Add-In retrieves from the database is defined as ‘query’. The query is also stored in the workbook, in a [very] hidden worksheet.

One of the features of the Add-In is allowing to slice and trim the result of a query by fetching only a few single rows of the resulting (ADODB) RecordSet. The slice and trim is defined as a ‘restriction’. The restrictions are managed via a UserForm which has the ListView with the individual restrictions in it.

Wish
My desire was adding a right-click popup menu letting me establish the logical link between a list item and its predecessor, selecting from this menu the ‘AND’ or the ‘OR’. I did not want to add another button control to avoid overloading the form (I already have 5 of such).

First implementation
After some investigation in Excel’s and MSDN’s help, I bounced into a popup menu provided natively by Excel via a CommandBar objects. I decided to use it. To obtain the functionality of the right-click popup menu on the ListView on my UserForm, I used:

  • The MouseUp Event of the ListView, where I could trap the right click and identify the current selected list item;
  • A CommandBar as the menu container, to be activated via the ‘.ShowPopup‘ method
  • Two CommandBarButtons to be placed ‘on’ the CommandBar, one for the AND and one for the OR
  • Subroutine for dealing with the factual changes, to be called via the ‘.OnAction‘ property of the CommandBarButton

In a popup menu, the 2 command bar buttons act as menu entries.

However, this code was not working. In other words, the popup menu did show, I could click but none of them triggered the execution of the associated subroutine procedures. Without raising any error. I was troubled.

Second and final implementation
On the Internet (which took some time in order to establish the right terms to feed into King Google) I got the hint I needed: instead of adding 2 generic command bar buttons, I had to create 2 private variables as command buttons in the declaration part of the UserForm using the ‘WithEvents’ to allow the buttons to react on my mouse click.

To make this example work, create a UserForm and place a ListView – a ListView ActiveX control 6.0, remember to add a reference in your project to Microsoft Common Controls 6.0 (SP6) – called ‘ListView1’. Somewhere in the code add one or more items to ListView1.

In the Declaration section of the UserForm:

Option Explicit ' Always recommended

Private WithEvents mButton1 As CommandBarButton ' Manages the first popup menu entry
Private WithEvents mButton2 As CommandBarButton ' Manages the second popup menu entry

In the MouseUp method of the ListView:

Private Sub ListView1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
                              ByVal x As stdole.OLE_XPOS_PIXELS, _
                              ByVal y As stdole.OLE_YPOS_PIXELS)
On Error Resume Next
    Dim wListItem    As MSComctlLib.ListItem
    Dim wListItemTag As String

    ' We intercept only the 'RightClick' without any button pressed.
    '
    If (Button <> xlSecondaryButton) Or (Shift <> 0) Then GoTo EndSub

    ' Locate the element. We have used the Tooltip to know if this element needs to be modified.
    '
    Set wListItem = lstCustomRestrictions.SelectedItem
    If wListItem Is Nothing Then Set wListItem = lstCustomRestrictions.HitTest(x, y)
    If wListItem Is Nothing Then GoTo EndSub

    ' This line protects the first entry in the ListView.
    '
    If (wListItem.Index = 1) Then GoTo EndSub

    wListItem.Selected = True
    wListItemTag = wListItem.Tag  ' The Tag contains a specially crafted object.

    ' Show the popup menu.
    '
    ShowPopupMenu wListItemTag    ' The 'Tag' indicates 'Time' or 'Dutch'.

EndSub:
    Set wListItem = Nothing
End Sub

The Click event of the 2 buttons declared in the form:

Private Sub mButton1_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
On Error Resume Next
    If (Ctrl.State = msoButtonDown) _
    Then
        ' It was down, i.e. 'checked', so we need to uncheck and void.
        '
        SetToolTipText vbNullString
    Else
        ' It was up, i.e. 'unchecked', so we need to check and set.
        '
        SetToolTipText Ctrl.Parameter
    End If
End Sub

Private Sub mButton2_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
On Error Resume Next
    If (Ctrl.State = msoButtonDown) _
    Then
        ' It was down, i.e. 'checked', so we need to uncheck and void.
        '
        SetToolTipText vbNullString
    Else
        ' It was up, i.e. 'unchecked', so we need to check and set.
        '
        SetToolTipText Ctrl.Parameter
    End If
End Sub

This is the first custom private procedure (called from the MouseUp subroutine):

Private Sub ShowPopupMenu(pLinkType As String)
On Error Resume Next
    Dim wPopupMenu As Office.CommandBar    ' Used for right-click on ListView1
    Dim wListItem  As MSComctlLib.ListItem

    ' First try to locate this commandbar. If it exists, then we destroy ad make it again.
    ' The name of the menu is not important but should be unique.
    '
    Set wPopupMenu = Application.CommandBars.Item("UserForm1Popup")
    If Not wPopupMenu Is Nothing _
    Then
        wPopupMenu.Delete
        Set wPopupMenu = Nothing
    End If

    ' Create the menu.
    '
    Set wPopupMenu = Application.CommandBars.Add(Name:="UserForm1Popup", _
                                                 Position:=msoBarPopup, _
                                                 Temporary:=True)
    wPopupMenu.Enabled = True

    ' Add the first child menu item to the popup
    '
    Set mButton1 = Nothing
    Set mButton1 = wPopupMenu.Controls.Add(Type:=msoControlButton, ID:=1, _
                                           Parameter:="Time", Temporary:=True)
    With mButton1
        .Caption = "Set Tooltip to current date/time (i.e. 'Now')"
        .Enabled = True
        If (pLinkType = "Time") _
        Then
            .FaceId = 990           ' Show check mark
            .State = msoButtonDown  ' Show pressed
        End If
        .Style = msoButtonIconAndCaption
        .Visible = True
    End With

    ' Add the second child menu item to the popup
    '
    Set mButton2 = Nothing
    Set mButton2 = wPopupMenu.Controls.Add(Type:=msoControlButton, ID:=1, _
                                           Parameter:="Dutch", Temporary:=True)
    With mButton2
        .Caption = "Set Tooltip to 'Dutch Gemini'"
        .Enabled = True
        If (pLinkType = "Dutch") _
        Then
            .FaceId = 990           ' Show check mark
            .State = msoButtonDown  ' Show pressed
        End If
        .Style = msoButtonIconAndCaption
        .Visible = True
    End With

    ' Show as a popup close to the mouse pointer.
    '
    wPopupMenu.ShowPopup

    ' Destroy the internal popup menu.
    '
    If Not wPopupMenu Is Nothing Then wPopupMenu.Delete
    Set mButton2 = Nothing
    Set mButton1 = Nothing
    Set wPopupMenu = Nothing
End Sub

And the second custom private procedure:

Private Sub SetToolTipText(pToolTipType As String)
On Error Resume Next
    Dim wListItem As MSComctlLib.ListItem

    Set wListItem = ListView1.SelectedItem
    If wListItem Is Nothing Then GoTo EndSub

    ' This line protects the first entry in the ListView.
    '
    If (wListItem.Index = 1) Then GoTo EndSub

    wListItem.Tag = pToolTipType
    Select Case pToolTipType
    Case "Time"
        wListItem.TooltipText = VBA.CStr(Now)
    Case "Dutch"
        wListItem.TooltipText = "Dutch Gemini"
    Case Else
        wListItem.TooltipText = ""
    End Select

    wListItem.Selected = False

EndSub:
    Set wListItem = Nothing
End Sub

That’s it.

For convenience I have made this sample project available as an Excel workbook which contains the necessary overhead for making the above work out-of-the-box. It also contains some code dealing with the positioning of the UserForm on multiple-monitor systems. Click here to download a copy of this document.

If you like it then you can make me aware of your appreciation by making a small donation to help supporting my work.

Create a free website or blog at WordPress.com.