Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como SOLICITAR PASSWORD CONTRASEÑA para OCULTAR y MOSTRAR Hojas de Excel VBA #521

password contraseña para ejecutar macro oculta y muestra hojas excel

Requerir Contraseña o Clave para Ejecutar una Macro

En esta entrega se mostrará un formulario que requiere contraseña, clave o password para ejecutar una macro, en este caso ocultar y mostrar las hojas de Excel, es decir como ocultar y mostrar hojas de Excel requiriendo para ello el ingreso de una Contraseña válida a través de un formulario de Excel VBA.

Deseas aprender Excel, si la respuesta es SI, hazlo 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.

Ejecutar Macro previo Validar si se Ingresó Contraseña o Password

La macro que se muestra en este post, cuyo libro de Excel usado como ejemplo se encuentra disponible para ser bajado desde el link que se encuentra al final del post, una vez abierto el libro se observa un botón al presionar el mismo se muestra un formulario que solicita un Password.

En este caso al ingresar el Password o clave correcta se muestran u ocultan las hojas de Excel según sea el caso, es decir si están visibles las oculta, si están oculta las muestra.

En este caso se procede a mostrar u ocultar hojas de Excel, pero se puede adaptar para que el formulario solicite Password o clave previo a ejecutar cualquier macro, es decir que con ello se impide que cualquier usuario ejecute una macro, solo podrá ser ejecutada por el usuario autorizado y es aquel que posee la clave correcta.

Se debe notar que en el mismo formulario se encuentra un Label con un icono de un «ojo», haciendo click sobre dicho objeto se puede mostrar la clave si se vuelve a hacer click la clave vuelve al modos «PasswordChart», es decir se muestran unos asteriscos (****) en lugar del texto en el Textbox destinado a ingresar la clave.

Detalle del código que muestra formulario y solicita password para ejecutar macro

Cuando se presiona el botón aceptar se procede  a determinar si lo escrito en el Textbox destinado a ingresar la clave es igual a la clave que en este caso se guarda en la variable «resp» cuya clave es «admin».

resp = «admin»

If TextBox1 = resp Then

En caso de haberse ingresado el Password correcto, oculta el formulario que solicita la contraseña y llama la macro para mostrar y ocultar hojas de Excel, con el siguiente código:

Unload Me
Call MuestraOculta

En caso que la contraseña ingresada no sea correcta sale un Msgbox (mensaje) avisando que el Password ingresado no es válido terminando la ejecución de la macro sin hacer ningún cambio, el código es el siguiente:

Else
MsgBox («El password ingresado no es válido»), vbInformation, «AVISO»

La macro que llama el formulario cuando se ingresó correctamente la contraseña permite ocultar y mostrar hojas, algo que ya fue explicado en el ejemplo como ocultar y mostrar hojas con un solo botón, el cual sugiero leer si se necesita una explicación más detallada en como se puede ocultar y mostrar hojas en Excel.

Para ocultar y mostrar hojas se determina cuantas hojas hay en total y se hace un bucle de la hoja 2 hasta la última hoja, ocultando todas las hojas, para ello se usa este código:

For ii = 2 To Sheets.Count
Sheets(ii).Visible = xlVeryHidden
Sheets(1).CommandButton1.Caption = «MOSTRAR HOJAS»
Next ii

Mostrando las hojas de Excel en caso que hayan estado ocultas, con el siguiente código:

Else
For ii = 2 To Sheets.Count
Sheets(ii).Visible = True
Sheets(1).CommandButton1.Caption = «OCULTAR HOJAS»
Next ii
End If

Para mostrar y ocultar el Password ingresado se debe hacer click en el Label u icono con ojo verde, la codificación está en el evento click de Label y lo único que hace es determinar si el Textbox tiene la propiedad «PasswordChar», es decir se oculta el texto con símbolos en este caso asteriscos (*****), en ese caso muestra el texto y si el texto es visible se vuelve a establecer la propiedad del Textbox en  «PasswordChart», quedando el Textbox son asteriscos (*****) nuevamente; el código usado es el siguiente:

If TextBox1.PasswordChar = «*» Then
TextBox1.PasswordChar = «»
Else
TextBox1.PasswordChar = «*»
End If

Descarga el Libro usado para Ocultar Mostrar Hojas de Excel mediante Ingreso de Contraseña o Clave

Descarga el libro usado en este ejemplo 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 Formulario que solicita Password para ejecutar un Macro

Código que se ingresa en un Userform

Private Sub CommandButton1_Click()
Dim resp As String
resp = «admin»
If TextBox1 = resp Then
Unload Me
Call MuestraOculta
Else
MsgBox («El password ingresado no es válido»), vbInformation, «AVISO»
TextBox1 = Clear
TextBox1.SetFocus
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub Label3_Click()
If TextBox1.PasswordChar = «*» Then
TextBox1.PasswordChar = «»
Else
TextBox1.PasswordChar = «*»
End If
End Sub

Private Sub UserForm_Initialize()
TextBox1.PasswordChar = «*»
End Sub

Código que se ingresa en un Modulo

Sub MuestraOculta()
Application.ScreenUpdating = False
If Sheets(2).Visible = True Then
For ii = 2 To Sheets.Count
Sheets(ii).Visible = xlVeryHidden
Sheets(1).CommandButton1.Caption = «MOSTRAR HOJAS»
Next ii
Else
For ii = 2 To Sheets.Count
Sheets(ii).Visible = True
Sheets(1).CommandButton1.Caption = «OCULTAR HOJAS»
Next ii
End If
Sheets(1).Select
Application.ScreenUpdating = True
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 (43)

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

No matter if some one searches for his necessary thing, thus he/she
wishes to be available that in detail, so that thing is
maintained over here.

Responder

hey there and thank you for your information – I’ve certainly picked up something new from right here.
I did however expertise some technical points using this web site, as I experienced to reload the
site lots of times previous to I could get it to load correctly.

I had been wondering if your hosting is OK? Not that I
am complaining, but slow loading instances times will sometimes affect your
placement in google and can damage your quality score if ads and marketing with Adwords.
Well I am adding this RSS to my email and can look out for a lot more of your
respective fascinating content. Make sure you update this again very soon.

Responder

It’s really a cool and helpful piece of info. I’m glad that you just shared this
useful info with us. Please stay us informed like this. Thank you for sharing.

Responder

An interesting discussion is definitely worth comment. There’s no doubt that that you need
to write more on this subject matter, it might
not be a taboo subject but usually people don’t discuss such topics.
To the next! Best wishes!!

Responder

I just like the valuable info you provide on your articles.
I’ll bookmark your blog and test again here frequently.
I am moderately certain I’ll learn a lot of new stuff proper here!
Best of luck for the next!

Responder

Hi! I could have sworn I’ve visited this website before but after going through a few of the articles I
realized it’s new to me. Nonetheless, I’m definitely pleased I discovered it and I’ll be
book-marking it and checking back often!

Responder

I blog often and I really thank you for your content.
This article has truly peaked my interest. I’m going to take a note of your blog and keep checking for new information about once per week.
I subscribed to your RSS feed as well.

Responder

hello there and thank you for your info –
I have certainly picked up anything new from right here.
I did however expertise a few technical points using this web
site, as I experienced to reload the web site a lot of times previous to I could
get it to load properly. I had been wondering if your hosting is OK?
Not that I’m complaining, but slow loading instances times will sometimes affect your placement in google and could damage your quality score if ads and marketing with Adwords.

Anyway I’m adding this RSS to my e-mail and can look out for
much more of your respective exciting content. Make sure you update this again soon.

Responder

Howdy! Someone in my Myspace group shared this website with us so I came
to look it over. I’m definitely loving the information.
I’m book-marking and will be tweeting this to my followers!
Outstanding blog and fantastic design.

Responder

It’s going to be ending of mine day, but before finish I am reading
this enormous paragraph to increase my know-how.

Responder

obviously like your web-site however you have to test the
spelling on quite a few of your posts. Several of them are rife
with spelling issues and I in finding it very troublesome to tell the truth on the other hand
I will certainly come back again.

Responder

Marvelous, what a webpage it is! This web site presents helpful information to us, keep it up.

Responder

After I initially left a comment I seem to
have clicked on the -Notify me when new comments are added- checkbox and
from now on whenever a comment is added I receive 4 emails with
the exact same comment. Perhaps there is an easy method you can remove me from that service?
Thanks!

Responder

Way cool! Some very valid points! I appreciate you writing this write-up plus
the rest of the website is really good.

Responder

Hi just wanted to give you a brief heads up and let you know
a few of the pictures aren’t loading correctly.

I’m not sure why but I think its a linking issue. I’ve tried it in two different web browsers and both show the same results.

Responder

Hello There. I discovered your weblog using msn. This
is a very well written article. I’ll make sure to
bookmark it and come back to read extra of your useful information. Thanks for the post.
I will definitely comeback.

Responder

Hello There. I discovered your weblog the use of msn. That is a very well written article.

I will make sure to bookmark it and return to read extra of
your helpful information. Thank you for the post. I’ll definitely comeback.

Responder

I read this article completely about the difference of newest and preceding technologies, it’s remarkable article.

Responder

Hi it’s me, I am also visiting this website daily, this website is in fact pleasant and the users are actually sharing pleasant thoughts.

Responder

Hey! I just wanted to ask if you ever have any issues with hackers?
My last blog (wordpress) was hacked and I ended up losing months
of hard work due to no data backup. Do you have any solutions to stop hackers?

Responder

Very good information. Lucky me I found your website by accident (stumbleupon).
I have saved it for later!

Responder

Very quickly this site will be famous among all blog users, due to it’s nice content

Responder

It’s enormous that you are getting ideas from this paragraph as well as from
our argument made at this time.

Responder

I loved as much as you will receive carried out right here.
The sketch is tasteful, your authored subject matter stylish.
nonetheless, you command get got an impatience over that you wish be delivering the following.
unwell unquestionably come more formerly again as exactly the same nearly a lot often inside case
you shield this increase.

Responder

I have been exploring for a bit for any high quality articles or weblog posts on this kind of house . Exploring in Yahoo I ultimately stumbled upon this site. Studying this info So i am satisfied to express that I’ve a very just right uncanny feeling I discovered just what I needed. I such a lot surely will make sure to don’t forget this site and provides it a glance regularly.

Responder

Hi there, everything is going fine here and ofcourse every
one is sharing facts, that’s actually good, keep up writing.

Responder

Hurrah, that’s what I was searching for,
what a information! present here at this webpage, thanks admin of this website.

Responder

Thanks for sharing your thoughts about 우리카지노. Regards

Responder

As the admin of this web page is working, no doubt very rapidly it
will be famous, due to its feature contents.

Responder

Appreciation to my father who told me concerning this website, this webpage is truly amazing.

Responder

First of all I want to say terrific blog! I had a quick question which I’d
like to ask if you do not mind. I was interested to know how you center
yourself and clear your mind before writing. I have had trouble clearing my thoughts in getting my ideas out.

I do take pleasure in writing but it just seems like the first 10
to 15 minutes tend to be lost just trying to figure out how to begin. Any recommendations or tips?
Many thanks!

Responder

I just could not go away your website before suggesting that I actually enjoyed the standard info an individual provide in your visitors?
Is going to be again ceaselessly to check out new posts

Responder

I’m more than happy to discover this page. I want to to thank
you for your time for this particularly fantastic read!!
I definitely really liked every bit of it and I have you book-marked to look at new information on your site.

Responder

You really make it seem so easy along with your presentation however I find this matter to be
actually one thing that I believe I might by no means understand.
It kind of feels too complicated and extremely large for me.
I am taking a look ahead in your subsequent publish, I’ll attempt to get the hang of it!

Responder

Great post. I am experiencing some of these issues as well..

Responder

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

Responder

Hi would you mind letting me know which hosting company you’re using?

I’ve loaded your blog in 3 completely different web browsers and I must say this blog loads a lot faster then most.
Can you recommend a good internet hosting provider at a
fair price? Thanks, I appreciate it!

Responder

I like this post, enjoyed this one thankyou for putting up.

Responder

We are a group of volunteers and starting a brand new
scheme in our community. Your site provided us with helpful info to work on. You have performed an impressive activity and our whole neighborhood shall be thankful to you.

Responder

That is really interesting, You’re a very skilled blogger.

I’ve joined your feed and look forward to in the hunt for extra of your
great post. Additionally, I have shared your web site in my
social networks

Responder

Hello, I enjoy reading through your article. I wanted to write a little
comment to support you.

Responder

This is a topic that is close to my heart… Best wishes!
Where are your contact details though?

Responder