martes, septiembre 05, 2006

Limitar el área de trabajo en Excel – Actualización

El método de limitar el área de trabajo en Excel que proponía en mi entrada del mes de mayo, adolece de un serio defecto. Al guardar el archivo, Excel no conserva los cambios introducidos en el cuadro de propiedades. De manera que al volver a abrir el archivo después de haberlo guardado, el área restringida no existe.

La manera de sobreponerse a este problema es con una pequeña macro. De hecho se trata de un evento. Estas macros, como su nombre sugiere, son activadas cuando algún evento en particular sucede.

En nuestro caso queremos que cada vez que abramos el archivo, Excel establezca el área restringida. Suponiendo que el área sea el rango B3:E10 de la Hoja1, procedemos así:

1 – Abrimos el editor de Visual Basic (Alt+F11 o Herramientas--Macros--Editor Visual Basic)

2 – El la ventanilla VBAProject del archivo, seleccionamos con un clic el objeto ThisWorkbook




3 – Elegimos la opción Workbook. Inmediatamente debajo de Private Sub Workbook_Open(), escribimos Sheets(1).ScrollArea = "B3:E10"



4 - Volvemos al cuaderno Excel y lo guardamos.

A partir de este momento, cada vez que abrimos el cuaderno (Workbook_Open) Excel define el área de trabajo de la Hoja1 con el rango B3:E10.


Categorías: Varios_

Technorati Tags: ,

47 comentarios:

  1. Hola, hay alguna forma de ocultar las barras donde aparecen las letras (columnas) y los numeros (filas) de la hoja para aparentar de que todo estuviera hecho en una hoja tipo word.

    ResponderBorrar
  2. Hola,
    en el menú Herramientas, vas al menú Opciones. En el diálogo que se abre, vas a la pestaña "Ver" y anulas la marca en la opción "Encabezados de Fila y Columna".
    Puedes ir más lejos para lograr una similitud con Word y anular la marca en la opción "Etiquetas de hojas".

    ResponderBorrar
  3. Hola justamente estoy buscando proteger mi proyecto limitando el area en el que ingresaran datos algunos usuarios, pero en el ejemplo que propones solo limitas un area dentro de la hoja, como hacer para limitar mas areas dentro de una hoja, intente con el scrollarea pero me sale error ya que no permite mas que una area, gracias por tu ayuda

    ResponderBorrar
  4. Hola, si lo que buscas es limitar el área de ingreso de datos, puedes aplicar simplemente desbloquear las celdas relevantes y proteger la hoja con contraseña.
    El método descrito en esta nota muestra como delimitar un área determinada dejando todo el resto de la hoja fuera del alcance del usuario.

    ResponderBorrar
  5. hola:

    necesito limitar el area de trabajo de la a1 a la q1000, y con este metodo puedo hacerlo, pero no me deja despés seleccionar una fila (con el encabezado de fila) para hacer autofiltro.

    sabrías decirme como puedo hacerlo?'

    saludos y gracias

    ResponderBorrar
  6. Hola,
    la limitación del área no limita o impide el uso de Autofiltro. Puedes describir con más detalle lo que haces?

    ResponderBorrar
  7. Hola Jorge

    He encontrado extremadamente util tu Blog y te felicito.

    En este tema hay una opcion en propiedades llamada EnableSelection, estoy tratando de usarla parecido a lo que indicas para ScrollArea cada vez que se abra el archivo pero creo que no he encontrado la forma de hacerlo.

    Muchas gracias

    ResponderBorrar
  8. La propiedad EnableSelection funciona sólo cuando la hoja está protegida. Puede ser que este sea el problema?

    ResponderBorrar
  9. Hola Jorge
    Me han parecido muy útiles tus comentarios y te doy las gracias por adelantado.
    Mi pregunta es: ¿Es posible usar "Sheets(1).ScrollArea" para dos o más rangos distintos dentro de una misma hoja? Gracias y un saludo

    ResponderBorrar
  10. Hola Rami

    hasta donde yo conozco, no. Pero la pregunta es interesante.

    ResponderBorrar
  11. Muy Buenos Días
    Sr. Dunkelman, Me parece muy interesante su pagina, apenas tenga cuenta virtual realizare el respectivo aporte Ppor Pay Pal. Quisiera saber si se puede guardar unicamente una hoja de excel, SIN GUARDAR EL LIBRO COMPLETO. Esto con el fin de poder guardar unicamente el formulario sin la base de datos que usted nos enseño a hacer. Muchísimas Gracias
    neo47477@hotmail.com

    ResponderBorrar
  12. Muy Buenos Días
    Sr. Dunkelman, Me parece muy interesante su pagina, apenas tenga cuenta virtual realizare el respectivo aporte Ppor Pay Pal. Quisiera saber si se puede guardar unicamente una hoja de excel, SIN GUARDAR EL LIBRO COMPLETO. Esto con el fin de poder guardar unicamente el formulario sin la base de datos que usted nos enseño a hacer. Muchísimas Gracias
    neo47477@hotmail.com

    ResponderBorrar
  13. Hola
    lo que tienes que hacer es copiar la hoja a un cuaderno nuevo y guardar este (que contendra solamente esa hoja). La mejor forma de hacerlo es abrir el menu contextual de la hoja (con el boton derecho del mouse, apuntando al nombre de la hoja) y eligiendo la opcion Mover/Copiar -- nuevo cuaderno.

    ResponderBorrar
  14. Estimado Jorge:
    Ante todo felicitarle y agradecerle todo el trabajo que ha desarrollado en esta página en la que estoy aprendiendo todo lo que sé de Excel (Hace poco mas de un mes no sabía absolutamente nada).
    Desde hace tiempo en mi empresa queríamos hacer una hoja de cálculo
    para controlar las horas trabajadas, la antiguedad y el sueldo de cada empleado con un montón de variables. Necesitábamos saber si el departamento de nóminas hacía bien su trabajo y por ello me lancé a hacer una plantilla que pudiera tener cada trabajador con sus turnos, antiguedad y demás variables.
    Al terminar de hacer el libro he intentado limitar el area de trabajo de las hojas como indica en su ejemplo y me he dado cuenta de que si cambiamos el nombre a la Hoja1(ANTIGUEDAD) el codigo ya no será Sheets(1).ScrollArea = "B3:E10" sino Sheets("ANTIGUEDAD").ScrollArea = "B3:E10"
    Simplemente quería hacer esta pequeña puntualización además de darle las gracias de nuevo (¡¡¡Ahora soy un héroe entre mis companeros!!!)

    ResponderBorrar
  15. Estimado K...
    me alegro que el blog te sea útil (y desde ya estás invitado a considerar dejar una donación).
    En cuanto a tu observación, el código seguirá trabajando tanto con Sheets(1).ScrollArea como con Sheets("ANTIGUEDAD").ScrollArea
    Excel permite que nos refiramos a la hojas por su nombre ("ANTIGUEDAD") o por su número de índice (Sheets(1)).
    Si te fijas en el editor de Vba, verás que la hoja esta representada como Sheet1(ANTIGUEDAD).

    ResponderBorrar
  16. Siento discrepar (acabo de probarlo).
    quizás tenga que ver con la versión de excel. Yo tengo la 2000

    ResponderBorrar
  17. No tengo instalada la version XL2000, pero hasta donde puedo recordar Vb se refiere a los objetos por índice. Tal vez no entiendo cuál es el punto de discrepancia.

    ResponderBorrar
  18. Buenas... muy interesante usted.. conoce mucho el Excel.. mire es solo una pregunta... tengo un modulo de visual basic en una hoja de excel q requiero abrir pero mi ex-compañero de trabajo (fue despedido y se fue muuyy molesto con los jefes) bloqueo los modulos con contraseña y no me la quiso dar, mi pregunta es esa ¿existe alguna manera de lograr debloquear dicha clave? e probado con programas pero solo me desbloquean la clave q protege la hoja mas no el modulo de visual basic.! le agredeceria la ayuda q me pueda brindar

    ResponderBorrar
  19. Hola Deyvis
    existen muchas utilidades, también gratuitas, para descifrar cintraseñas en módulos de Vba. Todo lo que tienes que hacer es realizar una búsqueda en Google, preferentemente en inglés, con algo así como "vba password".

    ResponderBorrar
  20. Saludos sr. Dunkelman es nuevamente un placer saludarlo y reiterarle que su pagina me parece la mejor para ayudas en excel.
    Realize el metodo que usted sugiere para limitar el area de impresion con scrollarea y me parece fabuloso incluyendole la macro, pero al abrir la hoja de excel que contiene esa limitante si no se activan la macro el cuaderno queda desprotegido. Como se activa la macro inmediatamente se abre la hoja de excel sin autorizacion de quien la esta abriendo...Gracias nuevamente por su ayuda.
    Alfonso Córdoba - Panamá

    ResponderBorrar
  21. Hola Alfonso
    me parece que has puesto el código en el módulo equivocado. El código debe ir en el módulo de ThisWorkbook. Esto hace que cada vez que abres el cuaderno, la macro corre y la hoja queda con el área limitada. En nuestro ejemplo la macro limita el área de la Hoja1. Si quieres que también otras hojas del cuaderno queden limitadas, debes modificar el código.

    ResponderBorrar
  22. Qué tal Sr. Jorge. Primeramente... FELICIDADES Y GRACIAS por compartir sus conocimientos. Mi Caso: Programe una hoja donde los usuarios ingresen datos sólo en las celdas desbloqueadas de cada fila; cada fila esta oculta y aparece sólo cuando se hace click en un commandbutton. Ahora bien, al proteger la hoja, a fin de que no modifiquen las celdas que estan bloqueadas, los commandbutton no funcionan y aparece el error '1004' "No se puede asignar la propiedad Hidden de la clase Range". Cómo hago para proteger el libro y funcionen los commandbutton y muestre las filas ocultas que debe rellenar los usuarios?? Gracias de antemano Sr. Jorge

    ResponderBorrar
  23. Tienes que desproteger temporariamente la hoja.

    ResponderBorrar
  24. Saludos Sr. Jorge. Entiendo que debo desproteger temporariamente la hoja, pero cómo lo hago (para que sea automático) con codigo VBA para que los usuarios no tengan acceso a las celdas bloquedas (protegiendo así las fórmulas internas) y funcionen los commandbutton que van mostrando, a medida que hacen click en ellos, las filas ocultas?? Un millón de Gracias nuevamente por sus respuestas y conocimientos.

    ResponderBorrar
  25. Mi sugerencia es que separes la hoja donde residen los datos, que debe estar protegida, de la hoja donde los usuarios ingresan los datos. Esto te permitiría, por ejemplo, ocultar totalmente la hoja con los datos y fórmulas haciéndola inaccesible al usuario común.

    ResponderBorrar
  26. excelente pagina la suya felicidades,
    mi duda es la siguiente: en la hoja 1 de excel tengo un formulario en donde lleno datos que necesito y en el boton guardar lo que tengo es una pequeña macro que hace que copie los datos del formulario y los pegue en la primera fila de la hoja 3, pero al hacer esto, se nota que excel se sale de la hoja 1 y va a la hoja 3, pega los datos y regresa a la hoja 1, esa es la instruccion, pero lo que quiero es que haga lo mismo pero sin salirse de la hoja 1
    gracias de antemano.

    ResponderBorrar
  27. Que vaya o no la Hoja 3 (o cualquier otra) depende de como se haya escrito el código.
    Puedes usar la orden
    Application.ScreenUpdating=False
    para evitar que Excel renueve la exposición en la pantalla. De este manera el usuario no verá el pasaje a la Hoja3 y el regreso a la Hoja1 (al terminar esa parte de la rutina hay que establecer Application.ScreenUpdating=True).
    Otra forma, más eficiente, es incluir la referencia a la Hoja3 en la rutina para pegar los datos.

    ResponderBorrar
  28. Hola Sr. Dukelman, nuevamente solicito de su vasta experiencia, tengo dos dudas, la primera, ¿como puedo bloquear un boton de comando de un formulario? y la segunda, el text box tiene una propiedad que es multilinea, en el label no existe, ¿como puedo poner un label con multilinea? muchas gracias.

    ResponderBorrar
  29. Para bloqueaar el bottón de comando tienes que poner la porpiedad Enabled como False.
    EN cuanto al label, para crear una nueva línea tiene que pulsar Ctrl junto con Enter.

    ResponderBorrar
  30. Excelente truco!
    Me ha sido de muchisima utilidad.
    Mil gracias por compartir conocimientos tna valiosos

    ResponderBorrar
  31. Francisco Perez19 agosto, 2009 21:08

    Jorge, le agradezco la ayuda a traves de su pagina y le felicito ya que sus consejos son muy claros, escuetos (pero suficientes) y efectivos. Me ha sido muy util su explicacion de la instruccion "Sheets(n)" para Visual Basic ya que la delimitacion "ScrollArea" se conserva aun despues de cerrar el archivo Excel.

    Creo que una adicion util a su explicacion seria, salvo su mejor opinion, recomendar que la instruccion "Sheets" puede repetirse varias veces en la misma subrutina, de manera que no solo la primera, sino varias paginas (o todas) pueden tener un "ScrollArea" delimitado y diferente entre si en un libro, por ejemplo incluir en la subrutina la instruccion Sheets(2)."ScrollArea=A1:Z10" para afectar una segunda hoja de calculo.

    Otra vez muchas gracias y Saludos. Dios le guarde.

    Francisco Perez / Queretaro, Mexico

    ResponderBorrar
  32. Hola Jorge.
    Mi nombre es Paulo y me han sido de mucha ayuda sus consejos.
    Agradecere pueda explicar con mas detalle el tema de como hacer que una planilla se vea como Word, es decir, eliminar las tetras de las columnas y numeros de filas, para que se vea como una presentacion, esto con el fin de que tenga apariencia de ejecutable (un estilo access).

    ResponderBorrar
  33. En Excel Clásico (97-2003) en el menú Herramientas-Opciones tienes la posibilidad de ocultar los encabezamientos y las etiquetas de las hojas. Para ocultar las barras de herramientas puedes aplicar lopción pantalla completa.
    En Excel 2007/10 tienes todas las posibilidades en la cinta, en Vista.

    ResponderBorrar
  34. Saludos!
    ¿Se puede asignar a la propiedad scrollarea un objeto range, osea un nombre que represente un rango?

    Gracias!

    ResponderBorrar
  35. Si. Supongamos que definimos el nombre "myArea" que se refiere al rango A1:C10.
    Ahora podemos usar este codigo para establecer la propiedad ScrollArea de la hoja

    ActiveSheet.ScrollArea = Range("myArea").Address

    ResponderBorrar
  36. Jorge L. Dunkelman, 03 noviembre, 2011 06:38
    Si. Supongamos que definimos el nombre "myArea" que se refiere al rango A1:C10.
    Ahora podemos usar este codigo para establecer la propiedad ScrollArea de la hoja

    ActiveSheet.ScrollArea = Range("myArea").Address

    perfecto, lo que me faltaba era usar ".address" osea que el requiere una cadena y no un objeto range. Muchas Gracias Dr. Dunkelman.

    Jpinilla

    ResponderBorrar
  37. buenas tardes Jorge
    tengo una duda, me podrias decir si en excel 97-2003 es posible delimitar los brincos de celda, por ejemplo que automaticamente se brinque de la celda b2 a la e2 con la tecla tab, lo que pasa es que estoy utilizando una lectora de codigos de barras,y tengo un formato en excel donde quiero que se guarden la informacion escaneada con la pistola, pero de las 8 celdas solo ocupo 3 excelente log por cierto

    ResponderBorrar
  38. Hay varias formas de hacerlo. La más sencilla es proteger la hoja quitando la opción "seleccionar celdas bloqueadas" y dejando las celdas B2 y E2 sin protección. Esto hace que sólo estas pueden ser seleccionadas.

    ResponderBorrar
  39. Buenas don Jorge

    Realicé la operación tal como lo indica, solo una pregunta cuando abro el archivo se solicita habilitar macros sino se habilita permite andar por toda la hoja, es decir la instrucción no se aplica, como hago para que no salga esa leyenda de habilitar macros.

    ResponderBorrar
  40. Le agradecere mucho su ayuda, en la empresa tengo un formato pero cada quien me mueve columnas y filas a su antojo, como le hago para que no modifiquen columnas y filas, solo datos.

    ResponderBorrar
  41. Usando al opción de proteger la estructura de la hoja (Revisar-Proteger...)

    ResponderBorrar
  42. Buenas don jorge

    de antemano agradecerle por su trabajo y tiempo, quisiera saber si hay alguna manera de bloquear el scrollarea al que accede el usuarios por "programador"-propiedades, desde hay lo puede quitar facilmente, incluso si tengo protegida la hoja, puedo poner contraseña al codigo vb pero no a esta propiedad de la pestaña programador, muchas gracias

    ResponderBorrar
  43. La solución es poner contraseña al proyecto de VB. En el editor de Vb, Tools-Vb Project Properties-Protection.
    Esto impide que el usuario pueda acceder a las propiedades de la hoja en el editor de Vb.

    ResponderBorrar
  44. hola,
    tengo un problema con la macro porque me limita la base de datos solo me guarda hasta 96 filas no se como ampliarla espero tu ayuda gracias

    ResponderBorrar
  45. La macro no limita la base de datos sino el área de trabajo. Para ampliarla sólo hay que modificar el rango en la macro.
    Excel siempre guarda todo el cuaderno sin relaciión al tamaño del área de trabajo de las hojas.

    ResponderBorrar
  46. GRACIAS POR EL APORTE....ESTA MUY BUENO...

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.