Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como CREAR VALIDACION de Celdas PERMITIR Solo INGRESO NUMERO Enteros con Macro Excel #537

Validacion en Excel, permitir solo numeros enteros con macros

CREAR LISTA DE VALIDACIÓN AL INSERTAR DATOS

En este ejemplo se muestra como crear una validación en celda al introducir un registro y permitir el ingreso solamente de números enteros, es decir que automáticamente cuando se ingrese un registro se generará una validación

Maneja Excel como los mejores, 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 la macro en acción, 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.

Validación en Celda Con Macros – Permitir ingreso de números enteros únicamente

En este ejemplo, al insertarse un dato en cualquier fila de la columna A, excepto la celda A1, que está reservada para la cabecera de la columna, se ejecuta una macro que agrega una validación por medio de macros en celda.

Mas precisamente inserta la validación en la celda de la columna F y target.row, es decir la celda que recibió la modificación, o dicho de otro punto de vista la celda donde estábamos posicionados al modificar la celda de la columna A.

La validación consiste, en que solo se pueden agregar números enteros ente 1 y 100, si se agrega un número distinto a los números de dicho rango o que no sean enteros, la macro emite un aviso no permitiendo modificar la celda, hasta que se introduzca un dato válido.

Explicación del código para Crear Validación Con Macros en Excel VBA

Primero se debe establecer si la modificación de la celda de Excel provino de cualquier celda de la columna A, en caso negativo, la macro no hace nada, en caso positivo, la macro ejecuta la macro contenida después del IF, el código es el siguiente:

If Target.Row > 1 And Target.Column = 1 Then

Luego se debe agregar los parámetros entre los cuales serán válidos los números enteros ingresados, ello se hace con el siguiente código:
valida1 = 1
valida2 = 100

Luego la macro inserta la validación en la celda de la Columna F y la Celda Correspondiente (que se acaba de modificar), procediendo primero a borrar cualquier validación, luego a agregar la validación utilizando como paramentos los determinados en las variables, valida1 y valida2; luego se agrega cual será el mensaje en caso de que se intente ingresar un dato inválidos,  ello se hace con el siguiente código;

With a.Cells(Target.Row, «F»).Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=valida1, Formula2:=valida2
.ErrorTitle = «Dato inválido»
.ErrorMessage = «Solo Puede ingresar números enteros entre 1 y 100»
End With

Código VBA para crear una validación en celda al insertar un dato

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row > 1 And Target.Column = 1 Then
Application.ScreenUpdating = False
Set a = Sheets(«Hoja1»)
valida1 = 1
valida2 = 100

With a.Cells(Target.Row, «F»).Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=valida1, Formula2:=valida2
.ErrorTitle = «Dato inválido»
.ErrorMessage = «Solo Puede ingresar números enteros entre 1 y 100»
End With
End If
Application.ScreenUpdating = True
End Sub



Descarga el Libro Como Crear Validación de Datos en Automáticamente con Macros de Excel

Descarga desde acá el archivo usado como ejemplo en este post y en el vídeo explicativo, el mismo es totalmente gratuito y libre su uso, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

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 una respuesta

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

Comentarios (46)

Hi there, I log on to your blogs daily. Your writing style is
witty, keep it up!

Responder

With havin so much written content do you ever run into any problems of plagorism or copyright violation? My
blog has a lot of unique content I’ve either authored myself or
outsourced but it appears a lot of it is popping it up all over the internet
without my agreement. Do you know any methods to help stop content from being ripped off?
I’d certainly appreciate it.

Responder

I’m not sure where you’re getting your information, but great topic.
I needs to spend some time learning more or understanding more.
Thanks for fantastic info I was looking for this information for my mission.

Responder

I was more than happy to uncover this page. I need to to thank you for ones time
due to this fantastic read!! I definitely enjoyed every little bit of it and I have you saved
as a favorite to see new information on your website.

Responder

Hi, I do believe this is an excellent website. I stumbledupon it 😉 I will come back yet again since
I saved as a favorite it. Money and freedom is the best way to
change, may you be rich and continue to guide others.

Responder

This is really attention-grabbing, You’re an excessively
skilled blogger. I have joined your rss feed and stay up for in search of extra of
your excellent post. Additionally, I’ve shared your web site in my social networks

Responder

Hello There. I found your blog using msn. This is
an extremely well written article. I’ll make sure to bookmark it and come back to read more of your useful information. Thanks for the post.
I’ll certainly return.

Responder

Good post! We will be linking to this particularly great
post on our website. Keep up the great writing.

Responder

Good info. Lucky me I came across your website by chance (stumbleupon).
I’ve bookmarked it for later!

Responder

Hmm is anyone else encountering problems with the images on this blog loading?
I’m trying to determine if its a problem on my end or if it’s the blog.
Any responses would be greatly appreciated.

Responder

Wow, this paragraph is good, my younger sister is analyzing
these things, so I am going to inform her.

Responder

It’s very straightforward to find out any matter on net as compared to books, as I found this article at this site.

Responder

Everything is very open with a very clear explanation of the challenges.
It was truly informative. Your site is very helpful. Many thanks for sharing!

Responder

Hi there, I enjoy reading through your post. I like to write a little comment to support
you.

Responder

This is my first time pay a quick visit at here and i am
in fact impressed to read all at one place.

Responder

These are truly fantastic ideas in concerning blogging. You have touched some fastidious points
here. Any way keep up wrinting.

Responder

I do accept as true with all the concepts you’ve presented in your post.
They’re very convincing and can certainly work. Nonetheless, the posts are too quick for
beginners. May you please prolong them a little from next
time? Thank you for the post.

Responder

[url=http://tadalafilxt.com/]tadalafil prescription us[/url]

Responder

[url=http://xviagrahot.com/]30 mg sildenafil chewable[/url] [url=http://sildenafilchem.com/]rx sildenafil tablets[/url] [url=http://cialissearch.com/]cialis without prescription canada[/url] [url=http://cialisconnect.com/]cialis medication cost[/url] [url=http://pharmacylewis.com/]canadian pharmacy sildenafil[/url] [url=http://ivermectinp.com/]ivermectin stromectol[/url] [url=http://eccpharm.com/]prednisone steroids[/url] [url=http://viagrahtab.com/]can you buy viagra over the counter in mexico[/url] [url=http://buyrxtablets.com/]buy clomid online usa[/url] [url=http://dbpills.com/]where to buy metformin 1000 mg[/url]

Responder

Howdy this is kinda of off topic but I was wanting to know if blogs use WYSIWYG
editors or if you have to manually code with HTML.
I’m starting a blog soon but have no coding experience so I wanted to get advice from someone
with experience. Any help would be greatly appreciated!

Responder

I enjoy reading an article that will make men and women think.
Also, many thanks for permitting me to comment!

Responder

Great article, just what I wanted to find.

Responder

Currently it appears like Drupal is the best blogging platform
out there right now. (from what I’ve read) Is
that what you’re using on your blog?

Responder

Wonderful goods from you, man. I’ve understand your stuff previous to and you are just too wonderful.
I really like what you have acquired here, certainly like what
you’re stating and the way in which you say it. You make it enjoyable and you
still care for to keep it sensible. I cant wait to read much more
from you. This is actually a terrific website.

Responder

Hello to all, it’s really a fastidious for me to pay a visit
this site, it includes precious Information.

Responder

At this time it seems like BlogEngine is the preferred blogging platform available right now.
(from what I’ve read) Is that what you are using on your blog?

Responder

I read this post fully concerning the difference of most up-to-date and previous technologies, it’s
remarkable article.

Responder

I like looking through an article that can make people think.

Also, thanks for permitting me to comment!

Responder

I’ve been surfing on-line greater than three hours nowadays,
but I never discovered any attention-grabbing article like yours.
It is pretty value enough for me. Personally, if all webmasters and bloggers made good
content as you did, the net will be a lot more useful than ever
before.

Responder

Interesting blog! Is your theme custom made or did you download it
from somewhere? A design like yours with a few simple tweeks would really make my blog
shine. Please let me know where you got your design. Thank you

Responder

Thanks for sharing your thoughts on equalizer mind
game. Regards

Responder

Howdy! I know this is kind of off topic but I was wondering which blog
platform are you using for this website? I’m getting sick and tired of
Wordpress because I’ve had problems with hackers
and I’m looking at alternatives for another
platform. I would be fantastic if you could point me in the direction of a good platform.

Responder

Hi there to all, how is everything, I think every one is getting more from this site, and your views are good in favor of new visitors.

Responder

This web site definitely has all the information I needed about this subject
and didn’t know who to ask.

Responder

Please let me know if you’re looking for a article writer
for your weblog. You have some really good 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
send me an e-mail if interested. Kudos!

Responder

[url=http://cialisdpill.com/]cialis 100[/url] [url=http://cialisconnect.com/]canadian drug pharmacy cialis[/url] [url=http://tadalafillowprice.com/]cialis from us pharmacy[/url] [url=http://tadalafilmtab.com/]generic tadalafil australia[/url] [url=http://viagraxbuy.com/]viagra mexico cost[/url]

Responder

Fantastic post however , I was wanting to know if you could write a litte more
on this subject? I’d be very grateful if you could elaborate a little bit further.
Bless you!

Responder

Thank you for sharing indeed great looking !

Responder

Its like you learn my mind! You seem to know a lot approximately this, such
as you wrote the book in it or something. I feel that you simply could do with some % to drive the
message home a bit, however other than that, that is excellent
blog. A fantastic read. I will definitely be back.

Responder

If some one wants to be updated with hottest technologies then he must be visit this web site and be up
to date everyday.

Responder

Hi, this weekend is fastidious in support of me, because this time i am reading this wonderful informative paragraph here at my home.

Responder

Have you ever thought about adding a little bit more than just your articles?
I mean, what you say is fundamental and everything. But just imagine if you added some great visuals
or videos to give your posts more, «pop»! Your
content is excellent but with pics and clips, this blog
could undeniably be one of the greatest in its
field. Fantastic blog!

Responder

Today, I went to the beachfront with my kids. I found a sea
shell and gave it to my 4 year old daughter and said «You can hear the ocean if you put this to your ear.» She put the shell to her ear and screamed.
There was a hermit crab inside and it pinched her ear. She never wants to go back!

LoL I know this is totally off topic but I had to tell someone!

Responder

Hi there, yup this article is actually fastidious and
I have learned lot of things from it regarding blogging.
thanks.

Responder

For the reason that the admin of this website is working, no
uncertainty very soon it will be famous, due to its quality contents.

Responder

I like what you guys tend to be up too. This type
of clever work and reporting! Keep up the very good works guys I’ve
included you guys to my blogroll.

Responder