Control de Acceso a Usuarios en Excel (Versión Simple)

En este artículo vamos a ver cómo controlar los accesos de usuarios a nuestra mini aplicación. Suponiendo que tenemos tres tipos de Usuario, de Administrador, de Invitado y Usuario Standard. Cada uno de estos usuarios tiene niveles de accesos diferentes. El primero puede ver todas las hojas, además puede crear usuarios, el segundo puede ver y escribir en todas las hojas, el tercero solo puede ver la hoja de informe.
En el ejemplo queremos resumir una forma de hacerlo, no quiere decir que sea la única. Es una forma sencilla en la que ocultamos las hojas dependiendo el nivel de acceso.
Por otro lado, esto se puede complejizar tanto como se quiera.


Click en la imagen para verl el video o en este Link.
En nuestro ejemplo usamos un nombre de fantasía para nuestra aplicación, es algo que se nos ocurrió para el ejemplo.
Vamos a empezar a explicar los elementos generales:
En primer lugar tenemos lo que sería la página de inicio, dónde esta el formulario de logeo o donde uno ingresa el usuario y contraseña para ingresar al sistema.



Como ven se usan dos celdas, una para el usuario y otra para la password. La celda del usuario tiene una lista de usuarios tomados de la hoja Usuarios. Estos fueron creados por el administrador. La celda de la contraseña tiene formato personalizado para que nos muestre asteriscos una vez ingresada la constraseña. Además tambien vemos que no se muestran las otras hojas de la mini aplicación.


En la parte de abajo hay un texto que nos dice si la constraseña y usuario es correcta. Además nos da la bienvenida.
Por otro lado, tenemos un boton “ingresar” al cuàl vamos a asociar nuestra macro para que funcione la mini aplicaciòn.
Ahora vamos a ver lo que contiene la hoja de “Usuarios”, que solo puede ser vista por el administrador del sistema.


En la figura se pueden ver ahora , todas las hojas de nuestra aplicación. Esto es lo que veria el administrador del sistema. Todas las hojas.
Se puede ver que se uso para comprobar la identidad un comparador en la celda “E4”.Si para ese usuario coincide la contraseña da “Verdadero” sino “Falso”. Esto lo tomaremos en el código.
En la hoja del “Usuarios” estan todos los usuarios con varios campos, el nombre del usuarui, la constraseña y el tipo de acceso.
El administrador puede agregar los usuarios que desee teniendo en cuenta que solo existen, en este caso, tres niveles de acceso; administrador, invitado y standard. En el campo tipo de acceso también usamos una validación de datos para elegir la opcion.
Ahora vamos a ver la hoja con la definicion de los niveles de acceso:


Aquí el administrador puede crear si lo desea otro nivel de acceso pero con la salvedad que podría ser necesario modificar el código. Como no es necesario que el administrador sepa de código, lo más probable es que lo modifique un “Super Usuario” o quíen creo la aplicación.
Ahora vamos a ver las hojas que contienen los datos, en este caso suponemos, que se trata de una aplicación que controla el ausentismo de los trabajadores y así poder controlar el salario.


Estos datos, los de las hojas BD1, BD2 e informe son los que van a usar los usarios. En el caso del usuario estándar podrá ver todas las hojas (BD1, BD2 e Informe), en el caso del invitado solo el informe. Como lo planteamos ni siquiera es necesario proteger las hojas.


Además tenemos, como vemos, un boton “Exit” o salida que nos hace regresar a la pagina de inicio y resetea el sistema, asociada a la macro correspondiente.
Procedimiento
Ahora vamos a repasar lo que tendremos que hacer crear nuestras dos macros. La primera que va a estar asociada al boton “Ingresar” y la otra al boton “Exit”.
La macro “Ingresar” tendrá que decidir que hacer de acuerdo al usuario que ingresa, que a su vez esta asociado a los tres niveles de acceso. Entonces:
·         Administrador: va a poder ver todas las hojas y trabajar en ellas.
·         Standard: es el usuario normal o comun, que trabaja ingresando datos, va a poder ver BD1, BD2 e informe. En este caso, usamos estas tres hojas para representar cualquier cantidad de datos que podamos tener en el sistema.
·         Invitado: el invitado generalmente no puede ver todas las hojas, escribir o editar, en este caso podrá ver solamente la hoja de informe.
La macro “Salir” ocultan todas las hojas que no sean la de “Inicio” y borra la contraseña ingresada al principio.
Como ya sabemos para tomar decisiones condicionales podemos utilizar la funcion “Si” o en inglés “if..then..End if”. Pero para este caso nos parecio sencillo usar otro metodo denominado “Select Case” que ya vamos a ver como funciona. Si quieres investigar para aprender mejor como funciona, lo pueden hacer en la ayuda sino buscando en internet donde hay mucha información.

En sintexis, vamos a mostrar como se define:

Select Case Variable        ‘elige el caso según la variable
Case Variable 1 ‘si la variable toma el valor Variable 1 elige este caso.
Codigo asociado al caso…
Case Variable 2 ‘si la variable toma el valor Variable 2 elige este caso.
Codigo asociado al caso…
Case Variable n ‘si la variable toma el valor Variable n elige este caso.
Codigo asociado al caso…
End Select       ‘Termina la definicion de los casos.

Como dijimos antes, se puede investigar y buscar de ejemplos de aplicación de esto.

Código
Ahora vamos a ver la macro “Ingresar” que tenemos para esta aplicación sencilla:
Sub Ingresar()
'Autentico el Usuario y Contraseña
If Sheets("Usuarios").Range("E4").Value  Then
                 'eligo el caso
'la variable esta representada por lo el dato que tenemos en la hoja usuarios en la celda D4. Esta celda muestra el mismo dato de la celda usuario en la hoja de inicio.
Select Case Sheets("Usuarios").Range("D4").Value Case "Administrador"
'Administrador
‘el administrador puede ver todas las hojas por lo que hacemos visible todas las hojas. Para saber este codigo creamos una macro de prueba.
Sheets("Usuarios").Visible = True
Sheets("Nivel_Acceso").Visible = True
Sheets("BD1").Visible = True
Sheets("BD2").Visible = True
Sheets("Informe").Visible = True

Case "Standard"
'Standard
‘en este caso solo hacemos visible las tres hojas.
Sheets("BD1").Visible = True
Sheets("BD2").Visible = True
Sheets("Informe").Visible = True
Case "Invitado"
'Invitado
‘solo vemos la hoja de informe
Sheets("Informe").Visible = True
End Select
End If
End Sub

Este codigo hace visible las hojas, por lo que en primer termino deberemos ocultarlas. Para este caso vamos a usar una propiedad que tiene cada hoja, que se llama “Visible”.

Esta propiedad puede tomar tres valores que son: 0 (xlSheetHidden), -1 (xlSheetVisible) y 2 (xlSheetVeryHidden). La primera hace “oculta” la hoja, la segunda “visible” y la tercera “muy oculta”.

En nuestro caso vamos a usar las propiedades 0 y 2. En este caso para hacer visible o invisible la hoja también podemos usar la propiedad puesta en “True” para visible o en “False” para invisible.
Cabe destacar que en este caso, para este ejemplo, decidimos que hojas mostrar o no pero podemos en vez de ello decidir que hojas proteger o no, por ejemplo.

Ahora vamos a ver el código para el botón “Exit” o Salir.
Sub Salir()
Select Case Sheets("Usuarios").Range("D4").Value
Case "Administrador"
'Administrador
Sheets("Usuarios").Visible = 2
Sheets("Nivel_Acceso").Visible = 2
Sheets("BD1").Visible = 2
Sheets("BD2").Visible = 2
Sheets("Informe").Visible = 2
Case "Standard"
'Standard
Sheets("BD1").Visible = 2
Sheets("BD2").Visible = 2
Sheets("Informe").Visible = 2
Case "Invitado"
'Invitado
Sheets("Informe").Visible = 2
End Select
'borro contraseña
Sheets("Inicio").Range("F19").Value = ""
Sheets("Inicio").Range("F19").Select
End Sub

Al contrario del código para “Ingresar” este código oculta las hojas. Además vuelve a la hoja Inicio y borra la contraseña anterior.

Como ven no es muy complicado y además es sencillo. A esto se le puede agregar mayor complejidad, por ejemplo protegiendo las hojas o incluso para usuarios más expertos se puede proteger el código completo. Esto último sería lo mejor.
¡Esperamos que les haya gustado la idea y vean lo fácil que es!

-------------------------------------- FIN CÓDIGOS
Recuerden que los artículos algunas veces requieren un mayor o menor nivel de conocimiento en ciertos temas de Excel, por ejemplo formulas avanzadas, macros o VBA, etc. Sin embargo en los artículos se nombran todos los elementos y con esta información podemos hacer nuestras pequeñas investigaciones usando internet, así desarrollamos nuestro conocimiento. Por ejemplo si  utilizamos la función “Y”, que la suponemos conocida, y no sabemos ¿qué es? podemos buscar en internet para ver como se utiliza y cuáles son los componentes. Las mini aplicaciones no son más que la recopilación de muchos elementos para dar forma a una nueva idea.
Pueden descargar algunos ejemplos (plantillas/templates) de la zona de Descargas. Si tienen alguna duda o consulta pueden hacerlo a través del formulario de contacto.
Les recomendamos que sigan el blog por alguno de los medios, mail o por redes sociales, para recibir las actualizaciones y novedades.

¡Éxitos!

Keys:
·         Control de Acceso a Usuarios.
·         Acceso con Usuario y Contraseña.
·         Cambiar Nivel de Acceso a usuarios en Excel.
·         Tipos de Acceso de Usuario en Excel.

·         Excel Access Control with username and password.

14 comentarios:

Jorge Perez dijo...

y este tipo archivo puede trabajarse en red por 4 usuarios a la vez??

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por comentar!
En cuánto a tu consulta, se puede arreglar para trabajar de esta manera. Tiene la opción de compartir libro pero hay que tener ciertos cuidados. Las opciones por el momento son bastante limitadas. No debería ser así, deberían haberlo solucionado para versiones mas recientes. Porque es muy necesario hoy en día. Para la última versión han agregado más funciones pero aún no es completamente flexible y útil.
Esperamos tus comentarios.
Saludos

Jorge Perez dijo...

Hola de nuevo

Gracias por responder.

Tengo un archivo en excel que pretendo manejar en la intranet o red de mi trabajo, pero ahorita mi complicación o duda es si al momento de accesar por medio de su usuario y contraseña y ejecuten las macros exista algún conflicto o error al guardar la información.

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por comentar.
Es cuestión de hacer el ejemplo y probar cómo reacciona Excel. La verdad que no esta preparada para trabajar multiusuario. Las versiones más recientes dejan más posibilidades de compartir y trabajar en equipo pero no de limitar el acceso a ciertas partes, etc.
Hay otras formas de hacer esto, una de ellas quizá sea generar una base de datos por ejemplo con "access" y cuando abres el archivo de "excel" te conectas con esta base de datos que esta protegida.
Veremos si subimos un artículo al respecto más adelante.
Gracias, un saludo.-

YNDIRA BASTARDO dijo...

HOLA UNA CONSULTA ESTO ES PARA TRABAJAR EN UNA MISMA COMPUTADORA O SE PUEDE COMPARTIR EN VARIAS COMPUTADORAS Y QUE VARIOS PUEDAN INTRODUCIR DATOS A LA VEZ

Juan Pablo Torres dijo...

Hola amigo, cómo estas!
A ver, vamos a intentar responderte para que lo entiendas. En primera instancia la respuesta sería "Si" pero tiene sus recaudos.

Primero, si lo pones en una carpeta compartida todos tendrán acceso. El tema es que hay que ver lo que pasa con las macros cuando esta compartido el archivo. Puede que se desactiven y no puedas ejecutar ninguna acción.

Para poder crear un archivo verdaderamente compartible y modificable deberías crear una base de datos aparte al cual vayan a parar todos los cambios y la información. Y en cada PC tener el archivo original (la última versión). De esta manera no tendrías problemas cuando las macros se ejecuten.

Tenemos un artículo pendiente de este tema. Es una lástima que no lo podamos subir todavía. ya nos han consultado sobre lo mismo si te fijas.

Esperamos tus comentarios.

Saludos

camilo riaño vargas dijo...

hola muchas gracias por este blog es de mucha ayuda mira me sale un error sub indice fuera de intervalo que que se debe

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por escribirnos!
Ese error es porque estas seleccionando un valor que no esta dentro del rango de datos. Por ejemplo "25" y los datos llegan a 24.
Saludos

Jorge Perez dijo...

Bueno despues de mucho logre adaptar un form para el control de acceso a usuarios a mi archivo

Aun así su mini app fue una inspiración y guía para esforzarme un poco más en mi muy bajo conocimiento de programación con macros

Saludos

Dr. ADEL ZARATE M. dijo...

Es de mucha ayuda,esperamos la información sobre la base de datos en acces

Juan Pablo Torres dijo...

Hola amigos, cómo están! Gracias por sus comentarios.
Saludos

Mauricio Jerez dijo...

Hola amigo, que tal buenas tardes... Encuentro genial el control de acceso, lo cual me ha permitido crear un sistema con acceso a usuario muy bueno, el cual me permite automatizar bastantes procesos, pero el problema es que cuando ingreso al sistema, este no vuelve a la página de inicio, quedando en otra hoja. Es posible que me puedas orientar, en como hacer que mi sistema queda en la página de inicio... De antemano gracias.

ESUEROS dijo...

Quisiera saber si se puede llenar un cuadro por un intervalo de datos? Es decir que en otra hola quiero que se lleguen los datos del 100 al 130 con los datos de la hoja.

Juan Pablo Torres dijo...

Hola amigo, cómo estas! No se entiende el comentario pero muchas gracias por hacerlo. Seguro que se puede hacer.
Saludos