Archive for the ‘CommandBars’ Category.

Dealing with Ribbons and Menus - Avoiding Two Versions

Hi all

When you open your Excel 97-2003 file in Excel 2007, one that creates its own menus,
the first thing you notice is that you don’t see your menus. Where’d they go? Then you discover
them - shunted over on the Add-ins tab. Not exactly the ideal user interface.
How are your users going to find them there?

What you want is an application that runs as a first class citizen in Excel 2007, one that takes full advantage
of the ribbon, and yet runs as it always did in Excel 2003. Yet you do not want to maintain two versions of
your app. Two versions are always a nightmare, so difficult to keep in sync.

So, is this possible? Is it possible to have one version that’s at home in Excel 2007 and Excel 2003? Yes it is;
there are two approaches from me and Jim Rech of this problem on this new page on my website.

http://www.rondebruin.nl/compatiblemenu.htm

If you have suggestions or other feedback let me know.

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Catching Paste Operations

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, since there are a zilion ways to paste.

I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.

Let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Table Drive Ribbon

Mike Alexander has updated his table driven Ribbon customizer. You can find it here:

http://www.datapigtechnologies.com/Custom_UI_Builder.zip

He says:

The utility now creates and adds a module with all the call-back functions to the newly created buttons, allowing the buttons to work right away.

As always, the source code is open for anyone who wants to build on this and create a better table-driven Ribbon Customizer.

If you try it out, post your comments here.

Excel 2007 Menu

Hi all

I add two pages to my site to create a menu in Excel 2007.

I changed John Walkenbach‘s superb menu maker example for 97-2003 to create the PopUp menu.
Very easy to add and edit menu items with this technique.

For one workbook
http://www.rondebruin.nl/qat2.htm

Menu for all your workbooks
http://www.rondebruin.nl/qat.htm

Suggestions are welcome

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Caller and Custom Commandbars

I learned some things today. Apparently I've never used Application.Caller in a procedure called from a custom CommandBarButton. Or if I have, and have since forgot about it. As it turns out, Application.Caller returns a Variant array with two elements. The first element seems like it should be the index number of the CommandBarButton from which the procedure was called. That is, if the second button on the CommandBar runs the procedure, the first element would contain a 2. The second element contains string that is the Caption of the CommandBar.

I say "seems" when I refer to the first element, because my CommandBar only contains four buttons.

?CommandBars("BBash").Controls.Count
4

Yet the first element of Application.Caller returns a seven. All of the controls are CommandBarButtons. There are no popups or any other kind of control. I can't imagine where it's getting 7. Does anyone know what I'm missing here?

If you're as surprised as I am that I didn't know Application.Caller returned an array, then you may be equally surprised at lesson #2 of the day. I can use a For Each construct to access the elements of an array as long as the control variable is Variant. I've always looped through arrays with For Next constructs like this

For x = LBound(arr) To UBound(arr)
    Debug.Print arr(x)
Next x

Now I know I could use this

For Each x In arr
    Debug.Print x
Next x

as long as x is declared as a Variant. I don't really plan to use this as it seems like an unnecessary use of a Variant.

Finally, an old nugget that I just don't use enough: multiple statements in the Immediate Window. In VBA procedures, you can put multiple statements on the same line by separating them with a colon. Since statements in the Immediate Window are executed as soon as you enter them, it's sometimes necessary to employ this technique. When I was trying to figure out what in tarnation Application.Caller was doing, I used a loop in the Immediate Window. To wit:

If you didn't know you could loop in the Immediate Window, then maybe you learned something today too.

Capture Deleted Rows

Excel doesn't provide events for the deletion of rows and columns. There are two methods that you can use to determine if a user deletes a row, and they are described here. If you want to detect column deletion, the process is the same, but some of the details change.

Monitored Public Variable

The first method counts the number of rows used in the spreadsheet, then recounts whenever something is changed. It uses the Worksheet_Change event which fires whenever the user changes any cell in the worksheet including row deletion. Start by creating a public variable in a standard module:

Public glOldRows as Long

This will hold the number of rows used in the spreadsheet before the change. We'll compare it to the number of rows after the change to determine if one has been deleted. In the worksheet's class module, set up an activate event and a change event.

Private Sub Worksheet_Activate()
    glOldRows = Me.UsedRange.Rows.Count
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
   
        If Me.UsedRange.Rows.Count <glOldRows Then
            MsgBox "Row deleted"
        End If
   
    glOldRows = Me.UsedRange.Rows.Count
   
End Sub

Use the dropdown boxes at the top of the code window to insert the Sub and End Sub statements. The activate event sets the initial value of the public variable whenever the sheet is activated. In the change event, the current number of rows are counted and compared to the public variable to determine if there are fewer.

One downside to this method is that you can't prevent the deletion, only know that it happened. There's also a lot of overhead associated with this method. The change event is fired every time something on the sheet is changed. You could work for days or years on this worksheet changing cells without deleting a row. All those precious computer cycles wasted.

Custom Class Module

The second method attempts to capture all the ways that a user can delete a row. The danger of this method is that you may miss a way. I can only see two ways to delete a row: the Delete control on the Row right click menu and the Delete control under the Edit menu. I don't know of any keyboard shortcuts to delete an entire row.

To use this method, we'll create a custom class module with two variable; one for each of the controls. We will then monitor the click events of those commandbar controls to determine if the user clicked them. Start by creating a custom class module (Insert > Class Module) . Rename it to CCbarEvents in the Properties Window (F4).

Next create a global variable to hold your custom class module - create this in a standard module:

Public gclsCbarEvents As CCbarEvents

and use the workbook's open event to initialize the class module whenever the workbook is opened:

Private Sub Workbook_Open()
   
    Set gclsCbarEvents = New CCbarEvents
   
End Sub

Now the class module will be "live" as long as the public variable is in scope, i.e. for as long as the workbook is open. Next we have to put some stuff in the class module that does some work.

Private WithEvents mRowDelButton As CommandBarButton
Private WithEvents mCellDelButton As CommandBarButton
 
Private Sub Class_Initialize()
 
    Set mRowDelButton = Application.CommandBars.FindControl(, 293)
    Set mCellDelButton = Application.CommandBars.FindControl(, 478)
   
End Sub
 
Private Sub mCellDelButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   
    If TypeName(Selection) = "Range" Then
        If Selection.Address = Selection.EntireRow.Address Then
            MsgBox Selection.Rows.Count & " Row" & _
                IIf(Selection.Rows.Count = 1, "", "s") & " deleted."
        End If
    End If
   
End Sub
 
Private Sub mRowDelButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   
    MsgBox Selection.Rows.Count & " Row" & _
        IIf(Selection.Rows.Count = 1, "", "s") & " deleted."
       
End Sub

I declare two variables using the WithEvents keyword to expose the events to our class module. The variables are assigned to the appropriate commandbar controls in the class' initialize event. I found the IDs of the commandbar controls by going to the immediate window and typing:

?application.CommandBars("Row").Controls("&Delete").Id
 293
?application.CommandBars(1).Controls("&Edit").controls("&Delete...").id
 478

Note that the caption for Edit > Delete changes if a row or column is selected instead of a cell. The class' initialize event is fired when I establish the class in the Workbook_Open event. Next I create the click events for the CommandBarButton variables. For mCellDelButton, I have to first make sure that an entire row (or rows) is selected because this command can be used to delete columns and non-row/column ranges, as well as a bunch of other stuff. I make sure that what is selected is a Range and that the address of the range is the same as the entire row(s).

For mRowDelButton, I know that a whole row is selected, because you can't show that menu otherwise. The action in each procedure is a message box, but you'll likely need something more substantive. Don't forget that you can set the CancelDefault argument of these event procedures to True to prevent the deletion from happening.

If I missed a way that a user can delete a row, or you have a better method for detecting row deletions, please leave a comment.