We all know how difficult it was to customize (or personalize, if you prefer) Office 2007. That changed a lot in Office 2010 and everything has become much, much simpler. A major change in this customization of the Ribbon is related to the scaling of groups. Previously, only the standard groups in the Ribbon could collapse. Now, Microsoft introduced the autoScale attribute, which is used to determine if a custom group should or should not collapse when we reduce the size of the Ribbon or the resolution of the screen is reduced.

Check out the picture below (I am actually using Office 2010 in Portuguese, but you will get the picture, if you forgive me the pun):

Figure 1: A Ribbon custom Group expanded

The group "Minhas Ferramentas (My Tools)" at the moment is expanded as usual. But assuming that the configuration of your screen or monitor is changed, you would get the following result with the autoScale set to "true" (autoScale="true"):

Figure 2: Custom Ribbon Group collapsed

If we keep reducing the Ribbon, the group is continuously reduced in size, and it further collapses the elements of our group:

Figure 3: Buttons collapsed to a splitButton

To implement this customization / personalization of the Ribbon just add the attribute autoScale and set its value as "true". Note that this attribute must be determined for each group separately, it does have a global effect on other groups in the Ribbon.

Can't install the Microsoft Office Webservices Toolkit for Microsoft Office 2003 because you are using Office 2007? This is a problem I came across a few years ago and the solution is simpler than one might expect. However, I had uninstalled it and kep the newer version saved under my back ups. Because of that, I decided to document this just in case I forget it again...

If you are trying to install version 2.01; it will fail. Instead, you should install Microsoft Office Web Services Toolkit 2.0

You can find this Toolkit here: http://www.microsoft.com/downloads/details.aspx?familyid=4922060f-002a-4f5b-af74-978f2cd6c798&displaylang=en

Have you ever wondered how to move items between listboxes in Microsoft Access using VBA? How about filtering listbox items as you type?

 

Using VBA in Microsoft Access, the objectives of this article are (Watch the following YouTube movie to see it in action http://www.youtube.com/watch?v=n0ykubD6e8g or check the bottom part of the article) :

 

·         Clear a listbox in Microsoft Access

·         Move items between  two listboxes in Microsoft Access

·         Filter listbox as you type

·         Move listbox items with double click

 

Before you continue, you will need the following setup:

 

·         Add VBA reference to Microsoft ActiveX Objects X.x (Where X.x is the version registered in your machine)

·         Add a table (name it tblNames) with the following fields:

o   IDName

o   FullName

o   ShowYesNoFilter (set the default value to “Yes”)

·         Create two queries and name them as follows

o   tblNames QueryNo (set the filter criteria to “No” and order ascending)

o   tblNames QueryYes(set the filter criteria to “Yes” and order ascending)

·         Create a form and the following controls

o   Textbox (name it “txtFilter”)

o   Listbox 1

§  Name it “ListNoItems”

§  Set its Row Source to tblNames QueryNo

§  Set its Row Source type to Table/Query

o   Listbox 2

§  Name it “ListYesItems”

§  Set its Row Source to tblNames QueryYes

§  Set its Row Source type to Table/Query

o   Button 1

§  Name it “cmdAddOne”

§  Caption: >

o   Button 2

§  Name it “cmdAddAll”

§  Caption: >>

o   Button 3

§  Name: “cmdRemoveOne”

§  Caption: <

o   Button 4

§  Name: “cmdRemoveAll”

§  Caption: <<

 

Once you have done that, your setup will look like this:

Now, you can add the code as follows:

Option Compare Database
Option Explicit

'*********************************************************************************************************************
'CONSTANTS
Private Const mstr_MsgBoxTitle                  As String = "Meu projeto Access 2007"
Private Const mstr_MsgNoItemToMove              As String = "Não há item para mover ou item não foi selecionado..."
Private Const mstr_Yes                          As String = "Yes"
Private Const mstr_No                           As String = "No"
Private Const mstr_Filtered                     As String = "Filtered"

'*********************************************************************************************************************
'STRINGS
Private mstr_SQLInstruction                     As String

'*********************************************************************************************************************
'OBJECTS
Private mobj_ADODBRecordset                     As ADODB.Recordset

Sub ExecuteCommand(ByVal strExecuteSQL, ByVal strShowYesNoFilter As String)
   
    Set mobj_ADODBRecordset = New ADODB.Recordset
   
    With mobj_ADODBRecordset
        .Open strExecuteSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        If Not .BOF Then .MoveFirst
       
        Do While Not .EOF
            .Fields("ShowYesNoFilter").Value = strShowYesNoFilter
            .Update
            .MoveNext
        Loop
    End With
       
    Me.ListYesItems.Requery
    Me.ListNoItems.Requery
   
    mstr_SQLInstruction = ""
    Set mobj_ADODBRecordset = Nothing

End Sub

Private Sub cmdAddAll_Click()
    mstr_SQLInstruction = "SELECT tblNames.IDName, tblNames.FullName, tblNames.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "tblNames.ShowYesNoFilter FROM tblNames "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((tblNames.ShowYesNoFilter)='" & mstr_Yes & "'));"

    ExecuteCommand mstr_SQLInstruction, "No"
   
End Sub

Private Sub cmdAddOne_Click()
    Dim strSelectedItem                 As String
    Dim lngSelectedItemIndex            As Long
   
    On Error Resume Next
    If Me.ListYesItems.ListIndex = -1 Then
        MsgBox mstr_MsgNoItemToMove, vbInformation, mstr_MsgBoxTitle
        Exit Sub
    End If
   
    lngSelectedItemIndex = Me.ListYesItems.ListIndex
    strSelectedItem = Me.ListYesItems.ItemData(lngSelectedItemIndex)

    If Len(Me.ListYesItems.ItemData(lngSelectedItemIndex)) < 1 Then
        MsgBox mstr_MsgNoItemToMove, vbInformation, mstr_MsgBoxTitle
        Exit Sub
    End If
   
   
    mstr_SQLInstruction = "SELECT tblNames.IDName, tblNames.FullName, tblNames.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "tblNames.ShowYesNoFilter FROM tblNames "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((tblNames.FullName)='" & strSelectedItem & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_No

End Sub

Private Sub cmdRemoveAll_Click()
    mstr_SQLInstruction = "SELECT tblNames.IDName, tblNames.FullName, tblNames.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "tblNames.ShowYesNoFilter FROM tblNames "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((tblNames.ShowYesNoFilter)='" & mstr_No & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_Yes

End Sub

Private Sub cmdRemoveOne_Click()
    Dim strSelectedItem                 As String
    Dim lngSelectedItemIndex            As Long
   
    'On Error Resume Next
   
    If Me.ListNoItems.ListIndex = -1 Then
        MsgBox mstr_MsgNoItemToMove, vbInformation, mstr_MsgBoxTitle
        Exit Sub
    End If
   
    lngSelectedItemIndex = Me.ListNoItems.ListIndex
    strSelectedItem = Me.ListNoItems.ItemData(lngSelectedItemIndex)

    If Len(Me.ListNoItems.ItemData(lngSelectedItemIndex)) < 1 Then
        MsgBox mstr_MsgNoItemToMove, vbInformation, mstr_MsgBoxTitle
        Exit Sub
    End If
   
    mstr_SQLInstruction = "SELECT tblNames.IDName, tblNames.FullName, tblNames.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "tblNames.ShowYesNoFilter FROM tblNames "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((tblNames.FullName)='" & strSelectedItem & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_Yes
End Sub

Private Sub Form_Open(Cancel As Integer)
   
    mstr_SQLInstruction = "SELECT tblNames.IDName, tblNames.FullName, tblNames.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "tblNames.ShowYesNoFilter FROM tblNames "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((tblNames.ShowYesNoFilter)='" & mstr_No & "')) "
    mstr_SQLInstruction = mstr_SQLInstruction & "OR (((tblNames.ShowYesNoFilter)='" & mstr_Filtered & "'));"
   
    ExecuteCommand mstr_SQLInstruction, mstr_Yes
   
End Sub

Private Sub ListNoItems_DblClick(Cancel As Integer)
    cmdRemoveOne_Click
End Sub

Private Sub ListYesItems_DblClick(Cancel As Integer)
    cmdAddOne_Click
End Sub

Private Sub txtFilter_KeyUp(KeyCode As Integer, Shift As Integer)
    Dim strFilter                   As String

    strFilter = Me.txtFilter.Text
   
    Select Case KeyCode
        Case 8, 46

            mstr_SQLInstruction = "SELECT tblNames.FullName, tblNames.ShowYesNoFilter FROM tblNames "
            mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((tblNames.FullName) Not Like '%" & strFilter & "%') "
            mstr_SQLInstruction = mstr_SQLInstruction & "Or ((tblNames.ShowYesNoFilter) = '" & mstr_Filtered & "'));"
           
            ExecuteCommand mstr_SQLInstruction, mstr_Yes

            mstr_SQLInstruction = "SELECT tblNames.FullName, tblNames.ShowYesNoFilter FROM tblNames "
            mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((tblNames.FullName) Not Like '%" & strFilter & "%') "
            mstr_SQLInstruction = mstr_SQLInstruction & "And ((tblNames.ShowYesNoFilter) = '" & mstr_Yes & "'));"

            ExecuteCommand mstr_SQLInstruction, mstr_Filtered
       
        Case Else
            mstr_SQLInstruction = "SELECT tblNames.FullName, tblNames.ShowYesNoFilter FROM tblNames "
            mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((tblNames.FullName) Not Like '%" & strFilter & "%') "
            mstr_SQLInstruction = mstr_SQLInstruction & "And ((tblNames.ShowYesNoFilter) = '" & mstr_Yes & "'));"
           
            ExecuteCommand mstr_SQLInstruction, mstr_Filtered
    End Select
   
End Sub

 

 

Microsoft Office 2010 (Beta) has come with a nice new feature that allows any earthling to change the looks of his or her user interface, aka, the Ribbon. When Microsoft Office 2007 came into existance, changing the user interface meant getting one's hands dirty in XML code. In Office 2010 things changed for the better, although you still need XML for a more professionally looking customization.

Here’s how this new feature looks like:

Figure 1: Microsoft Office 2010 Ribbon Customization

This looks great and certainly makes life much easier when it comes to customizing the user interface. However, I think that this adds layers to our task. An alternative would be to add an option to the right-click such that we could easily remove a tab, group or button. Also, there should be a button to reset the Ribbon from the same right-click. Here’s how my Ribbon Customization idea would look like:

Figure 2: Remove/Reset Ribbon button straight from the right-click