Excel: eliminar 1000 cuadros de texto
Los cuadros de texto en Excel
El otro día, programando con vba y Excel 2007 me ocurrió una cosa muy curiosa: varias celdas de una hoja contenían un montón de Cuadros de Texto. Cuando digo un montón quiero decir cerca de 1000 cuadros de texto. ¿Cómo aparecieron? Pues realmente no lo sé y los descubrí de casualidad ya que cuando pasas sobre ellos el cursor del ratón cambia de forma. Son todos cuadros de texto vacios, uno encima de otro y de pequeño tamaño. Excel 2007, y he buscado concienzudamente por todos los menús, no tiene una forma de saber cuántos cuadros de texto existen en una hoja ni de cómo eliminarlos todos. Veremos una forma muy sencilla de hacerlo.
La manera que indica Microsoft de eliminar cuadros de texto en Excel es de forma individual ir pinchando en un extremo del cuadro y pulsar la tecla Supr. Esto para un cuadro de texto o para diez es una forma razonable de hacerlo, pero para ¡eliminar 1000! no lo es...además que tampoco sabemos su localización exacta. Vamos a usar un poco las matemáticas y me voy a marcar un reto. Voy a imaginar (optimistamente) que soy capaz de eliminar un cuadro de texto en cinco segundos. Entonces:
- Con 1000 cuadros de texto tardaré 5000 segundos ? 1hora 23 minutos aproximadamente.
El reto que me voy a plantear es hacerlo de otro modo usando programación y macros en la mitad de ese tiempo. Para esto tengo que hacer algo sencillo.
Formas - Shape
Excel, como sabrás, dispone de una gran cantidad de lo que denomina formas. Puedes crear desde formas básicas (círculos, retángulos etc...) hasta combinaciones elaboradas para la creación de formas complejas. Dentro de estas formas se encuentran nuestros cuadros de texto. Generalmente, los cuadros de texto se añaden desde la pestaña Insertar -> Cuadros de texto. Cuando creas un cuadro de texto y lo seleccionas, puedes ver su nombre en el cuadro de nombres, en mi caso uno de los nombres es 854 CuadroTexto por eso dije al principio que tenía cerca de 1000 cuadros de texto.
Si traducimos al inglés la palabra formas encontraremos la palabra clave que necesitamos para programar: Shape
Creando la macro
Preparando las variables
No hay tiempo que perder y tiene que ser algo sencillo. Voy a crear una macro vacia: pestaña programador -> Grabar macro, le pongo de nombre Listar_formas y pulso aceptar. Finalmente detener grabación. Para acceder rápidamente a su código me voy a Ver macros (Alt+F8), selecciono la que acabo de crear y pulso modificar. Ya tengo el inicio del código:
Sub Listar_formas() End Sub
Voy a trabajar con la hoja activa:
ActiveSheet
Voy a escribir en las celdas de la columna A el nombre de todas las formas existentes. Usaré una variable ? contador:
ActiveSheet.Range("A" & contador).Value
Voy a definir el objeto Shape (forma):
Dim forma As Shape
Macro para listar todas las formas
Y voy a buscar todas las formas existentes. El código final para listar todas las formas es el siguiente:
Sub Listar_formas() Dim forma As Shape Dim contador As Integer contador = 0 For Each forma In ActiveSheet.Shapes contador = contador + 1 ActiveSheet.Range("A" & contador).Value = forma.Name & " - " & forma.Type Next forma End Sub
Macro para eliminar cuadros de texto
Pues ya está todo. Cierro de nuevo el archivo Excel sin guardar nada, lo abro, creo una macro y añado el siguiente código:
Sub Eliminar_formas() Dim forma As Shape Dim contador As Integer contador = 0 For Each forma In ActiveSheet.Shapes If forma.Type = msoTextBox Then contador = contador + 1 forma.Delete End If Next forma MsgBox "Se han eliminado: " & contador & " cuadros de texto" End Sub
La ejecuto, hace su trabajo perfectamente en milésimas de segundo, no se deja ninguno y no he llegado a tardar ni la tercera parte de tiempo que haciéndolo a mano como pensé en un primer momento:
Eliminar en todas las hojas
El usuario ignacio, nos dejó el siguiente comentario:
El código está fenomenal, aunque tengo una duda. ¿cómo se puede hacer para que lo haga en todas las pestañas del libro?
Voy a intentar solucionar su duda, para esto y basándome en lo que acabamos de ver, creo la siguiente macro:
Sub Eliminar_formas() Dim forma As Shape Dim contador As Long Dim hoja As Worksheet contador = 0 For Each hoja In Worksheets For Each forma In hoja.Shapes If forma.Type = msoTextBox Then contador = contador + 1 forma.Delete End If Next forma Next hoja MsgBox "Se han eliminado: " & contador & " cuadros de texto" End Sub
Como puedes observar, en este último código he puesto la variable contador como Long. ¿Por qué? pues porque el usuario Damian nos dejó un mensaje en el que decía que el código le había generado un ¡desbordamiento! Una variable definida como Integer, si observas la ayuda de Excel, verás que se almacena como número de 16 bits (2 bytes) con valores que van de -32.768 a 32.767. Esto significa que posiblemente el usuario Damian tenía en su archivo Excel ¡más de 32767 cuadros de texto! ¡impresionante! Le tendría que ir lentísimo.
gracias
Mi duda es cómo es que se llegan a insertar tal numero de formas en el documento.
Gracias por tu comentario.
Un saludo.
HACER CTRL + I ( IR )
ESPECIAL
BUSCAR OBJETOS
DELETE Y LOS BORRA A TODOS
La macro no funcionó, aunque creo que ha sido porque lo que tenía era una 'forma' no un 'cuadro de texto'. Antes de cambiar la macro propbé esta otra solución y fué perfecta. Gracias a todos.
Un saludo
GRACIAS
Tenia exactamente el mismo problema, pero esta solucion y otras que hay en la red no me funcionaban ya que el excel dejaba de responder porque tenia ya una cantidad ridicula de cuadros de texto, entonces Excel no daba abasto para cumplir con la solución y por lo tanto dejaba de funcionar. Pero finalmente encontré en otro foro esta solución y vaya que es super rapida y eficaz. Espero les sirva.
"I had an excel workbook with 5 tabs and on each tab 30000 textboxes or so...
I renamed the xlsx to zip, then removed the drawings xml files.
renamed back to xlsx, open, then excel will tell you that there is something wrong with the file. Press repair and then all the textboxes are gone."
Muchas gracias
se requiere una macro para tomar los datos de los cuadros de texto encontrados en una hoja com lo hago?, gracias
Usa el código Sub Eliminar formas (macro para eliminar cuadros de texto) y en vez de eliminar los cuadros de texto (forma.Delete) escribe forma pon un punto detrás (forma.) y te saldrán todas las opciones existentes. Ahí podrás modificar los cuadros de texto
Solo tienes que ejecutar la última macro de todas, la que pone Eliminar_formas pero la que está al final del artículo, en el título: "Eliminar en todas las hojas."
A base de copiar celdas arrastrando hacia abajo iba copiando cada vez más cuadros de texto desde la primera celda que sólo tenía uno....pero a base de ir arrastrando al final tenía unos 3.000 y pico....ahora va la hoja mucho más fluída!!....... Gracias!...Buen finde!!
Iba un poco lento.
Me salvaste. Me había pasado antes y ya no recordaba la macro. Tenía 9356 cuadros!!
Mil gracias!
Según tu comentario parece que tienes muchas imágenes.
Para eliminarlas puedes sustituir en el código esto:
Código:
If forma.Type = msoTextBox ThenPor esto:
Código:
If forma.Type = msoPicture ThenPero recuerda: con este código borrarás TODAS las imágenes de la hoja de excel y no solo las de las banderas.
No sé si es lo que quieres. Después puedes volver al código original y borrar los cuadros de texto.
checo mi hoja y ahi estan los dichosos cuadros, ¡que puedo hacer?
mi archivo esta muy lento.
Gracias por su apoyo
Puedes modificar el código y el bucle dejarlo así:
Código:
For Each forma In ActiveSheet.Shapes
If forma.Type = msoTextBox Then
contador = contador + 1
forma.Delete
If contador = 5000 then Exit For
End If
Next forma
Un saludo Eli.
checo mi hoja y ahi estan los dichosos cuadros, ¡que puedo hacer?
mi archivo esta muy lento.
Gracias por su apoyo
Madre mía, 107200 cuadros de texto...
Pienso que igual tienes muchos cuadros de texto por hoja.
Intenta usar el código "Eliminar formas" para una sola hoja y no el último para todas. A ver si tienes suerte.
y ahi siguen, ¡alguna opcion?
Gracias por responder.
Muchisimas gracias, estaba ya desesperada...
Saludos!
lo probe en office 2010 y anduvo barbaro.
no se nada de macros ni de programacion, pero nada y lo pude hacer!
genial! gracias!!!
La gran incognita es DE DONDE SALEN? alguien tiene esa informacion?
Si te aparece la leyenda "la referencia no es válida" verifica qué tipo de control has creado o haces referencia.
El único Excel que no he probado con este código es la versión 2003. Si usas esta versión prueba a cambiar esto:
Código:
...If forma.Type = msoTextBox And forma.TextEffect.Text = "" Then
...
por
Código:
...If forma.Type = msoTextBox And forma.TextFrame.Characters.Text = "" Then
...
Saludos!!!
forma.Delete
End If , pero aun asi me sigue borrando todos los cuadros de texto sin importar que esten llenos o vacios. Espero me puedas ayudar.
Saludos.
Código:
Dim Valor As BooleanValor = IsNull("")
Verás que Valor te devuelve False. En vez de usar esa condición, utiliza esta:
Código:
If forma.TextEffect.Text = "" Then...Un saludo.
Hola, antes que nada gracias por responder
Saludos y gracias por tu tiempo.
Código:
...
If forma.Type = msoTextBox And forma.TextEffect.Text = "" Then
contador = contador + 1
forma.Delete
End If
...
Si en el código escribes forma.T
¿No sale TextEffect?
Y si escribes forma.TextEffect.T
¿No sale Text?
Esto tal vez sea posible porque estés usando una versión de Excel 2007 desactualizada. ¿Tienes los Service Pack instalados? ¿Qué versión de Excel estás usando?
Hola de nuevo!, he modificado el codico a como me indicaste y ahora me arroja un error dirente: "el valor especificado se encuentra fuera de los limites" (añado imagen). Respecto a la versión de excel que estoy utilizando es la 2013 de 64bits actualizado y si, cuando escribo forma. me sale la opción "TextEffect" y posteriormente la de "Text".
Un Saludo y Muchas Gracias!
Desbordar la variable long (contador) es prácticamente imposible.
Tienes que depurar el código y ver en qué línea está el error.
De todos modos, para Excel 2003, podrías probar lo siguiente. Sustituye esto:
Código:
...
If forma.Type = msoTextBox And forma.TextEffect.Text = "" Then
...
Por esto otro:
Código:
...
If forma.Type = msoTextBox And forma.TextFrame.Characters.Text = "" Then
...
¡Suerte!
Si es un rectángulo lo que quieres borrar, debes modificar en el código la línea:
Código:
If forma.Type = msoTextBox Theny cambiar la palabra msoTextBox por otro tipo de forma. Todas las formas de Excel las tienes en este enlace.
msdn.microsoft.com/en-us/library/aa432678(v=office.12).aspx
No sé ahora cuál corresponde a un rectángulo, prueba msoAutoShape como primera opción a ver...
|)`
| |
| |_____
/ (]__)
/ (]___)
/ (]___)
___(]_)
/
Es muy extraño lo que comentas porque yo lo acabo de probar en Excel 2007 y Excel 2013 y funciona perfecto.
¿Has puesto bien el código?
Quieres decir que ¿Tienes 5000 rectángulos?
Eso sí que es raro. Pero... ¿lo que quieres hacer es borrar rectángulos? Voy a echar un vistazo al código...
Código:
If forma.Type = msoTextBox Theny cambiar la palabra msoTextBox por otro tipo de forma. Todas las formas de Excel las tienes en este enlace.
msdn.microsoft.com/en-us/library/aa432678(v=office.12).aspx
No sé ahora cuál corresponde a un rectángulo, prueba msoAutoShape como primera opción a ver...
Suerte y gracias por tus comentarios.
Sub Eliminar_formas()
...
End Sub
Es posible que vosotros tengáis algún código VBA que copie y pegue celdas. Pero ya has visto que tiene solución.
Gracias por tu comentario. Buen día.
Un saludo
Haz lo siguiente y me cuentas: cambia en el código esto:
Código:
Dim contador As Integerpor esto:
Código:
Dim contador As LongEsto ya ha sido corregido en el artículo. Gracias Damian.
Un saludo
Saludos!
Gracias
Un saludo
Ciertamente una cosa que sigo sin entender es por qué con el tiempo y uso aparecen tantos cuadros de texto de forma involuntaria.
Un saludo.
Un saludo