Macros Excel – Cómo crear un menú

En las últimas dos notas vimos cómo automatizar una tarea con macros en Excel, como hacer la macro disponible para todos los cuadernos en la sesión y como convertirla en un complemento (Add-in). Esto último nos permite usar y distribuir la macro eficientemente.
Para activar la macro del complemento mostramos como crear una barra de herramientas cada vez que instalamos el complemento.
Otro método, que personalmente prefiero, es agregar un menú en la barra de menús.
Normalmente la barra de menús se ve así




En esta nota veremos una técnica relativamente sencilla para agregar un nuevo elemento en la barra de menús, desde donde podremos activar nuestras macros



Como se puede ver, hemos agregado un nuevo menú, "Mis Macros", entre los menús "Ventana" y "Ayuda". Si pulsamos el menú veremos dos entradas



Cada entrada activa una macro.

La técnica que hemos usado es la propuesta por John Walkenbach. La técnica consiste en almacenar todos los datos del menú en una hoja Excel; luego en un módulo de Vba escribir una rutina para crear el menú de acuerdo a los datos de la hoja y otra rutina para borrar el menú cuando cerramos la aplicación.
Una vez creadas la hoja y la rutina, agregamos eventos para correr las macros de creación y borrado del menú. Finalmente convertimos el archivo en complemento, que luego podremos instalar-

Veamos el ejemplo. Hemos creado una macro, N_Gris, para poner negrita y fondo gris a un rango. Una segunda macro, Saludar, nos saludará con la fórmula adecuada de acuerdo a la hora del día.

Una vez creadas las macros, creamos la hoja "Menu" que guardará los datos del menú.



La hoja Menu usa cinco columnas

Level (Nivel): determina la ubicación de la entrada en el menú en forma jerárquica.
Caption (Título/Subtítulo): es el texto que aparece en la entrada del menú.
Position/Macro (Posición/Macro); en el nivel 1 debe ser un número entero que determina la posición en la barra de menús.
Divider (Divisor): Si queremos que aparezca una línea divisora entre dos entradas del menú ponemos TRUE.
FaceID: es el número que representa la imagen/icono que queremos que aparezca al lado del texto.

La macro que crea el menú, es la siguiente

Sub CreateMenu()

   Dim Menu As Worksheet
   Dim MenuObject As CommandBarPopup

   Dim MenuItem As Object
   Dim SubMenuItem As CommandBarButton
   Dim Row As Integer
   Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set Menu = ThisWorkbook.Sheets("Menu")
''''''''''''''''''''''''''''''''''''''''''''''''''''

  ' Make sure the menus aren't duplicated
   Call DeleteMenu

  ' Initialize the row counter
     Row = 2

  ' Add the menus, menu items and submenu items using
  ' data stored on Menu

     Do Until IsEmpty(Menu.Cells(Row, 1))
     With Menu
     MenuLevel = .Cells(Row, 1)
     Caption = .Cells(Row, 2)
     PositionOrMacro = .Cells(Row, 3)
     Divider = .Cells(Row, 4)
     FaceId = .Cells(Row, 5)
     NextLevel = .Cells(Row + 1, 1)
    End With

     Select Case MenuLevel
       Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
   Set MenuObject = Application.CommandBars(1). _
   Controls.Add(Type:=msoControlPopup, _
   Before:=PositionOrMacro, _
   Temporary:=True)
   MenuObject.Caption = Caption

     Case 2 ' A Menu Item
     If NextLevel = 3 Then
   Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
   Else
   Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
   MenuItem.OnAction = PositionOrMacro
   End If
   MenuItem.Caption = Caption
'If FaceId <> "" Then MenuItem.FaceId = FaceId
   If Divider Then MenuItem.BeginGroup = True

   Case 3 ' A SubMenu Item
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub

Para borrar el menú usamos esta macro

Sub DeleteMenu()
' This sub should be executed when the workbook is closed
' Deletes the Menus
Dim Menu As Worksheet
Dim Row As Integer
Dim Caption As String

On Error Resume Next
Set Menu = ThisWorkbook.Sheets("Menu")
Row = 2
Do Until IsEmpty(Menu.Cells(Row, 1))
If Menu.Cells(Row, 1) = 1 Then
Caption = Menu.Cells(Row, 2)
Application.CommandBars(1).Controls(Caption).Delete
End If
Row = Row + 1
Loop
On Error GoTo 0
End Sub

En el objeto ThisWorkbook ponemos estos dos eventos

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteMenu
End Sub

Private Sub Workbook_Open()
    Call CreateMenu

    MsgBox "Se ha agregado el menú Mis Macros delante del menu de Ayuda", vbInformation

End Sub

Finalmente, guardamos el cuaderno como complemento, pasamos al editor de Vba, cambiamos la propiedad IsAddin del objeto ThisWorkbook a TRUE y guardamos el proyecto con el menú Archivo del editor. Todo este proceso está explicado en la nota anterior.

Para instalar el complemento, usamos el menú Herramientas—Complementos. Si el complemento no aparece en la lista, usamos el botón Examinar para instalarlo. Una vez instalado veremos este mensaje



y el menú "Mis Macros" aparecerá en el lugar planeado

Technorati Tags: MS Excel