Ingrese la hora sin dos puntos - Consejos de Excel

Tabla de contenido

La pregunta de Excel de esta semana proviene de John estacionado en Okinawa.

Estoy creando una hoja de cálculo de Excel para reflejar las salidas y llegadas. Básicamente habrá tres celdas: Hora real de salida, Hora estimada en ruta y Hora estimada de llegada. Me gustaría que la persona pudiera ingresar (por ejemplo) 2345 y que la celda formatee automáticamente la pantalla para mostrar 23:45. Lo que obtengo en su lugar es 0:00, independientemente de la fórmula o el formato. Y el cálculo no mostrará nada más que 0:00 si el usuario no puede cambiar la tecla y los dos puntos. Sé que parece sencillo hacerlo, sin embargo, cada pequeño segundo guardado cuenta, especialmente cuando se ingresan datos similares una y otra vez en Excel.

Para que esto funcione, debe utilizar un controlador de eventos. Los controladores de eventos eran nuevos en Excel 97 y se analizaron en Ejecutar una macro cada vez que cambia el valor de una celda en Excel. Sin embargo, en ese consejo, el controlador de eventos estaba aplicando un formato diferente a ciertas celdas. Esta aplicación es ligeramente diferente, así que revisemos el controlador de eventos.

Un controlador de eventos es un pequeño fragmento de código macro que se ejecuta cada vez que ocurre un determinado evento. En este caso, queremos que la macro se ejecute siempre que cambie una celda. Para configurar un controlador de eventos, siga estos pasos:

  • Un controlador de eventos está asociado con una sola hoja de trabajo. Comience desde esa hoja de trabajo y presione alt-F11 para abrir el editor de VB.
  • En la ventana superior izquierda (Proyecto - Proyecto VBA) haga doble clic en el nombre de su hoja de trabajo.
  • En el panel derecho, haga clic en el menú desplegable izquierdo y cambie general a Hoja de trabajo.
  • En el menú desplegable de la derecha, elija Cambiar.

Esto hará que Excel ingrese previamente el siguiente shell de macro por usted:

Private Sub Worksheet_Change(ByVal Target As Range) UserInput = Target.Value If UserInput> 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End Sub

Cada vez que se cambia una celda, la celda que se cambió se pasa a este programa en la variable llamada "Destino". Cuando alguien ingresa un tiempo con dos puntos en la hoja de trabajo, se evaluará a un número menor que uno. El bloque If se asegura de cambiar solo las celdas si son mayores que uno. Utilizo las funciones left () y right () para dividir la entrada del usuario en horas y minutos e insertar dos puntos en el medio.

Siempre que el usuario ingrese "2345", el programa cambiará esta entrada a 23:45.

Posibles mejoras

Si desea limitar el programa para que solo funcione en las columnas A y B, puede verificar el valor de Target.Column y solo ejecutar el bloque de código si está en las dos primeras columnas:

Private Sub Worksheet_Change(ByVal Target As Range) ThisColumn = Target.Column If ThisColumn 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End If End Sub

Si alguna vez desea realizar cambios en la hoja de trabajo sin ingresar dos puntos (por ejemplo, si necesita agregar fórmulas o cambiar encabezados, etc.), puede desactivar el controlador de eventos con esta breve macro:

Sub TurnEventHanderOff() Application.EnableEvents = False End Sub You can turn event handlers back on with this macro: Sub TurnEventHanderOff() Application.EnableEvents = True End Sub

Si toma este concepto y lo cambia, hay un concepto importante que debe tener en cuenta. Cuando la macro del controlador de eventos asigna un nuevo valor a la celda a la que hace referencia Target, Excel lo cuenta como un cambio en la hoja de trabajo. Si no apaga brevemente los controladores de eventos, Excel comenzará a llamar de forma recursiva al controlador de eventos y obtendrá resultados inesperados. Antes de realizar un cambio en una hoja de trabajo en un controlador de eventos de cambio, asegúrese de suspender temporalmente el manejo de eventos con la línea Application.EnableEvents.

Articulos interesantes...