Excel - Escribir celda al modificar otra hoja

En un libro que tiene dos hojas de cálculo necesito modificar la celda F2 en la "Hoja2" cuando el usuario cambia cualquier celda de la columna B en la Hoja1... estoy tratando de hacerlo con una macro, pero no consigo que funcione.
En la ventana de código de Hoja1 escribo:

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 2) Then
Dato_A_Evaluar = Target.Value
End If
End Sub

Y en un módulo independiente:

Public Dato_A_Evaluar As String

Sub Escribir_Dato_A_Evaluar()
Sheets("Hoja2").Cells(2, 6).Value = Dato_A_Evaluar
End Sub

Pero me salta un error en la instrucción Sheets("Hoja2").Cells(2, 6).Value = Dato_A_Evaluar

En la ventana de código de Hoja1 escribe:


Y solucionado.


Saludos.
\\//_

Ahora el error salta en la instrucción application.enableevents=false, y la cosa es que traté de simplificar al máximo la consulta pensando que debía haber algún problema en el código y realmente parece que el conflicto está en algún otro proceso que no alcanzo a entender, voy a exponer la situación de nuevo...

Lo que pretendo en realidad consiste en lo siguiente:
Estoy tratando de generar una plantilla para seleccionar productos de una lista que incluye unas 7k referencias para lo que he configurado una validación de datos dinámica en la columna B de la Hoja1 con la intención de que al teclear algunos caracteres en alguna celda de la columna B y pulsar en el desplegable de los datos validados, se muestren solo los resultados que incluyan dichos caracteres... la validación de datos dinámica se ejecuta en Hoja2 a través de la función Filtrar de excel a partir del dato en F2, y para que la lista de productos esté disponible para todas las celdas de la columna B en Hoja1, pensé en escribir mediante código en F2 de Hoja2 cualquier valor que se cambie en columna B de Hoja1, y el error salta cuando pulso el desplegable tras introducir algunos caracteres en columna B de Hoja1 y empezar a correr el código del evento change, sin embargo, si en lugar de pulsar en el el desplegable de la validación de datos pulso intro y confirmo algunos caracteres en alguna celda de columna B de Hoja1, no salta ningún error (la validación de datos está configurada para que no salte ningún error ni aviso) aunque no consigo del todo el efecto que busco, ya que, para ver los productos que incluyen los caracteres introducidos, tengo que volver a la celda y abrir el desplegable sin teclear ningún otro caracter con el teclado, entonces la lista sí muestra correctamente los productos que incluyen los caracteres que anteriormente introduje en columna B... es decir, que consigo que funcione en dos tiempos cuando la rutina change se lanza tras pulsar intro o seleccionar una celda diferente con el ratón, pero no consigo que funcione cuando la rutina change se lanza al pulsar en el deplegable de la lista validada.

Adjunto un par de capturas para clarificarlo cualquier duda.

En cualquier caso, muchísimas graciaas.

Te adjunto dos libros con la macro, probados y funcionando correctamente.

El Libro_Prueba_1.xlsm si tiene las líneas de comando Application.EnableEvents = False y Application.EnableEvents = True
El Libro_Prueba_2.xlsm no tiene las líneas de comando Application.EnableEvents = False y Application.EnableEvents = True

El resto de la explicación no la acabo de entiendo por que tengo que imaginar lo que me explicas. Sube el libro e intento entenderlo.

Saludos.
\\//_

• Libro_Prueba_1.zip(12,3 KB)
• Libro_Prueba_2.zip(12,3 KB)

Como indicaba, la idea es generar una plantilla (todavía muy en construcción) desde la que poder seleccionar productos en la columna B de Hoja1 y que al teclear algunos caracteres el desplegable de opciones muestre solo las opciones que contienen dichos caracteres en la base de datos de Hoja2... y ahora lo que está ocurriendo es que al teclear algunos caracteres en cualquier celda de columna B y pulsar sobre el desplegable para ver las opciones, salta un error de ejecución.

• plantilla-oferta.zip(238,3 KB)

Te cuento todos los inconvenientes que veo.

Empiezo por la Hoja2

En la columna A hay una referencia que no puedo actualizar
En la columna B hay una fórmula que no entiendo que se pretende con ella.
En la columna D hay una fórmula desconocida: _xlfn._xlws.FILTER(). En ingles solo existe FILTER y FILTERXML y en español FILTRAR y XMLFILTRO.

Seguimos en la Hoja1

En la columna B de la fila 16 a la 20 las celdas tienen definida una lista desplegable con una función que no existe y que no define una lista porque solo toma la celda D2.

La fórmula: =_xlfn.ANCHORARRAY(Hoja2!$D$2)


Creo saber que es lo que pretendes hacer y lo veo muy rebuscado.
Seguro que se puede hacer de otra forma mucho menos complicada.

Voy a explicar lo que creo que quieres hacer:

- Quieres escribir un texto en la columna B de la Hoja1
- Que en la columna D de la Hoja2 filtre los códigos que contengan es texto introducido.
- A continuación la celda de la columna B de la Hoja1 se convierta en una lista con los datos de la columna D de la Hoja2.

Te adjunto dos soluciones posibles.

Para el fichero 1 hay una función que hace lo mismo en la columna D de la Hoja2 pero en mi versión de Excel no funciona y lo hago con macro.


Saludos.
\\//_

• plantilla-oferta_V1.zip(235,5 KB)
• plantilla-oferta_V2.zip(196,4 KB)

Lo que trato de hacer es efectivamente que la columna b de hoja 1 contemple la lista desplegable con los productos que incluyan los caracteres previamente introducidos en esa misma celda, y el procedimiento utilizado es el que se explica en este vídeo. https://www.youtube.com/watch?v=uE6iYUME6As
Y realmente lo he conseguido hacer funcionar a la perfección sin macros como se muestra en el vídeo, el problema surge al tratar de que sean un conjunto de celdas las que muestren ese mismo desplegable con la lista que corresponda en cada caso.