Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como CREAR LISTA de VALIDACION Datos en Otra Hoja Con MACRO Excel VBA #532

Crear Validacion en Excel con Macro Listas Validacion otra hoja

Crear Validación de Datos en Excel con Macro

En este post se muestra como crear una validación de datos en Excel con macro, los datos para crear la lista de validación se encuentra en otra hoja distinta a la cual estamos trabajando.

Aprende excel en forma fácil y sencilla, 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.

Crear Lista de Validación con Excel

En este ejemplo al seleccionar en la celda «J2» un item se crea automáticamente en la celda «J3» una lista de validación con macro, la cual permite el ingreso de datos, solamente de los que coincidan con la lista de validación generada por la macro, o bien se debe seleccionar desde el listado de validación que se genera en la celda, seleccionado el dato se procede a filtrar y mostrar los datos en la hoja de Excel.

Explicación del código para crear Listado para Validación de Datos en Excel VBA

La macro detecta si se modifica la celda de la columna 10 es decir «J1», se utiliza el siguiente código

 Target.Row = 1 And Target.Column = 10 Then

Luego se crea objetos con las hoja donde se encuentran los datos y la hoja donde se creará el listado de validación, así:

Set a = Sheets(«Hoja1»)
Set b = Sheets(«Hoja3»)

Se determina la última celda con datos de la hoja 3 que es donde se encuentra el listado de datos para utilizarlos como validación

uf = b.Range(«B» & Rows.Count).End(xlUp).Row

Por último se agrega la validación en la celda «J2» de Excel VBA, se usa el siguiente código:

With a.Range(«J2»).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=»=Hoja3!$B$2:$B$» & uf
.IgnoreBlank = True
.InCellDropdown = True
End With

Descarga el Libro de Excel utilizado en el Ejemplo Como Crear Validación con Macro

La descarga del archivo de Excel usado como ejemplo puedes hacerlos desde el final del post, 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.

Código para Crear Validación de Datos con Macro de Excel – VBA

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 10 Then
Application.ScreenUpdating = False
Set a = Sheets(«Hoja1»)
Set b = Sheets(«Hoja3»)
uf = b.Range(«B» & Rows.Count).End(xlUp).Row
With a.Range(«J2»).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=»=Hoja3!$B$2:$B$» & uf
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

If Target.Row = 2 And Target.Column = 10 Then Call Filtrar
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 una respuesta

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

Comentarios (64)

Hello mates, pleasant piece of writing and good urging commented here, I am actually enjoying by these.

Responder

I’m extremely inspired with your writing talents and
also with the layout in your weblog. Is this a paid theme or did you
modify it your self? Anyway keep up the excellent high quality writing,
it is rare to look a nice weblog like this one nowadays..

Responder

Write more, thats all I have to say. Literally, it seems as though you relied on the
video to make your point. You definitely know what youre talking about, why waste
your intelligence on just posting videos to your
blog when you could be giving us something enlightening to read?

Responder

Thank you for sharing your info. I really
appreciate your efforts and I am waiting for your next
write ups thanks once again.

Responder

This is a topic which is near to my heart…
Best wishes! Exactly where are your contact details though?

Responder

My spouse and I stumbled over here different website
and thought I should check things out. I like what I see so i am just following you.
Look forward to going over your web page yet again.

Responder

Do you have a spam problem on this website; I also
am a blogger, and I was wanting to know your situation; many of us
have developed some nice methods and we are looking to exchange methods with
others, please shoot me an e-mail if interested.

Responder

Hi, Neat post. There is an issue along with your site in internet
explorer, would check this? IE nonetheless is the marketplace chief and
a big section of other people will pass over your excellent
writing due to this problem.

Responder

[url=https://sirilending.com/]people with bad credit[/url]

Responder

Magnificent website. A lot of useful info here.
I’m sending it to a few buddies ans additionally sharing in delicious.
And certainly, thanks on your effort!

Responder

I really like it when people get together and share ideas.
Great website, continue the good work!

Responder

It is in point of fact a great and helpful piece of information. I’m happy that
you simply shared this helpful info with us. Please keep us informed like this.
Thank you for sharing.

Responder

If you are going for most excellent contents like I do, just visit this web site every day
for the reason that it presents feature contents, thanks

Responder

Hey! This post could not be written any better! Reading this post reminds me of my previous room mate! He always kept talking about this. I will forward this article to him. Pretty sure he will have a good read. Thank you for sharing!|

Responder

Wow, that’s what I was exploring for, what a stuff!
present here at this website, thanks admin of this website.

Responder

[url=https://nolvadexpill.com/]buy tamoxifen 20mg[/url] [url=https://medicinecialis.com/]where to purchase cialis online[/url] [url=https://cialisexpress.com/]tadalafil online usa[/url] [url=https://wbapharmacy.com/]secure medical online pharmacy[/url] [url=https://phdtabs.com/]generic propecia 5mg[/url] [url=https://wplasix.com/]lasix mastercard[/url] [url=https://viagrabuycheap.com/]generic viagra usa[/url] [url=https://anviagra.com/]buy viagra online prescription[/url] [url=https://viagraiix.com/]viagra tablet 25 mg price[/url] [url=https://pviagra.com/]viagra for female in india price[/url]

Responder

Hi! Do you use Twitter? I’d like to follow
you if that would be ok. I’m absolutely enjoying your blog
and look forward to new posts.

Responder

Hey there, You have done a great job. I will definitely
digg it and personally recommend to my friends. I am sure they’ll be benefited from this website.

Responder

[url=https://viagrahtab.com/]viagra pills pharmacy[/url] [url=https://buyrxtablets.com/]clomid tablets price uk[/url] [url=https://tadalafillowprice.com/]tadalafil 2.5 mg tablets india[/url] [url=https://viagraxbuy.com/]how much is a viagra prescription[/url]

Responder

I read this piece of writing fully on the topic of the comparison of newest and earlier technologies, it’s awesome article.

Responder

[url=https://aventolin.com/]combivent price[/url] [url=https://medptr.com/]levitra us[/url] [url=https://tadalafilbestbuy.com/]tadalafil 5mg prescription[/url] [url=https://sildenafilchem.com/]viagra sildenafil[/url] [url=https://azcialis.com/]cialis online from canada[/url]

Responder

[url=https://wplasix.com/]furosemide 120 mg[/url] [url=https://oxlpharm.com/]quineprox 60 mg[/url] [url=https://buyrxtablets.com/]where to buy clomid online[/url] [url=https://sildenafilvtab.com/]generic viagra capsules[/url]

Responder

I don’t even understand how I stopped up right here, but I thought this put up was
good. I don’t know who you’re however certainly
you are going to a well-known blogger when you aren’t already.

Cheers!

Responder

Great article. I am facing many of these issues as well..

Responder

[url=http://azcialis.com/]cialis 80mg online[/url] [url=http://fasttadalafil.com/]how much is generic tadalafil[/url] [url=http://anycialis.com/]us pharmacy cialis[/url]

Responder

I’m gone to convey my little brother, that he should also visit this webpage on regular basis to take updated from newest news update.

Responder

Your style is unique compared to other people I’ve read stuff from.
Many thanks for posting when you have the opportunity, Guess I
will just book mark this blog.

Responder

Thankfulness to my father who told me concerning this weblog,
this website is in fact awesome.

Responder

I’m really enjoying the design and layout of your website.
It’s a very easy on the eyes which makes it much more pleasant for me
to come here and visit more often. Did you hire out a developer to create your theme?
Excellent work!

Responder

Hi there! This blog post couldn’t be written much better!

Looking through this article reminds me of my previous roommate!
He always kept preaching about this. I will forward
this article to him. Pretty sure he’ll have a great read.
Thanks for sharing!

Responder

Hey I know this is off topic but I was wondering if you
knew of any widgets I could add to my blog that automatically tweet my newest twitter updates.
I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have
some experience with something like this. Please let me know if
you run into anything. I truly enjoy reading your blog and I look forward to your new updates.

Responder

Write more, thats all I have to say. Literally, it seems as though
you relied on the video to make your point. You clearly know what youre talking about, why throw away your intelligence on just posting videos to your
site when you could be giving us something enlightening to
read?

Responder

Hello there! This post could not be written much better!
Looking through this post reminds me of my previous roommate!
He always kept preaching about this. I am going to forward this post to him.
Fairly certain he’s going to have a very good read.
Many thanks for sharing!

Responder

[url=http://medptr.com/]levitra 40[/url] [url=http://sildenafilpharm.com/]order viagra from india[/url] [url=http://cialisfmed.com/]generic cialis[/url] [url=http://crmeds.com/]prozac 200 mg[/url] [url=http://tadalafilxt.com/]cheap tadalafil tablets[/url]

Responder

Do you have any video of that? I’d love to find out some
additional information.

Responder

You could certainly see your expertise within the article you write.
The arena hopes for more passionate writers like
you who aren’t afraid to say how they believe.
Always go after your heart.

Responder

[url=https://viagrafmed.com/]how to buy viagra online in usa[/url] [url=https://anviagra.com/]viagra price in india[/url] [url=https://pviagra.com/]how to obtain viagra[/url] [url=https://tadalafilmtab.com/]best tadalafil[/url] [url=https://tadalafilxt.com/]buy tadalafil usa[/url] [url=https://phdtabs.com/]propecia how to get[/url]

Responder

Greetings I am so delighted I found your weblog, I really
found you by accident, while I was looking
on Google for something else, Anyhow I am here now and
would just like to say cheers for a incredible post and a all round thrilling blog
(I also love the theme/design), I don’t have time to
browse it all at the minute but I have bookmarked it and also added your RSS feeds, so
when I have time I will be back to read a lot more, Please do keep up the awesome work.

Responder

Hey There. I found your blog using msn. This is a very well written article.
I will be sure to bookmark it and come back to read more of your useful info.

Thanks for the post. I will definitely return.

Responder

[url=https://tadalafilbestbuy.com/]how much is tadalafil cost[/url]

Responder

I am regular visitor, how are you everybody? This piece of writing posted at this web site is
genuinely fastidious.

Responder

Hey there just wanted to give you a quick heads up.
The words in your post seem to be running off
the screen in Opera. I’m not sure if this is a formatting
issue or something to do with internet browser compatibility but I thought I’d post
to let you know. The design and style look great though!
Hope you get the problem solved soon. Many thanks

Responder

[url=https://viagrabi.com/]prescription viagra 100mg[/url] [url=https://dbpills.com/]metformin 3000 mg[/url] [url=https://incrhealth.com/]order antabuse online canada[/url] [url=https://wbapharmacy.com/]best pharmacy[/url] [url=https://pharmacylewis.com/]best online pharmacy reddit[/url] [url=https://startpills.com/]lisinopril brand[/url] [url=https://wplasix.com/]over the counter lasix[/url] [url=https://tadalafilbestbuy.com/]cheap tadalafil 5 mg[/url] [url=https://cialisdpill.com/]buy cialis canadian pharmacy[/url] [url=https://tadalafilworx.com/]best price for tadalafil 20 mg[/url]

Responder

[url=https://viagrauni.com/]where can i get viagra in canada[/url] [url=https://tadalafilxt.com/]tadalafil over the counter canada[/url] [url=https://pharmacyomni.com/]top mail order pharmacies[/url] [url=https://crmeds.com/]can i buy prozac online[/url] [url=https://sildenafileasy.com/]sildenafil 120[/url]

Responder

[url=https://sildenafilvtab.com/]how to get viagra uk[/url] [url=https://incrhealth.com/]is a prescription required for antabuse[/url] [url=https://tadalafilbestbuy.com/]cheap tadalafil tablets[/url] [url=https://onlinemediorder.com/]where to buy modafinil us[/url] [url=https://unocialis.com/]cialis canada pharmacy[/url] [url=https://cialisstep.com/]tadalafil buy india[/url] [url=https://rxremi.com/]augmentin medicine price[/url] [url=https://cheapestsildenafil.com/]sildenafil 110 mg capsule[/url] [url=https://pharmacylewis.com/]order pharmacy online egypt[/url] [url=https://sildenafilzpill.com/]sildenafil 20 mg prescription online[/url]

Responder

Very good site you have here but I was wanting to know if you knew of
any message boards that cover the same topics talked about in this article?
I’d really like to be a part of online community where I can get comments from other knowledgeable
individuals that share the same interest.
If you have any suggestions, please let me know.

Cheers!

Responder

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

Responder

[url=https://dbpills.com/]generic for glucophage[/url] [url=https://opivermectin.com/]ivermectin buy online[/url] [url=https://tadalafilxt.com/]tadalafil 40 mg india[/url] [url=https://cialisexpress.com/]real cialis online[/url] [url=https://ordertadalafilpills.com/]buy brand cialis online[/url]

Responder

[url=http://viagraconn.com/]sildenafil women[/url] [url=http://opivermectin.com/]ivermectin iv[/url] [url=http://cheapestsildenafil.com/]sildenafil 50 mg coupon[/url]

Responder

[url=http://viagraxbuy.com/]otc sildenafil in us[/url] [url=http://buyrpills.com/]buy doxycycline south africa[/url] [url=http://sildenafilpharm.com/]sildenafil 50 mg buy online india[/url] [url=http://opivermectin.com/]stromectol 12mg online[/url] [url=http://cialismedicine.com/]where can i buy cialis tablets[/url] [url=http://cialisconnect.com/]buy real cialis online canada[/url] [url=http://aventolin.com/]buy ventolin over the counter nz[/url] [url=http://phdtabs.com/]best generic finasteride brand[/url]

Responder

[url=http://wplasix.com/]furosemide 40[/url] [url=http://sildenafilmedicine.com/]where to buy female viagra[/url] [url=http://viagraconn.com/]generic viagra australia online[/url] [url=http://cialisextr.com/]genuine cialis price[/url] [url=http://xrpills.com/]accutane online australia[/url]

Responder

[url=https://sildenafilmedicine.com/]best viagra tablet[/url] [url=https://sildenafilpharm.com/]can i buy viagra in europe[/url] [url=https://cialisstep.com/]cialis online cheap[/url] [url=https://viagrabuycheap.com/]viagra online in india[/url] [url=https://cialisetabs.com/]real cialis from canada[/url] [url=https://xrpills.com/]accutane pills for sale[/url] [url=https://viagraxbuy.com/]viagra over the counter mexico[/url] [url=https://viagrauni.com/]how to purchase viagra online in india[/url] [url=https://cialisextr.com/]cialis daily 5mg online[/url] [url=https://buyrxtablets.com/]can i buy clomid in mexico[/url]

Responder

[url=https://cialismedicine.com/]20 mg cialis daily[/url]

Responder

[url=http://wbapharmacy.com/]online pharmacy denmark[/url] [url=http://tadalafilbestbuy.com/]tadalafil for sale uk[/url] [url=http://viagrahtab.com/]buy viagra in us online[/url] [url=http://sildenafileasy.com/]sildenafil 130[/url] [url=http://buyrpills.com/]doxyciclin[/url] [url=http://rxremi.com/]amoxicillin 500 mg capsules[/url] [url=http://fasttadalafil.com/]cialis 10mg online india[/url]

Responder

[url=https://buyrpills.com/]doxycycline without a prescription[/url] [url=https://cialisextr.com/]discount cialis for sale[/url] [url=https://sildenafilzpill.com/]sildenafil canada generic[/url] [url=https://kamagrabuyonline.com/]kamagra online paypal uk[/url] [url=https://viagraconn.com/]viagra otc uk[/url] [url=https://viagrahtab.com/]cost of viagra pills in india[/url] [url=https://pviagra.com/]buy real viagra from canada[/url] [url=https://viagrabi.com/]viagra price singapore[/url]

Responder

[url=http://medicinecialis.com/]cialis brand coupon[/url]

Responder

Magnificent goods from you, man. I’ve understand your stuff previous to and you are just too great.
I actually like what you’ve acquired here, certainly
like what you’re stating and the way in which you say it.
You make it enjoyable and you still take care of to keep
it smart. I cant wait to read far more from you. This is actually a great site.

Responder

Having read this I thought it was extremely informative.
I appreciate you finding the time and energy to put this informative article
together. I once again find myself personally spending way too much
time both reading and posting comments. But so what, it was still worthwhile!

Responder

You’ve made some really good points there.
I checked on the internet for more info about the issue and found most people will go along with your views on this
website.

Responder

Good answer back in return of this matter with real arguments and describing everything concerning that.

Responder

[url=https://tadalafillowprice.com/]cialis generic in us[/url] [url=https://xviagrahot.com/]sildenafil 25 mg buy online[/url] [url=https://accmeds.com/]motilium 30 mg[/url] [url=https://healthsupl.com/]can you order modafinil online[/url] [url=https://tadalafilbestbuy.com/]cost of tadalafil[/url] [url=https://oxlpharm.com/]hydroxychloroquine buy online uk[/url] [url=https://bestviagrapills.com/]viagra 100mg price usa[/url] [url=https://opivermectin.com/]price of ivermectin[/url] [url=https://phdtabs.com/]where to buy finasteride online[/url] [url=https://cialisextr.com/]generic cialis 20mg uk[/url]

Responder

Saved as a favorite, I like your blog!

Responder

Hey There. I found your weblog the use of msn. That is
an extremely smartly written article. I will make
sure to bookmark it and return to learn more of your
useful info. Thank you for the post. I’ll definitely
return.

Responder

[url=https://ivermectinbestbuy.com/]order stromectol[/url]

Responder