Autor: Oscar Cronquist Artículo actualizado en mayo 06, 2019

Aynsley Wall pregunta:

Tengo una hoja de cálculo que utilizo para diferentes compañías 3. Lo que realmente me gustaría hacer es tener un menú desplegable con los tres nombres de la compañía: por ejemplo: Mcdonalds, Pizza Hut, Subway y luego, cuando elija a qué compañía será la hoja de cálculo, toda la información de contacto y el logotipo aparecerán como un encabezado en la parte superior de la hoja de cálculo. es posible?

Responder

Sí, es posible. La imagen animada de arriba muestra una hoja de trabajo que le permite al usuario seleccionar un valor usando una lista desplegable y el encabezado cambia en consecuencia. El encabezado y el pie de página se encuentran arriba y debajo de la cuadrícula de celdas, respectivamente, cuando imprime una hoja de trabajo.

La lista desplegable es una lista desplegable regular ubicada en la celda C2, el nombre y la dirección de la compañía llenan el encabezado izquierdo. El código postal y el nombre de la ciudad llenan el encabezado central y la imagen se muestra en el encabezado derecho.

Si descarga el libro de trabajo y las imágenes no se muestran, entonces necesita crear imágenes y guardarlas en la carpeta c: \ temp \ o cambiar las rutas y los nombres de archivo en la columna F Datos de la hoja de trabajo a las imágenes que desea usar.

El siguiente código de VBA es un código de evento que se guarda en un módulo de hoja de trabajo y no en un módulo normal. Ajuste la referencia de celda A1: A4 a continuación para que apunte a todos los valores del conjunto de datos.

Codigo de evento

El siguiente código de evento coincide con el nombre de la compañía seleccionada en la lista desplegable con el registro correcto en la hoja "Datos", la macro de evento guarda el número de fila de ese registro en una variable y usa ese número para extraer cada valor del registro .

Por último, guarda los datos en el encabezado. El encabezado contiene tres contenedores, izquierda, centro y derecha. Char (10) es el carácter de una nueva línea, separa el nombre de la empresa y la dirección en el contenedor de la izquierda. También se usa en el contenedor central para poner el código postal y la dirección de correo electrónico en una línea cada uno.

'This event code is executed when a cell value is changed.
Private Sub Worksheet_Change(ByVal Target As Range)

'Dimension variables and data types
Dim iRow As Single

'Check if target is cell C2
If Not Intersect(Target, Range("C2")) Is Nothing Then

   'Enable error handling
   On Error Resume Next
  
   'Save row number of matching cell in cell range A1:A4 to variable iRow
   iRow = Application.Match(Range("c2"), Worksheets("Data").Range("A1:A4").Value, 0)
   
   'Check if there has been an error, MATCH function returns an error if no value is found
   If Err <> 0 Then

      'Show a message box
      MsgBox "Company name not found!"

      'Stop event code
      Exit Sub
   End If

   'Disable error handling
   On Error GoTo 0

'Save text and picture from data table to header
   With ActiveSheet
  
      'Save company name and address to left header on active worksheeet
      .PageSetup.LeftHeader = Worksheets("Data").Range("A" & iRow).Text & Chr(10) & _
      Worksheets("Data").Range("B" & iRow).Text

      'Save zip code and email address to center header on active worksheeet
      .PageSetup.CenterHeader = Worksheets("Data").Range("C" & iRow).Text & " " & _
      Worksheets("Data").Range("D" & iRow).Text & _
      Chr(10) & Worksheets("Data").Range("E" & iRow).Text
      
      'Save picture to right header on active worksheeet
      .PageSetup.RightHeaderPicture.Filename = Worksheets("Data").Range("G" & iRow).Text
   End With
End If
End Sub

¿Dónde poner el código?

  1. Copia el código de arriba.
  2. Haga clic derecho en el nombre de la hoja.
  3. Haga clic en "Ver código" para abrir el módulo de la hoja de trabajo en el Editor VB.
  4. Pegue el código en el módulo de la hoja de trabajo.
  5. Salir del editor vba y volver a Excel.
Tenga en cuenta que guarde su libro con la extensión de archivo * .xlsm (macro habilitado) para preservar el código.

Fuente de datos

El conjunto de datos utilizado para llenar el encabezado se encuentra en la hoja de trabajo "Datos", la imagen de arriba muestra lo que contendrá el archivo descargado. La columna A contiene los nombres de las compañías.

La columna B contiene direcciones, los códigos postales de la columna C, los nombres de ciudad de la columna D, las direcciones de correo electrónico de la columna E y la columna F contiene las rutas de las carpetas y los nombres de los archivos de los logotipos de la compañía.

Reflexiones finales

Le recomiendo que use una tabla definida por Excel en los datos de la hoja, le permite hacer referencia solo al nombre de la tabla y al nombre del encabezado de la tabla. No es necesario ajustar las referencias de las celdas cuando agrega registros, la Tabla definida por Excel es dinámica.

Por ejemplo, la siguiente fila:

iRow = Application.Match(Range("c2"), Worksheets("Data").Range("A1:A4").Value, 0)

se convierte en

iRow = Application.Match(Range("c2"), Worksheets("Data").Range("Table1[Company Name]").Value, 0)

Descargar el archivo de Excel


Dynamic-header.xlsm