commandbars en excel: agregar barras de herramientas con macros

No podemos negar la gran utilidad y facilidad en nuestro trabajo diario que representan las CommandBars (Barra de Herramientas). Estos pequeños íconos, generalmente colocados en la parte superior de la pantalla (debajo de los menus), son accesos directos, visualmente muy poderosos, a las principales herramientas y funciones de Excel.

[+/-] Ver el resto / Ocultar

Lo mejor es que podemos crear nuestras propias barras de herramientas, colocando en las mismas distintos controles (botones, listas desplegables) y asignarles código VBA (macros) creado por nosotros.

Esto llevará a nuestros proyectos a un nuevo nivel de personalización y profesionalismo, ofreciéndole al usuario final un entorno gráfico y funcional exactamente igual al que nos brinda Excel.

Por lo general estamos acostumbrados a grabar/programa una macro y condicionar su ejecución al "click" sobre un botón o a la activación de algún evento. Crear "nuestra" barra de herramientas nos hará posible ejecutar las macros en un entorno 100% "Excel".

Como ya es costumbre en mi blog, no ahondaré mucho en la teoría, mas bien apuntaré a casos prácticos y fáciles de entender, las dudas e inquietudes mas elevadas que pudieran surgir las canalizan mediante este blog y lo resolvemos enseguida.

Para VBA un Menu o una Barra de Herramientas es lo mismo: es una CommandBar, que es el principal objeto sobre el cual trabajaremos. Si bien tienen varias propiedades, métodos y eventos, revisaremos solos los principales y mas útiles, así pueden aplicar esto a su trabajo luego de pocos minutos de práctica.

El objeto CommandBar es, a su vez, contenedor de otros objetos: botones, listas desplegables, etc. Si observan la barra de herramientas de su Excel, en específico la herramienta "Zoom", o  "Tipo de Fuente" verán allí lo que es introducir una lista desplegable:

estos controles serán, junto a otros, los que "dispararán" nuestras macros.

Bueno, basta de teoría. Iremos viendo mas de ella sobre ejemplos prácticos.

Si normalmente deseo proteger una hoja debo ir a "herramientas / proteger / proteger hoja" e ingresar (dos veces) la respectiva contraseña. Supongamos que, vaya a saber por que motivo, se nos presenta un proyecto en el cual debemos realizar estos pasos en varias ocasiones, o bien cierta clase de usuarios administradores pueden tener acceso al desbloqueo de hojas. ¿Que mejor que evitar botones y agregar nuestra propia barra de herramientas, con dos botones: uno para proteger la hoja activa y otra para desprotegerla?

Veamos como se hace:

Sub crearBarraDeHerramientas()
'defino las variables de uso interno:
Dim cmdBar As CommandBar 'barra de herramientas
Dim Boton As CommandBarButton 'botones

'si la barra existe, la borro, para no duplicarla:
'coloco un controlador de errores para que no se
'detenga la macro en caso de NO existir
On Error Resume Next
Application.CommandBars("MiCommand").Delete
On Error GoTo 0

'bueno, así se crea una Barra de Herramientas, con el método
'Add, indicando Nombre y Posicion, para un mejor control:
Set cmdBar = Application.CommandBars.Add _
        (Name:="MiCommand", temporary:=True, Position:=msoBarTop)
'es necesario hacerla visible:
cmdBar.Visible = True
    
'ahora colocamos un botón en la barra:
'msoControlButton = boton
Set Boton = cmdBar.Controls.Add(msoControlButton)
With Boton
    'titulo que llevará:
    .Caption = "Proteger Hoja"
    'macro que ejecutará:
    .OnAction = "ProtegerHoja"
    'imagen del botón:
    .FaceId = 225
End With

'creamos otro botón:
Set Boton = cmdBar.Controls.Add(msoControlButton)
With Boton
    'nombre
    .Caption = "Desproteger Hoja"
    'macro
    .OnAction = "DesprotegerHoja"
    'imagen
    .FaceId = 277
End With

'destruyo las variables, para liberar recursos
Set cmdBar = Nothing
Set Boton = Nothing
End Sub

El código arriba escrito nos devuelve lo siguiente:

esta es nuestra pequeña barra de herramientas con dos botones: el del candado para bloquear la hoja con contraseña y el de la llave, para desbloquearla.

Habrán observado uno de los argumentos: msoBarTop. Con esto le indico que incorpore la CommandBar en la parte superior de la hoja, junto a las demás barras de herramientas, como se aprecia en la siguiente imagen:

Si utilizo el argumento msoBarFloating, la barra de herramientas permanecerá "flotando" en la hoja:

El tema de como agregar un ComboBox (lista desplegable) lo veremos en otras entradas, ya que posee sus "bemoles" y no es tan sencillo de programar como un simple botón.

Vieron el FaceId (identificador de imagen) que coloco en cada botón. Para beneplácito de todos.... son miles. Y encima, numéricos. Así que resulta bastante engorroso ir probando "de a uno" hasta "pegarle" con la imagen indicada; digamos que el candado y la llave que utilicé tienen cierta lógica con las funciones que desempeña cada botón. Para que no renieguen mucho y puedan observar todos los FaceIds existentes en VBA, les dejo el siguiente código:

Sub ShowFaceIDs()
    Dim NewToolbar As CommandBar
    Dim NewButton As CommandBarButton
    Dim i As Integer, IDStart As Integer, IDStop As Integer
    
'   si existe previamente, borramos la barra de herramientas
    On Error Resume Next
    Application.CommandBars("FaceIds").Delete
    On Error GoTo 0
    
'   agregamos una barra de herramientas vacía
    Set NewToolbar = Application.CommandBars.Add _
        (Name:="FaceIds", temporary:=True)
    NewToolbar.Visible = True
    
'   empiezo con el Id nro 1 y llego al 250.
'   pueden cambiar estos valores (ej: del 251 al 500)
'   para ver mas FaceIds (hay miles...)
    IDStart = 1
    IDStop = 250
    
    For i = IDStart To IDStop
        Set NewButton = NewToolbar.Controls.Add _
            (Type:=msoControlButton, ID:=2950)
        NewButton.FaceId = i
        NewButton.Caption = "FaceID = " & i
    Next i
    NewToolbar.Width = 600
End Sub

Esto les dará como resultado lo siguiente:

miles y miles de imagenes para elegir. posando el cursor sobre cada ícono tendrán el numero correspondiente y así sabrán cual utilizar.

Para ir cerrando la presente entrada, resta aclarar: noten que siempre destruyo una barra de herramientas antes de crearla. Evito la duplicidad y posibles errores. No lo olviden, para cuando encaren sus propios proyectos.
Tambien siempre al cerrar el libro (pueden aprovechar el evento BeforeClose de Workbook) destruyan las barras de herramientas y vuelvan a crearlas al abrir el libro o cuando el proyecto así lo requiera. Por que de lo contrario, y aunque no esté visible, la barra quedará guardada en Excel. Si quieren corroborarlo por cuenta propia, vayan al menú Ver / Barras de Herramientas y observarán como todas las barras creadas por nosotros persisten, aún cuando no estén visibles.
Para eliminar una barra utilicen el siguiente código:

CommandBars("FaceIds").Delete

en donde lo colocado entre comillas debe coincidir con el nombre de la barra a eliminar.

¿Algo mas? Si, el tema es interminable, pero lo seguiremos viendo en futuras entradas.