Hojas de cálculo en Excel - página principal

Validar la entrada de datos en un formulario

Si nos ha dado por utilizar formularios en nuestras aplicaciones Excel, porque consideramos que son más elegantes, cuidadas, y porque le dan un toque más profesional, llegará un momento en el que deseemos controlar más todavía lo que un usuario introduce por ejemplo en un TextBox. Seguramente nos habremos preguntado ¿qué pasa si un usuario introduce letras en un TextBox que luego utilizo para hacer operaciones con los datos de una celda?. Pues es evidente que nos dará un error, pues estaremos operando con texto y números. Pensemos por ejemplo, cuál será el resultado de multiplicar un texto por una cifra, ...¿nos dará error, verdad?.

Pues bien, vamos a intentar que eso no suceda. Vamos a hacer que en un TextBox de nuestro formulario, solo se puedan introducir números. También explicaremos como hacer lo contrario, es decir, cómo permitir que el usuario solo escriba texto, y ya puestos, para que nadie lo pregunte :-) también validaremos la entrada de fechas en formato dd/mm/aaaa.

Para el primer caso, es decir, si solo vamos a permitir que el usuario introduzca números, vamos a suponer que tenemos un formulario tan sencillo como este que podéis ver en la imagen:


Para ver la utilidad del dato introducido, supondremos que vamos a hacer una operación con ese valor. Es decir, vamos a imaginar que si el usuario introduce una cifra cualquiera, la multiplicaremos por 6, y escribiremos ese valor en la celda B11. Algo como lo que muestra la siguiente imagen:


El código asociado al TextBox del UserForm, será este:


Private Sub TextBox1_Change()
'Solo permitiremos que se introduzcan números
'para lo cual, si el valor introducido es una letra
'lo omitiremos, y nos quedaremos con el resto de caracteres,
'es decir, con los números

If Not IsNumeric(Right(TextBox1, 1)) And Len(TextBox1) > 1 Then
TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
ElseIf Not IsNumeric(Right(TextBox1, 1)) And Len(TextBox1) = 1 Then
TextBox1 = Clear
End If
End Sub

Y el código asociado al botón "OK" que se puede ver en ese mismo userform, es este:


Private Sub CommandButton1_Click()
'Si hay errores, que continúe
On Error Resume Next
'Vamos a multiplicar el valor introducido por el usuario
'por una cifra cualquiera. En este caso, la multiplicaremos
'por 6, y escribiremos el dato resultante en la celda B11

Range("B11") = TextBox1 * 6
'liberamos el formulario de memoria
Unload UserForm1
End Sub

Como veis, es muy sencillo. No hace falta ni que lo comente más allá de lo que ya está comentado dentro del propio código.

Ahora vamos a imaginar que solo queremos que el usuario entre letras. En ese caso, ya no nos será útil multiplicar esas letras por 6, así que omitiremos hacer operaciones con esos datos introducidos, y simplemente los mostraremos en la celda. En el caso de este nuevo formulario, le hemos puesto nombres diferentes a los objetos, de tal forma que lo que antes era TextBox1, ahora es TextBox2, lo que antes era CommandButton1, ahora es CommandButton2, y así sucesivamente. En el caso que nos ocupa ahora, deberemos poner este código asociado al TextBox2:


Private Sub TextBox2_Change()
'Solo permitiremos que se introduzcan números
'para lo cual, si el valor introducido es una letra
'lo omitiremos, y nos quedaremos con el resto de caracteres,
'es decir, con los números

If IsNumeric(Right(TextBox2, 1)) Then TextBox2 = Left(TextBox2, Len(TextBox2) - 1)
End Sub

Y este otro en el botón "OK":


Private Sub CommandButton2_Click()
'Si hay errores, que continúe
On Error Resume Next
'Vamos a escribir el dato introducido, en la celda E11
Range("E11") = TextBox2
'liberamos el formulario de memoria
Unload UserForm2
End Sub

Para el caso de que queramos validar si el dato introducido es una fecha con formato dd/mm/aaaa, y suponiendo que nuestro TextBox se llama ahora TextBox3, en lugar de TextBox2, el botón se llama CommandButton3 en lugar de era CommandButton2, y así sucesivamente, tendremos que utilizar este código asociado al TextBox (al TextBox3, como hemos comentado):


Private Sub TextBox3_Change()
'si hay errores, que continúe
On Error Resume Next
'impedimos que se introduzca texto
If Not IsNumeric(Right(TextBox3, 1)) And Right(TextBox3, 1) <> "/" Then
TextBox3 = Left(TextBox3, Len(TextBox3) - 1)
End If
'contamos lo que mide de largo
largo_entrada = Len(TextBox3)
'evaluamos la posición de lo que vamos escribiendo
Select Case largo_entrada
'después del 2º carácter, que añada una barra
Case 2
'controlamos algunas manipulaciones de los números
If Right(TextBox3, 1) = "/" Then
TextBox3 = Left(TextBox3, Len(TextBox3) - 1)
Else
TextBox3 = TextBox3 & "/"
End If
'después del 5º carácter, que añada una barra
Case 5
'controlamos algunas manipulaciones de los números
If Right(TextBox3, 1) = "/" Then
TextBox3 = Left(TextBox3, Len(TextBox3) - 1)
Else
TextBox3 = TextBox3 & "/"
End If
End Select
'si el largo ocupa más de 10 caracteres,
'solo nos quedamos con los primeros 10 caracteres

If Len(TextBox3) > 10 Then TextBox3 = Left(TextBox3, 10)
'Deshabilitamos el botón "OK", si la fecha tiene menos de 10 caracteres
If Len(TextBox3) < 10 Then
CommandButton3.Enabled = False
Else
CommandButton3.Enabled = True
End If
'evitamos que haya más de 2 barras en el TextBox
If Len(TextBox3) = 10 And Len(Replace(TextBox3, "/", "")) < 8 Then
TextBox3 = Clear
End If
End Sub

Y este otro en el botón "OK":


Private Sub CommandButton3_Click()
'Si hay errores, que continúe
On Error Resume Next
'Vamos a escribir el dato introducido, en la celda H11
Range("H11") = TextBox3
'liberamos el formulario de memoria
Unload UserForm3
End Sub

Solo queda por solucionar un tema, y es validar que la fecha sea correcta, no solo en formato, que eso ya lo hemos visto en este artículo, sino en su validez o consistencia, para que por ejemplo nadie introduzca una fecha como esta: 44/55/2222, pues los días como máximo son 31, y los meses 12. Pero eso, casi es mejor que lo validéis después de darle al botón OK, en lugar de impedir la entrada de fechas de ese tipo. Os lo dejo que lo resolváis vosotros, pues no parece muy complicado a simple vista, ya que el alcance de este artículo es validar que se entran datos de acuerdo a un criterio: números, textos, o fechas en formato dd/mm/aaaa, sin evaluar si esta última es correcta.

He resumido todo esto que he explicado, en una hoja de cálculo que tiene este aspecto, para el que esté interesado en verlo con ejemplos prácticos, pues siempre ayudan mucho más:


Desde aquí podéis descargar el fichero, con los ejemplos que hemos visto en este artículo.



20 comentarios:

GSibaja dijo...

Gracias por el truco.

Pero si cometo un error en el momento de digitar el numero o la fecha no me deja usar la tecla backspace para poder corregir.

en el caso de los numeros sale un error en el codigo y en el caso de las fechas me llena los 10 caracteres con barras (//////////).

esto en el momento que utilizo el backspace para corregir el error.

Como se puede solucionar esto.

Gracias

Javier Marco dijo...

Hola GSibaja...

Tienes razón, había algún error a la hora de validar la entrada de datos. Por ejemplo, en el primer caso que planteas, el del userform exclusivo para números, si el primer caracter introducido era un texto, daba error. Si era del 2º caracter en adelante, entonces no lo daba.

En el caso de las fechas, he implementado algunos controles adicionales, para solucionar el tema de la barra.

He estado probándolo con todas las variables que se me han ocurrido, y creo que ya funciona todo correctamente.

Un saludo, y garcias por el aviso.

GSibaja dijo...

Gracias a ti por compartir tus conocimientos. muy buenos consejos para los que estamos aprendiendo a programar en VB.

Estoy tratando de entender tu codigo. Tengo una duda: cuando digito la fecha y coloco los 2 primeros caracteres(por ejemplo 14/) y quiero borrar (backspace) no me lo permite, tengo que usar la tecla <- para poder pasar la barra (/) y asi borrar el 14.
como se puede hacer para poder borrar los / con la tecla backspace. no se si me esplico?

Perdona por ser tan insistente, lo que pasa es que estoy trabajando en un formulario que utilizan terceras personas y quiero hacerlo "a prueba de tontos" (como dicen algunos programadores).

Saludos desde Costa Rica.

Javier Marco dijo...

Hola GSibaja.

Tus preguntas son muy interesantes. Verás, si pones una fecha en el userform que pretende controlar el formato dd/mm/aaaa, pasa una cosa:

Cuando introduces los 2 primeros números, el "programita" automáticamente le pone la barra tras ese segundo número. ¿Por qué?. Pues porque en el código le estamos diciendo que si la cifra introducida tiene dos caracteres (mírate el select, y el case 2), que añada detrás la barra.

Claro, si luego te das cuenta que te has equivocado, y en lugar de poner 14, quieres poner 13, al intentar borrar la barra no te deja de ninguna de las formas, ni con la tecla de backspace, ni seleccionándola con el ratón. ¿Por qué?. Pues porque al intentar borrarla, como hay 2 caracteres antes, vuelve a generarse la barra. Es como un bucle infinito, en el que quieres borrar la barra, pero como hay 2 caracteres delante, y en el código VBA del userform pone que cuando haya 2 caracteres que añada la barra, ...pues siempre volvemos a las mismas. Quizás lo veas más claro en lugar de usando backspace, usando las flechasy seleccionando la barra. Intenta borrarla, y verás como automáticamente te aparece de nuevo.

Solución: no la hay, pues si borrases la barra podrías escribir un número y ya no tendría formato dd/mm/aaaa. Como no te permite poner 3 números seguidos (excepto con el año), pues siempre tendrás la barra.

Si deseas borrar la fecha en caso de error, puedes crearte un botón al lado del otro llamado "OK", que lo que haga sea borrar el contenido con este sencillo código:

TextBox3 = Clear

Saludos.

PD: Estaré missing, de vacaciones hasta el 1 de septiembre, así que no podré contestar ninguna duda hasta esa fecha.

GSibaja dijo...

Muy interesante tu explicacion, gracias por tomarte el tiempo para hacerlo.

Felices vacaciones.

saludos

Ivan dijo...

oye tengo una consulta, jejeje, ya se que no deberia, como le hiciste para ser tan bueno en esto de excel, felicidades en serio, muchas gracias por darte el tiempo de escribir, la verdad eres muy buena onda

Anónimo dijo...

HOla.. SOY NUEVO EN ESTO..conozco muy poco de funciones..
Quisiera saber como validar para que la fecha sea correcta, para que por ejemplo no se pueda introducir una fecha que no es real.. como por ej.. 98/13/5822, porque los días son como máximo son 31, y los meses 12. Q codigo tengo q introducir y adonde.. si podes decirme mejor.. por que no entiendo mucho de esto.. MUCHAS GRACIAS!!

Javier Marco dijo...

Este sería el código del botón sobre el que al hacer clic, evaluaremos si en el TexBox1 hay una fecha u otra cosa. Creo que con esto ya tienes todo el tema solucionado:

Private Sub CommandButton1_Click()
'Si hay errores, que continúe
On Error Resume Next
'Si la fecha nos da error...
If IsError(CDate(TextBox1)) Then
'mostramos un mensaje en la celda A1
Range("A1") = "Error"
Else
'en caso contrario, ponemos la fecha del TextBox1
Range("a1") = TextBox1
End If
End Sub

Saludos.

Anónimo dijo...

gracias..pero no es precisamente lo q necesito.. necesito que en el formulario vos puedas elegir de una determinada fecha a otra fecha.. nada mas.. y q t lo valide sin ningun boton.. Gracias nuevamente.. sepa disculpar mis molestias..

Anónimo dijo...

Muy bueno el blog, una pregunta, puede ser que en el que valida numeros solo acepte numeros sin decimales??

Javier Marco dijo...

Muy buena la observación. Como esperaba que alguien se diera cuenta, también tenía esa otra opción implementada. Aquí la tienes:

Private Sub TextBox1_Change()
'Solo permitiremos que se introduzcan números
'para lo cual, si el valor introducido es una letra
'lo omitiremos, y nos quedaremos con el resto de caracteres,
'es decir, con los números
If Not IsNumeric(Right(TextBox1, 1)) And Len(TextBox1) > 1 Then
'controlamos que se permita poner decimales
If Right(TextBox1, 1) = "." Or Right(TextBox1, 1) = "," Then
'controlamos que no haya más de 1 signo de puntuación
If InStr(TextBox1, ".") >= 1 And InStr(TextBox1, ",") >= 1 Then TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
If Len(TextBox1) - Len(Replace(TextBox1, ".", "")) >= 2 Then TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
If Len(TextBox1) - Len(Replace(TextBox1, ",", "")) >= 2 Then TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
Else
TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
End If
ElseIf Not IsNumeric(Right(TextBox1, 1)) And Len(TextBox1) = 1 Then
TextBox1 = Clear
End If
End Sub

Si te fijas bien, permite el uso de la coma o el punto, dependiendo de la que utilices (en España se usa como separador de decimales la coma, pero en Iberoamérica se usa el punto). En cualquier caso, una vez introducido uno de los caracteres (la coma o el punto), no se puede añadir el otro.

Espero que sea la solución que buscabas (y que ya tenía hecha, ....solo esperaba que alguien lo preguntara).

Saludos.

PD: Si usas un separador decimal que no es el apropiado (por ejemplo, el punto como separador decimal en España), cuando haces la multiplicación del ejemplo (se multiplica por 6 el valor intrducido), el resultado obtenido omite el punto. Ejemplo:

Si introduces 10.5, la apliación del ejemplo multiplicaría 10.5 x 6, pero como el separador decimal en España no es el punto, lo omitiría, y se calcularía 105 x 6

Anónimo dijo...

Hola, he creado una macro en Word 2010 para definir un encabezado y un pie de página, y al ejecutar(correr) la macro, coloca ambos únicamente en el encabezado, alguna persona experta en este tema? sera que ya existe algun parche para solucionar este problema. Muchas gracias por su aporte.

Anónimo dijo...

Amigo felicidades por tu blog esta es mi duda

Nesesito que me explices para que sirve este codigo pero detalladamente plizzzz

If Len(TextBox3) = 10 And Len(Replace(TextBox3, "/", "")) < 8 Then
TextBox3 = Clear
End If

Javier Marco dijo...

Pues significa que si el largo (len) del textbox (la cajita para introducir la fecha) es igual a 10 (es decir, tiene 10 caracteres), y además el largo del textbox una vez reemplazadas las barras por nada (de "/" pasamos a ""), es menor que 8, entonces limpiamos el textbox (eliminamos lo que se haya introducido).

Esta es una medida de control, pues el textbox permite introducir números y el signo de la barra (esta la pone automáticamente). Simplemente lo que hace ese condicional es controlar que efectivamente el largo del textbox es de 10 caracteres, y que de esos 10, 8 son números (los otros son barras, pues el formato es dd/mm/aaaa).

Saludos.

Anónimo dijo...

Excelente blog, es de mucha ayuda.
Una consulta, al validar solo número como se puede determinar que solo se introduzca dos decimales? y puede ser que al estar en el textbox automaticamente al introducir un numero aparezca con dos decimales ejemplo 1.00 que se por defecto dos decimales .00 digamos si yo pongo un numero entero 55 y no pongo decimales lo rellente 55.00 asi como en las celdas de excel que le damos el formato contable y tomo los dos decimales por defecto. gracias por tu tiempo

Javier Marco dijo...

Bueno, lo que planteas, no me parece factible, por una cuestión. Si yo escribo en el textbox1 por ejemplo 5, tu quieres que me lo cambie por 5.00, pero claro, ¿qué pasa si yo quiero escribir un número que no sea ese, que sea por ejemplo 54?. Como me habrá puesto el separador decimal, y los dos ceros, tendré que moverme con el cursor detrás del 5, para poner el 4.

Creo que lo mejor es hacer eso que pides, cuando se cambia de textbox, o se pasa a otro control (combobox, etc). Para ello, échale un vistazo al código fuente de este artículo, donde explico como calcular préstamos bancarios. Si te descargas la aplicación, verás que por ejemplo, en el primer textbox, puedes introducir cualquier número con o sin decimales, y que al pasar al siguiente textobox, es cuando al primero le da el formato deseado.

No obstante, al hilo de tu comentario, he adaptado el código de este artículo para validar la entrada de datos en un formulario, y he incluido una limitación cuando se entran números, para que en el caso de querer poner 3 o más decimales, no te deje. Es decir, como máximo podremos introducir 2 decimales. Este sería el código fuente:

Private Sub TextBox1_Change()
'Solo permitiremos que se introduzcan números
'para lo cual, si el valor introducido es una letra
'lo omitiremos, y nos quedaremos con el resto de caracteres,
'es decir, con los números
If Not IsNumeric(Right(TextBox1, 1)) And Len(TextBox1) > 1 Then
'controlamos que se permita poner decimales
If Right(TextBox1, 1) = "." Or Right(TextBox1, 1) = "," Then
'controlamos que no haya más de 1 signo de puntuación
If InStr(TextBox1, ".") >= 1 And InStr(TextBox1, ",") >= 1 Then TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
If Len(TextBox1) - Len(Replace(TextBox1, ".", "")) >= 2 Then TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
If Len(TextBox1) - Len(Replace(TextBox1, ",", "")) >= 2 Then TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
Else
TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
End If
ElseIf Not IsNumeric(Right(TextBox1, 1)) And Len(TextBox1) = 1 Then
TextBox1 = Clear
End If
'controlamos que solo haya dos decimales
If (InStr(TextBox1, ".")) > 0 And Len(Mid(TextBox1, InStr(TextBox1, ".") + 1, Len(TextBox1))) > 2 Then TextBox1 = Left(TextBox1, InStr(TextBox1, ".") + 2)
If (InStr(TextBox1, ",")) > 0 And Len(Mid(TextBox1, InStr(TextBox1, ",") + 1, Len(TextBox1))) > 2 Then TextBox1 = Left(TextBox1, InStr(TextBox1, ",") + 2)
End Sub

Anónimo dijo...

Gracias por la respuesta eres un capo total te mando un saludo desde Sucre-Bolivia-SudAmerica

fofo dijo...

he eswtado haciendo un formulario con calendario, la cuestion es que quiero que el calendario solo muestre o solo permita que se pueda seleccionar la fecha de hoy hcia atras es decir si hoy es 10/12/2000 a partir de esa fecha hacia a;os atras se puedan seleccionar y no las futuras

Javier Marco dijo...

Échale un vistazo a estos artículos:
Calendario emergente en Excel.
Incluir un calendario en un formulario.

Saludos.

h cr dijo...

¿como puedo usar ese codigo para que quede en formato dd de mmmm de yyyy?