Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como VALIDAR que en TEXTBOX se INGRESE una FECHA Excel VBA #514

Validar Textbox ingreso fechas, ingreso numeros, barra fechas automaticas

Como Permitir que solo se ingresen fechas en Textbox

En esta oportunidad les voy a presentar una macro que deja que solo se inserten fecha en el textbox, como así también agrega automáticamente las barras de fecha y permite que solo se ingrese número, no texto o string en textbox.

Necesitas apender a manejar Excel, click acá, si quieres aprender sobre Excel en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet, te lo recomiendo no te arrepentirás.

  
 

Mira el funcionamiento de la macro y una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestro canal de You Tube, mira el playlist con  vídeos relacionados donde podrás ver la macros relacionadas en acción con una explicación en forma visual que ayudará a entender el ejemplo en forma más fácil.

Macro para Validar Textbox y permitir ingreso de fechas solamente

Se debe descargar el ejemplo para que sea más fácil entender el código, una vez hecho y al abrir el libro se debe presionar el botón que muestra un formulario, en dicho formulario hay un textbox que solo permite ingresar números, valida que dato ingresado sea una fecha y a su vez inserta en forma automática las barras de la fecha, es decir que para ingresar un dato tipo fecha solo se debe presionar el día el mes y el año despreocupándose por insertar las barras de fecha porque la macro lo hace automáticamente. 

Si se ingresa un carácter distinto a un número, excepto la barra inclinada usada para la fecha, la macro automáticamente borra el carácter impidiendo que se ingrese texto en el textbox o cualquier otro carácter distinto a un número, para ello tiene en cuenta el código Ascci.

Explicación del código que permite el ingreso de números y fechas únicamente en Textbox, además de ingresar barras de fechas 

Para validar el ingreso de números en textbox y barras de fechas en forma automatizada, se debe agregar el código en el evento «Change» del Textbox, es decir que a medida que se valla ingresando algún carácter en el Textbox se ejecutará la macro.

Para permitir el ingreso solamente de números en un Textbox de Excel VBA, se debe hacer un bucle For… Next, recorriendo cada carácter ingresado en el Textbox, en primer lugar se determinar el largo del texto ingresado hasta el momento haciendo un bucle entre el primer y último carácter para determinar el largo se usa el siguiente código:

Lar = Len(Me.TextBox1.Value)

Luego se realiza el bucle determinando si el carácter esta incluido entre el código 48 y 57, que en el código Ascii representan a los números del 0 al 9, se usa el siguiente código:

 For i = 1 To Lar
Car = Mid(Tex, i, 1)
If Car <> «» Then
If Car < Chr(48) Or Car > Chr(57) Then

En caso que detecte que el carácter ingresado no es un número sino el código que representa la barra inclinada que se una para ingresar una fecha, la macro no hace nada y sigue con el carácter siguiente, esto se da porque la macro también acepta que se pueda ingresar la barra de fecha manualmente, se usa el código:

If Car = Chr(47) Then GoTo ir:

En caso que el carácter sea distinto a un número, excepto una barra inclinada usada para ingreso de una fecha el carácter es reemplazado por un carácter vacío, nada, o eliminado, con el siguiente código:

TextBox1.Value = Replace(Tex, Car, «»)

Para el ingreso de barras de fechas se utiliza un Select Case, es decir detecta que si ingresaron 2 o 5 caracteres, agregando automáticamente la barra inclinada en el Textbox, la explicación es porque 2 y 5  es la posición de la barra inclinada normalmente cuando se ingresa una fecha en formato «dd/mm/yyyy», 

Select Case Lar
Case 2
Me.TextBox1.Value = Me.TextBox1.Value & «/»
Case 5
Me.TextBox1.Value = Me.TextBox1.Value & «/»
End Select

Algo que no se explicó aún, pero que aparece el código al principio de la macro es que el Textbox se limita al ingreso de 10 caracteres, no permitiendo ingresar mas, ello tiene explicación en la sumatoria de caracteres de la fecha que son: 2 para el día, una barra, 2 para el mes, una barra y cuatro para el año, en total suman 10 caracteres, el código es el siguiente:

TextBox1.MaxLength = 10

En el evento «Exit» del Textbox se encuentra la macro que valida que se haya ingresado una fecha, en caso que lo ingresado en el textbox no sea una fecha sale un msgbox solicitando que se ingrese una fecha valida, el código que se usa para determinar si el dato ingresado en el Textbox es una fecha es el siguiente:

If IsDate(TextBox1) = False Then
MsgBox («Debe ingresar una fecha válida»), vbCritical, «AVISO»

En caso que lo ingresado sea una fecha esta es formateada del modo «dd/mm/yyyy» con el siguiente código:


TextBox1 = Format(CDate(TextBox1), «dd/mm/yyyy»)

 

Descarga del archivo ejemplo como permitir que se inserten fechas en Textbox de Excel

Descarga el Libro de Excel con el código completo del Ejemplo validar textbox en excel con fechas el link se encuentra al final del post, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

Código del ejemplo Como Validar Textbox y Permitir Ingreso de Números y Fechas Solamente y agregar automáticamente barras de fecha

Código que se ingresa en Userform1 

Private Sub CommandButton1_Click()
TextBox1 = Clear
TextBox1.SetFocus
End Sub

Private Sub TextBox1_Change()
‘Valida para que solo se ingrese número
Dim Tex As Variant, Car As Variant, Lar As Integer
On Error Resume Next
TextBox1.MaxLength = 10
Tex = Me.TextBox1.Value
TextBox1.Value = Replace(Tex, «//», «/»)
Lar = Len(Me.TextBox1.Value)
For i = 1 To Lar
Car = Mid(Tex, i, 1)
If Car <> «» Then
If Car < Chr(48) Or Car > Chr(57) Then
If Car = Chr(47) Then GoTo ir:
TextBox1.Value = Replace(Tex, Car, «»)
End If
End If
ir:
Next i

Select Case Lar
Case 2
Me.TextBox1.Value = Me.TextBox1.Value & «/»
Case 5
Me.TextBox1.Value = Me.TextBox1.Value & «/»
End Select

End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1 <> Empty Then
If IsDate(TextBox1) = False Then
MsgBox («Debe ingresar una fecha válida»), vbCritical, «AVISO»
Cancel = True
TextBox1 = Empty
Else
TextBox1 = Format(CDate(TextBox1), «dd/mm/yyyy»)
End If
End If
End Sub

 

Código que se ingresa en Modulo1

Sub muestra()
UserForm1.Show
End Sub



Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Donate:
Cuenta Paypal: https://paypal.me/programarexcel
Cuenta Bitcoin: 1KBGGb8fyDzyR3X1Rie6m7VguzaAfngNbd
Cuenta Ether: 0x41Bbd24556914C83a31217eBb3BC49789b66e407

Summary
Author Rating
1star1star1star1star1star
Aggregate Rating
no rating based on 0 votes

Entradas relacionadas

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (33)

I am the co-founder of JustCBD company (justcbdstore.com) and am trying to broaden my wholesale side of company. I am hoping anybody at targetdomain can help me 🙂 I thought that the very best way to do this would be to reach out to vape companies and cbd retail stores. I was hoping if someone could suggest a reliable website where I can buy Vape Shop Database Leads I am already taking a look at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Unsure which one would be the most ideal choice and would appreciate any support on this. Or would it be simpler for me to scrape my own leads? Ideas?

Responder

тонкость процедуры субсидиарная ответственность или смежная процедура закрыть ооо с долгами

Responder

The very next time I read a blog, I hope that it does not fail me just as much as this one. After all, I know it was my choice to read through, but I actually believed you would have something helpful to say. All I hear is a bunch of moaning about something you could fix if you weren’t too busy looking for attention.

Responder

Oh my goodness! Amazing article dude! Many thanks, However I am experiencing problems with your RSS. I don’t know the reason why I can’t join it. Is there anyone else getting the same RSS issues? Anyone who knows the solution will you kindly respond? Thanx!!

Responder

Very good post. I am dealing with many of these issues as well..

Responder

This is the right website for anyone who wants to understand this topic. You understand a whole lot its almost hard to argue with you (not that I personally would want to…HaHa). You definitely put a brand new spin on a topic which has been discussed for many years. Wonderful stuff, just wonderful!

Responder

bookmarked!!, I really like your web site!

Responder

This site was… how do I say it? Relevant!! Finally I’ve found something which helped me. Many thanks!

Responder

Good blog you’ve got here.. It’s difficult to find excellent writing like yours these days. I truly appreciate individuals like you! Take care!!

Responder

I was able to find good advice from your blog posts.

Responder

This is a topic that is close to my heart… Take care! Exactly where are your contact details though?

Responder

May I simply say what a relief to discover somebody who truly knows what they’re discussing over the internet. You definitely realize how to bring an issue to light and make it important. More and more people ought to check this out and understand this side of the story. I was surprised that you aren’t more popular because you certainly have the gift.

Responder

Greetings! Very useful advice within this article! It’s the little changes that produce the most important changes. Thanks a lot for sharing!

Responder

There’s definately a lot to know about this subject. I really like all the points you made.

Responder

I want to to thank you for this fantastic read!! I definitely enjoyed every bit of it. I’ve got you book-marked to check out new things you post…

Responder

I must thank you for the efforts you have put in penning this site. I really hope to check out the same high-grade content by you in the future as well. In fact, your creative writing abilities has inspired me to get my own, personal website now 😉

Responder

Excellent write-up. I absolutely love this website. Stick with it!

Responder

Excellent article. I’m experiencing many of these issues as well..

Responder

Hello! I just wish to offer you a big thumbs up for the great info you have got right here on this post. I will be coming back to your web site for more soon.

Responder

Everything is very open with a clear description of the challenges. It was really informative. Your site is useful. Thank you for sharing!

Responder

Having read this I thought it was very enlightening. I appreciate you finding the time and energy to put this article together. I once again find myself spending a significant amount of time both reading and commenting. But so what, it was still worth it!

Responder

The very next time I read a blog, Hopefully it does not fail me as much as this one. After all, Yes, it was my choice to read through, however I genuinely thought you would have something helpful to talk about. All I hear is a bunch of whining about something that you can fix if you weren’t too busy looking for attention.

Responder

Hello there! I just wish to offer you a huge thumbs up for the excellent information you have right here on this post. I am coming back to your site for more soon.

Responder

Right here is the perfect website for everyone who really wants to understand this topic. You know so much its almost hard to argue with you (not that I actually would want to…HaHa). You definitely put a new spin on a topic that’s been discussed for a long time. Wonderful stuff, just wonderful!

Responder

Oh my goodness! Incredible article dude! Many thanks, However I am experiencing issues with your RSS. I don’t understand the reason why I can’t join it. Is there anybody else having similar RSS problems? Anyone who knows the answer can you kindly respond? Thanks!!

Responder

Can I just say what a relief to discover a person that really knows what they’re discussing over the internet. You actually understand how to bring an issue to light and make it important. More and more people need to read this and understand this side of your story. I was surprised that you’re not more popular given that you most certainly have the gift.

Responder

You’re so awesome! I don’t believe I have read through something like this before. So great to find somebody with some original thoughts on this issue. Seriously.. thanks for starting this up. This site is one thing that’s needed on the internet, someone with some originality!

Responder

You are so awesome! I don’t suppose I’ve truly read something like this before. So great to discover someone with a few genuine thoughts on this topic. Seriously.. thank you for starting this up. This web site is something that is required on the web, someone with a bit of originality!

Responder

Please let me know if you’re looking for a writer for your site.
You have some really great articles and I feel I would be a
good asset. If you ever want to take some of the load off, I’d absolutely love
to write some articles for your blog in exchange for a link
back to mine. Please shoot me an email if interested. Many thanks!

Responder

Hi there! I could have sworn I’ve been to your blog before but after browsing through many of the articles I realized it’s new to me. Anyways, I’m certainly happy I found it and I’ll be book-marking it and checking back regularly!

Responder

Hi there to every body, it’s my first pay a visit of this website; this weblog consists of amazing and really fine stuff in support
of visitors.

Responder

Hi, i think that i saw you visited my website so
i got here to return the want?.I am trying to in finding
things to enhance my website!I guess its good enough to use some of your ideas!!

Responder

I must thank you for the efforts you have put in writing this site. I’m hoping to check out the same high-grade content by you in the future as well. In truth, your creative writing abilities has motivated me to get my own blog now 😉

Responder