Hojas de cálculo en Excel - página principal

Impedir que se cambie el nombre a una hoja

En un artículo anterior hablé de cómo podíamos proteger nuestra aplicación, para impedir que los usuarios le cambien el nombre al fichero. En realidad, se le puede cambiar el nombre, pero si se hace eso, no se podrá trabajar con el libro de excel, pues al abrirlo, nos mostrará un mensaje, y nos indicará cual es el nombre correcto que debe tener, para poder utilizarlo, y a continuación se cerrará automáticamente el libro.

En esta ocasión haremos algo similar, pero con las hojas de cálculo, o si lo preferís, las pestañas, y utilizando el sentido común. En realidad, lo que vamos a hacer es permitir momentáneamente que el usuario le cambie el nombre a las hojas (pestañas), pero en el momento de cerrar el fichero, todas las hojas volverán a tener el nombre que tenían inicialmente.

Veamos lo que podría hacer un usuario con las pestañas de un libro:

1.- Podría cambiar el nombre de alguna o de todas las pestañas, trabajar con el libro, y cerrarlo sin guardar los cambios.
2.- Podría cambiar el nombre de alguna o de todas las pestañas, trabajar con el libro, y cerrarlo guardando los cambios realizados.
3.- Podría dejar el nombre las pestañas tal y como están, trabajar con el libro, y cerrarlo sin guardar los cambios.
4.- Podría dejar el nombre las pestañas tal y como están, trabajar con el libro, y cerrarlo guardando los cambios realizados.

En el caso número 1, no hay problema, pues al no grabar los cambios, el fichero siempre mantendrá los nombres originales en sus hojas o pestañas. En el caso 3 y 4, sería exactamente lo mismo, pues al no cambiar los nombres de las pestañas, no tendríamos porqué preocuparnos de nada. Solo el caso 2, es el que querremos controlar, para que los usuarios no le pongan nombres distintos a las hojas, de los que tenían inicialmente.

Supongamos que tenemos un libro con 3 hojas, que se llaman CANTIDAD VENDIDA, PRECIOS UNITARIOS, e INGRESOS TOTALES, tal y como muestra la siguiente imagen:


Si un usuario le cambia el nombre de una de las pestañas, por ejemplo la primera, la que se llama CANTIDAD VENDIDA, y le pone por ejemplo el nombre de VENTAS, el usuario podrá trabajar con el libro, con todas sus hojas, y no tendremos el más mínimo problema (excepto si en nuestros macros referenciamos la selección de hojas, en función del nombre visible que tienen, cosa que veremos luego como se puede solucionar), pues en el momento de cerrar el libro, le cambiaremos el nombre, y le pondremos el que tenía inicialmente.

Para ello, nos bastará con copiar unas sencillas líneas de código, dentro de Thisworkbook, tal y como se aprecia en la siguiente imagen:


El código que tendremos que introducir es este:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Esto se ejecutará justo unos instantes antes de guardar el fichero
If Hoja1.Name <> "CANTIDAD VENDIDA" Then Hoja1.Name = "CANTIDAD VENDIDA"
If Hoja2.Name <> "PRECIOS UNITARIOS" Then Hoja2.Name = "PRECIOS UNITARIOS"
If Hoja3.Name <> "INGRESOS TOTALES" Then Hoja3.Name = "INGRESOS TOTALES"
End Sub

Ese código lo que hace es evaluar el nombre de las hojas, y si éstas tienen un nombre diferente al que deberían tener, entonces se lo cambia para ponerle el nombre que les corresponde.

Muchos de los usuarios que llegan a este blog, buscan precisamente esto, para impedir que los usuarios le cambien el nombre a las pestañas. Es probable que lo hagan porque en sus macros, cuando seleccionan una hoja de cálculo, lo hacen de la siguiente forma (imaginemos que el nombre de la hoja sea PRECIOS UNITARIOS):

'Seleccionamos la hoja de PRECIOS UNITARIOS
Sheets("PRECIOS UNITARIOS").Select

Si alguien le cambia el nombre a la pestaña PRECIOS UNITARIOS, y ejecutamos un macro donde seleccionemos la hoja de PRECIOS UNITARIOS llamándola tal y como figura en esa línea de código, obtendremos este bonito mensaje de error, pues la hoja PRECIOS UNITARIOS no se puede seleccionar, ya que no existe, pues se le ha cambiado el nombre:


Lo más sencillo para solucionar esto, es trabajar siempre con el nombre interno de las hojas que podemos ver en modo VBA, justo delante del nombre que se ve cuando trabajamos en modo usuario (el nombre visible de la hoja, o de la pestaña). En la siguiente imagen podemos ver dentro del recuadro rojo, los nombres internos de las hojas, y a su derecha, entre paréntesis, el nombre visible de las mismas:


El nombre interno que acabamos de ver, también se puede cambiar, pero para ello deberemos acceder al modo VBA, y por supuesto deberá estar desprotegido nuestro código. Si protegemos el código VBA, no tendremos ningún problema, pues ese nombre no estará al alcance de los usuarios y por tanto, no lo podrán manipular.

Si deseamos seleccionar una hoja de cálculo desde un macro, os recomiendo siempre que trabajéis con el nombre interno. Fijaos que la forma de llamar a las hojas varía un poco, si lo hacemos llamándolas por su nombre interno o por su nombre visible. Si por ejemplo queremos seleccionar la hoja llamada PRECIOS UNITARIOS, nos bastará con hacerlo de la siguiente forma, y así nos evitaremos problemas con los macros si un usuario le cambia el nombre a la pestaña:

'Seleccionamos la hoja de PRECIOS UNITARIOS
Hoja2.Select

Como veis, es algo bastante sencillo, y nos va a servir para solucionar algunos posibles errores dentro de nuestros macros.

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.



18 comentarios:

Anónimo dijo...

Hola, sigo revisando tu pagina, y me parece fantástica, cada aporte que tu haces es muy valioso, me voy a dar la tarea de ir revisando uno por uno y quiero pedirte si me permites obtenerlo para usarlo en mi organización.

Un saludo.

Javier Marco dijo...

Por supuesto. Todo lo que hay en el blog es para compartirlo con los usuarios que llegan hasta aquí.

Un saludo.

Anónimo dijo...

Ha que te dedicas?, de donde eres? se nota que sabes mucho en temas de programación.

Javier Marco dijo...

Soy español, pero no tengo nada que ver con el mundo de la informática. He aprendido Excel por necesidades del trabajo :-)

Unknown dijo...

Hola, quisiera saber como puedo hacer de codigo para proteger de escritura ciertas celdas.

sdos

Gu

carlos ladino dijo...

VIEJO UN FAVOR ESTOY TRABAJANDO CON OFFICE 2007 Y NO ME FUNCIONA EL TRABAJAR CON EL NOMBRE INTERNO DE LA HOJA COLOCANDO EL CODIGO COMO Hoja1.Select que es lo que puede ocurrir?? gracias .

Javier Marco dijo...

Pues en principio debería funcionarte. Fíjate en la última imagen que hay en este artículo que has leído, y mira el nombre de las hojas, a nivel interno, es decir, el nombre que aparece enmarcado en rojo en la imagen. Igual no te funciona porque no se llama hoja1.

Anónimo dijo...

Hola, por razones de trabajo también he tenido que aprender macros para excel (vba) y buscando en internet llegué a este blog que me ha sido de bastante utilidad....gracias!

Con respecto al código para volver a los nombres originales de las hojas antes de guardar el archivo, al tratarse de la instrucción IF THEN, no debería llevar cada una (cada if) un end if???
O igual funciona sin el end if?

saludos, andrea

Javier Marco dijo...

Buena pregunta...

Si la instrucción está en una única línea, no es necesario finalizarla con el End If. Es decir, esto:

If persona="Andrea" Then inteligente="si"

Es lo mismo que esto otro:

If persona="Andrea" Then
inteligente="si"
End If

Pero esto te daría error, por estar en 2 líneas, y no finalizar el condicional con el End If:

If persona="Andrea" Then
inteligente="si"

Anónimo dijo...

muy bueno saberlo....gracias por la aclaración.....
cada día se aprende algo nuevo no?

saludos, andrea

Anónimo dijo...

Hola Tu pagina esta increible, es de gran ayuda y gracias de antemano, quisera hacer una pregunta, solo dime si o no, necesito saber si hay una opcion dentro de la propiedad worksheet que impida minimizar la hoja de calculo. y si tienes tiempo me la respondas plis, encontre una pero te quita solo por un instante y encontre otra que quita las de excel.

Leo dijo...

Javier Marco, Buen día

Me gustaría saber cómo se cambia el nombre interno de las hojas de cálculo, en el blog hablas de que se puede cambiar, pero no especificas cómo.

Gracias por tu ayuda

Javier Marco dijo...

Pues es tan sencillo, como hacer doble clic en el nombre de la pestaña que deseas cambiar, y escribir el nuevo nombre.

Si no vieras las pestañas, en Herramientas --> Opciones --> Pestaña Ver --> Muesca en "Etiquetas de hojas".

Leo dijo...

Buen día Javier Marco, Gracias por la respuesta.

No, no es tan sencillo como eso.

Mi pregunta hace referencia al nombre interno de la hoja de cálculo. Al abrir un documento aparecen 3 hojas (Hoja1, Hoja2, Hoja3) y efectivamente yo puedo cambiar el nombre de esas hojas a "Ventas", "Comisión", "Valor"... lo que sea. Sin embargo, si entras en el editor de VB, en el explorador de proyectos vas a ver algo como:
Hoja1(Ventas)
Hoja2(Comisiones)
Hoja3(Valor)

Esos nombres (Hoja1, Hoja2 y Hoja3) son los que yo necesito modificar a través del código. Encontré una forma manual y es modificar la propiedad (name) de el objeto Hoja1, pero la forma a través de código VB no la he encontrado.

Me podrías ayudar?

Gracias

Leo dijo...

Javier Marco, saludos.

Ya he solucionado el problema.

Se debe Configurar en el menú Herramientas - Macro - Seguridad.
En la ficha Fuentes de confianza, clic en Confiar en el acceso a proyectos de Visual Basic.

Por otro lado, la línea para hacer el cambio de nombre(interno) es:

ThisWorkbook.VBProject.VBComponents(Sheets(2).CodeName).Name = "Nuevo_Nombre"

Gracias por su ayuda

Javier Marco dijo...

Gracias por la autorrespuesta ;-)

Anónimo dijo...

Muy interesante, nunca había ejercitado esto de las macros, donde puedo aprender más, gracias infinitas por la ayuda.

Atentamente Adolfo Zayola antonio_zayola@hotmail.com

Anónimo dijo...

como esta Javier,mucahs gracias por aportar tu conocimiento en prvecho de los demas.
tengo unas dudas baje el archovo de los coches yo lo aplico para un catalogo de repuestosEjemplo tengo una cloumna con codgis validados como en su archivo son digamos 300codigos A1,A2,A3,A4 etcyo pongo un hypervinculo que me lleva a una celda especifia en otra hojatodo funciona bien pero la pregunta es como arrastro ese codigo hasta esa celda automaticamente para nada maas dar enter y que apareca la foto
la otra cos es bloquear la hoa al hacerlo ya no puedo ver las fotos