Worksheet_Change: pegado masivo, eventos desactivados e InputBox cancelado
En la hoja «Pedidos», la columna D recibe la cantidad y la columna F debe mostrar «OK» o «REVISAR». El procedimiento Worksheet_Change comprueba si la celda modificada está en D y escribe el resultado en F con Me.Range("F" & Target.Row).Value = ...; no usa Intersect ni desactiva eventos. Cuando un usuario pega 500 filas desde otro libro, aparece un MsgBox por cada fila y Excel parece bloqueado durante minutos. En otra versión, el código desactiva los eventos con Application.EnableEvents = False antes de escribir en F, pero un error de tipo 13 en mitad del proceso salta sin manejador: desde ese momento las validaciones dejan de ejecutarse sin que nadie lo note. Por último, la cantidad se pide con InputBox y se convierte con Val(Respuesta); al pulsar Cancelar se graba un 0.
1. ¿Por qué aparece un MsgBox por cada fila al pegar 500 filas? Rediseña el manejador para que el pegado se procese una sola vez y sin diálogos.
Read the worked answer
Qué llega en Target. Al pegar un bloque, Worksheet_Change se dispara una vez, pero Target es todo el rango pegado, no una celda. Si el código asume Target.Row y recorre celda a celda mostrando un mensaje, se generan 500 avisos modales. Cada MsgBox detiene la ejecución hasta que alguien lo cierre, por lo que Excel parece colgado cuando en realidad está esperando al usuario.
Además, cada escritura en F dispara el evento otra vez. Los eventos están activos mientras se ejecuta el manejador; escribir en la hoja desde dentro de Worksheet_Change vuelve a entrar en el procedimiento. Si la escritura cae fuera de la columna vigilada no hay recursión infinita, pero sí trabajo duplicado.
Rediseño:
- Filtrar la zona vigilada:
Dim zona As Range: Set zona = Intersect(Target, Me.Columns("D")): If zona Is Nothing Then Exit Sub. - Trabajar con
zonacompleta, no conTarget.Row: recorrer sus celdas, acumular resultados en una matriz y escribir en F una sola vez al final. - Desactivar eventos solo alrededor de la escritura:
Application.EnableEvents = False, escribir,Application.EnableEvents = True, con restauración garantizada. - Sustituir el
MsgBoxpor un estado en una celda, un recuento al final o, si de verdad hace falta avisar, un único mensaje resumen.
Resultado esperado: un pegado de 500 filas produce una sola pasada, sin diálogos y en menos de un segundo.
2. Si el procedimiento falla después de `Application.EnableEvents = False`, ¿qué ocurre exactamente y cómo se garantiza la restauración?
Read the worked answer
Qué ocurre. Los eventos de Excel quedan desactivados en la sesión: Worksheet_Change, Worksheet_SelectionChange, Workbook_Open y compañía dejan de dispararse. El usuario sigue editando la hoja con normalidad aparente, pero las validaciones, los sellos de fecha y los cálculos automáticos que dependían de eventos ya no se ejecutan. No hay ningún aviso: es un fallo silencioso que puede durar días. Solo se recupera reiniciando Excel o ejecutando manualmente una línea que vuelva a poner la propiedad en True.
Por qué no basta con ponerla a True al final del procedimiento. Si el error salta antes de esa línea, la restauración nunca se alcanza. Y un error de tipo 13 (tipos incompatibles) es habitual cuando se manipulan datos pegados.
Patrón seguro:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Salir
Application.EnableEvents = False
' ... trabajo ...
Salir:
Application.EnableEvents = True
If Err.Number <> 0 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
End If
End Sub
La etiqueta final se ejecuta tanto por salida normal como por error, y la restauración va antes de cualquier mensaje.
Recomendación adicional: mantener el bloque con eventos desactivados lo más corto posible y, si se puede, evitar escribir en la zona vigilada en lugar de desactivar. Otra opción es usar una variable de módulo como bandera (mProcesando) que el propio manejador consulte al entrar, aunque no cubre eventos de otras hojas.
3. El código pide la cantidad con `InputBox` y la convierte con `Val(Respuesta)`; al pulsar Cancelar se graba un 0. Explícalo y corrige también la lectura del separador decimal.
Read the worked answer
Cancelar y cadena vacía son indistinguibles con InputBox. La función InputBox devuelve una cadena; al cancelar devuelve "", exactamente igual que si el usuario hubiera aceptado sin escribir nada. Val("") devuelve 0 sin protestar, de modo que se graba una cantidad de 0 como si fuera un dato real. Es un error de datos, no de sintaxis.
Solución recomendada: Application.InputBox con tipo.
Dim resp As Variant
resp = Application.InputBox("Cantidad:", "Pedido", Type:=1)
If VarType(resp) = vbBoolean Then Exit Sub ' el usuario canceló
Con Type:=1 la función devuelve un Double ya convertido, y al cancelar devuelve False (Boolean), lo que permite distinguir la cancelación de un cero. Además rechaza el texto no numérico mostrando su propio aviso.
El problema del separador decimal. Val interpreta siempre el punto como separador decimal y se detiene en el primer carácter que no reconoce: en configuración española, Val("12,5") devuelve 12, no 12,5. Es una pérdida silenciosa de datos del 4 %. Application.InputBox con Type:=1 usa la configuración regional y convierte «12,5» correctamente. Si se trabaja con la cadena, usar CDbl (que respeta la configuración regional) en lugar de Val.
Validaciones que faltan. Comprobar que la cantidad sea mayor que cero, que no supere un máximo razonable y, si el pedido ya existe, si se desea sobrescribir. Cualquier fallo de validación debe cancelar la escritura, no grabar un valor por defecto.