Rellenar ListBox con datos de varias hojas.
~ jose ~
Como puedo rellenar un ListBox1 con los datos de todas las hojas de un
libro menos de la primera?.
Para rellenar los datos de una hoja empleo estas celdas:
("c6:e" & Range("b6").End(xlDown) + 5)
Venga, gracias y un saludo,
jose
Ivan
se me ocurren al menos estas dos formas: (la 1ª creo que no iria mal si no son demasiadas filas en cada hoja, la 2ª
creo que es mas rapida, aunque te crea una hoja que luego elimina)
no estan probadas, pero tras revisarlas, creo que deberian funcionar siempre y cuando todas las hojas tengan mas de un
dato en el rangoy tengas mas de una hoja
'''********** puedes ponerlos en un modulo normal o en el de el formulario **********
'
' si no son demasiados datos podrias usar algo asi:
'
Sub CargarDeVariasHj_1(ByRef ListBx As MSForms.ListBox)
Dim n As Integer, f As Long, uf As Long
ListBx.ColumnCount = 3
Application.ScreenUpdating = False
With ThisWorkbook
For n = 2 To .Worksheets.Count
With .Worksheets(n)
uf = Range("b6").End(xlDown).Row + 5
For f = 6 To uf
ListBx.AddItem .Range("b" & f).Value
ListBx.List(ListBx.ListCount - 1, 1) = .Range("c" & f).Value
ListBx.List(ListBx.ListCount - 1, 2) = .Range("d" & f).Value
Next
End With
Next
End With
End Sub
'
' si son muchos registros quizas te venga mejor esta
'
Sub CargarDeVariasHj_2(ByRef ListBx As MSForms.ListBox)
Dim n As Integer, f As Long, uf As Long
ListBx.ColumnCount = 3
Application.ScreenUpdating = False
With ThisWorkbook
.Worksheets.Add After:=.Worksheets(1)
For n = 3 To .Worksheets.Count
With .Worksheets(n)
.Range("c6:e" & .Range("b6").End(xlDown).Row + 5).Copy _
ActiveSheet.[a65536].End(xlUp).Offset(1)
End With
Next
ListBx.List = Range("a2:c" & [a65536].End(xlUp).Row).Value
Application.DisplayAlerts = False
.Worksheets(2).Delete
Application.DisplayAlerts = True
End With
End Sub
'
'*************************************
espero te ayuden
un saludo
Ivan
"~ jose ~" <carpi...@gmail.com> escribió en el mensaje news:1190475472....@y42g2000hsy.googlegroups.com...
Ivan
aunque creo que es obvio, se me ha pasado comentar que tendrias que
llamar a la macro cuando quieras cargar el listbox, pasandole como
argumento el propio listbox (ListBox1, ListBox23, MiListbox, etc..)
un saludo
Ivan
~ jose ~
las propuesta que expones no me sirven, cada hoja contiene 65530 filas
y puede haber más hojas iguales. El código para ir creando hojas según
la necesidad ya lo he creado, ahora lo que quiero es poder mostrar
todas las hojas (menos la primera del libro) en un listbox llamado
ListBox1 cada vez qeu inicie el formulario.
Un saludo,
jose
Ivan
Ivan
> ¿y por que no te sirven?
Disculpa, ya me he dado cuenta en cuanto a la 2ª propuesta,
pero la 1ª propuesta, aunque no se lo que tardara, valer si vale
(excepto si el listbox no admite tantas filas
de todas formas voy a probarlo un poco
un saludo
Ivan
~ jose ~
tardaría muchiiiisimo ya que añade el contenido de uno en uno y si
tenemos en cuenta que son varias hojas y todas menos la última que no
está completa serían 65530 filas por 3 columnas y por el nº de hojas,
uffff, mucho tiempo.
El segúndo ejemplo me viene pero, no puedo copiar el contenido de una
hoja "llena" y pegarla en otra nueva y volver a copiar el contenido de
otra hoja "llena" y volverla a pegar en la hoja donde pegué la
anterior, lógico verdad?
Alguna sugerencia Ivan?
~ jose ~
Esto que expongo no funciona pero por dar una idea...
With ThisWorkbook
For n = 2 To .Worksheets.Count 'Empieza el for a partir de la hoja
2
With .Worksheets(n)
Resultado = .Range("c6:e" & .Range("b6").End(xlDown).Row +
5).Value
End With
Next
ListBox1.List = Resultado
End With
Habria que hacer "algo" que el 'Resultado' se vaya acumulando y en la
última linea cargar todos los 'Resultados' de todas las hojas, me
explico?
Y si quizás hay que emplear RowSource?
Alguna idea?
Un saludo
jose
> > ¿y por que no te sirven?- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -
julian-vlc-sp
¡Importante!: Colabora con el grupo.Contesta a este mensaje
y dinos si te sirvió o no la respuesta dada. Muchas gracias.
===========================================
He de confesar que me he perdido en el hilo y realmente no se lo que
necesitas.
De la lectura del mensaje al que contesto, cuando dices:
, ahora lo que quiero es poder mostrar
todas las hojas (menos la primera del libro) en un listbox llamado
ListBox1 cada vez que inicie el formulario.
Entiendo que necesitas un listbox1 donde seleccionar una de las hojas de tu
libro.
Yo tengo algo parecido a ver si te sirve:
Si he entendido mal te ruego me disculpes, y aun así posteo mi propuesta por
si te sirve a ti o a algún otro.
A la columna A que la tengo oculta le he dado como nombre HojasDelLibro, en
esta columna tendremos los nombres de las hojas del libro ordenadas
alfabéticamente.
En lugar de un listbox, a la celda E10 le he aplicado lo de validación de
datos diciéndole que permito una lista cuyo origen es:
=HojasDelLibro
Ahora lo que nos falta es rellenar el rango HojasDelLibro y ordenarlo
alfabéticamente.
El procedimiento que rellena y ordena el rango también borra el contenido de
la celda E10 por si en ella tengo seleccionada alguna hoja que he borrado.
En mi libro, para agregar, eliminar o renombrar las hojas lo hago mediante
botones y por tanto mediante código, en cada uno de esos procesos después de
agregar, eliminar, o renombrar las hojas deseadas lanzo un procedimiento que
tengo en un modulo, que se llama:
LeerLasHojas()
y que pego a continuación.
Sub LeerLasHojas()
Dim hoja As Worksheet
Dim i As Long
Sheets("hoja1").Select
Range("hojasdellibro").Select
Selection.ClearContents
For Each hoja In Worksheets
If hoja.Name <> "Esta Hoja no" And hoja.Name <> "Esta hoja tampoco"
Then
i = i + 1
ActiveSheet.Range("a" & Trim(Str(i))).Value = hoja.Name
End If
Next
Range("E10").Select
Selection.ClearContents
Call OrdenarElRango
End Sub
Sub OrdenarElRango()
Range("hojasdellibro").Select
Selection.Sort Key1:=Range("hojasdellibro"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
SALUDOS.
Julián-Valencia-España
http://ijulian.iespana.es/
"~ jose ~" <carpi...@gmail.com> escribió en el mensaje
news:1190485596....@d55g2000hsg.googlegroups.com...
julian-vlc-sp
¡Importante!: Colabora con el grupo.Contesta a este mensaje
y dinos si te sirvió o no la respuesta dada. Muchas gracias.
===========================================
He encontrado una versión un poco mejorada.
Para poder tener tu rango donde quieras (y no necesariamente en toda la
columna A) usa el código que te pongo a continuación:
Sub LeerLasHojas()
Dim hoja As Worksheet
Dim i As Long
i = 0
Sheets("hoja1").Select
Range("hojasdellibro").Select
Selection.ClearContents
For Each hoja In Worksheets
If hoja.Name <> "Esta Hoja no" And hoja.Name <> "Esta hoja tampoco"
Then
i = i + 1
ActiveSheet.Range("hojasdellibro").Activate
ActiveCell.Offset(i, 0).Value = hoja.Name
End If
Next
Range("E10").Select
Selection.ClearContents
Call OrdenarElRango
End Sub
Sub OrdenarElRango()
Range("hojasdellibro").Select
Selection.Sort Key1:=Range("hojasdellibro"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
SALUDOS.
Julián-Valencia-España
http://ijulian.iespana.es/
"julian-vlc-sp" <ijulianE...@iespana.es> escribió en el mensaje
news:%231qMvPV$HHA....@TK2MSFTNGP05.phx.gbl...
Ivan
> serían 65530 filas por 3 columnas y por el nº de hojas,
> > uffff, mucho tiempo.
> > Alguna sugerencia Ivan?
pues no se lo que quieres hacer, pero cargar cientos de miles de
registros en un listbox,..... uffff, .... pobre usuario cuando tenga
que buscar algo
yo intentaria condicionar/reducir la carga en base a algun criterio y
renovar el list segun condiciones (pero supongo que tus razones
tendras)
> With ThisWorkbook
> For n = 2 To .Worksheets.Count 'Empieza el for a partir de la hoja> 2
> With .Worksheets(n)
> Resultado = .Range("c6:e" & .Range("b6").End(xlDown).Row +
> 5).Value
> End With
> Next
> ListBox1.List = Resultado
> End With
> Habria que hacer "algo" que el 'Resultado' se vaya acumulando y en la
> última linea cargar todos los 'Resultados' de todas las hojas, me
> explico?
el problema, al menos segun mis pruebas, es que parece que no se puede
redimensionar una matriz creada a partir de un rango, al menos para
añadirle otro rango, (supongo que se podran ir añadiendo elementos
individuales)
supongo que sera porque solo se puede redimensionar la ultima
dimension de una matriz, y segun mi experiencia (y aunque mis
conclusiones pueden ser erroneas) excel asigna la 1ª dimension a las
filas y la 2º a las columnas, y al ir incrementando (redimensionando)
la matriz lo que estarias intentando (si es que no se genera
automaticamente un error) es aumentar la !º dimension
esto mismo me imagino que se podra aplicar para la propiedad list del
listbox
> Y si quizás hay que emplear RowSource?
segun mis pruebas, yo al menos no lo he conseguido (incluso si no
recuerdo mal de algunas pruebas antiguas, ni siquiera admite rangos
discontinuos de una misma hoja, aunque quizas este equivocado)
> Alguna idea?
quizas se pueda transponer el rango en el 'eter' y usar la propiedad
column del list (aunque parezca una boutade, algo de eso me esta
rondando la cabeza)
quizas rellenar una matriz o coleccion de la form habitual, pero creo
que el problema del tiempo seguiria ahi. Quizas un objeto dictionary
pero no tengo ni idea de ellos
otra posibilidad al menos creo que a mi alcance es cargar solo los
datos de una hoja y si el usuario desea seleccioar otro mas alto (no
se si tienes algun mecanismo mas de seleccion o filtrado a disposicion
del usuario [si no es asi ,,,,Ufffff...]) o el 'campo de vision del
list' llega al final/principio automaticamente recargar el list con el
contenido de la siguiente/anterior hoja
supongo que habra soluciones mucho mas eficaces, pero me temo que no
en mi mano
un saludo
Ivan
Ivan
a la espera de algun experto, otra posibilidad, parecida a la anterior
y si no un poco mas chapucera, si al menos un poco mas *cara* (aunque
posiblemente mas rapida al menos durante la ejecucion posterior a la
carga) seria tener tantos listbox identicos [y en la misma posicion]
como hojas haya crgados con los registros de cada una y jugar con el
orden z
como digo, una chapuza, pero si es imprescindible ....
un saludo
Ivan
~ jose ~
Voy a hacer un pequeño resumen del problema y conclusión de este hilo.
Tengo un libro excel que está compuesto por la primera hoja con texto
y unas instrucciones para el usuario final, la segunda hoja con datos
desde la celda c5 hasta la e65536. La tercera, con datos desde la c5
hasta e60000 ~. Tengo un formulario desde donde se escriben los datos
para agragarlos a la hoja, si una hoja se llena (como fué el ejemplo
de la hoja2), entonces una macro automáticamente crea una hoja nueva e
inserta ahí el contenido de los textbox del formulario.
El formulario lo que contiene a parte de los textbox y algun label es
un ListBox que lo quería utilizar para mostrar el contenido de todos
los datos de todas las hojas (menos la primera) juntos. La verdad,
este ListBox no hace nada más que mostrar los datos para que el
usuario vea en algún sitio todos los registros ya que en este libro
SÓLO se trabaja desde el formulario, por eso si esto que pido no se
puede realmente no pasa mucho, ya haré un ListBox que sólo muestre las
coincidencias de la búsqueda de datos.
De todas formas gracias por intentarlo a los dos vale?
Ya se que "siempre" que pregunto en el foro pregunto cosas "raras",
pero bueno, que le vamos a hacer!!!
Un saludo,
jose
Héctor Miguel
> ... resumen del problema y conclusion de este hilo.
> Tengo un libro... compuesto por la primera hoja con texto y unas instrucciones para el usuario final
> la segunda hoja con datos desde la celda c5 hasta la e65536. La tercera, con datos desde la c5 hasta e60000 ~.
> Tengo un formulario desde donde se escriben los datos para agragarlos a la hoja
> si una hoja se llena (como... la hoja2), entonces una macro... crea una hoja nueva e inserta... el contenido de los textbox
> El formulario... contiene... un ListBox que lo queria... para mostrar... todos los datos de todas las hojas (menos la primera) juntos.
> La verdad, este ListBox no hace nada mas que mostrar los datos para que el usuario vea en algun sitio todos los registros
> ya que en este libro SOLO se trabaja desde el formulario, por eso si esto que pido no se puede realmente no pasa mucho
> ya hare un ListBox que solo muestre las coincidencias de la busqueda de datos...
> Ya se que "siempre" que pregunto en el foro pregunto cosas "raras", pero bueno, que le vamos a hacer!!!
[*casi siempre*]... hay *formas* de resolver [casi] todo tipo de *problematicas* [solo que *a veces* sale mas caro *el caldo*] :))
el siguiente ejemplo hace una copia de las hojas 2 a 'n' de tu libro, elimina las 5 primeras filas y dos primeras columnas [segun tu ejemplo]
despues cada libro/hoja se guarda como archivo de texto [*.CSV] en dos *pasos*:
- el primer paso es un CSV *temporal*, y el segundo va *acumulando* los temporales a un CSV [obviamente *acumulativo*] :D
lo del *caldo caro* es porque las pruebas las realice [por demas obvio que] con muy pocas lineas de texto en cada hoja
[no quisiera *verme* acumulando un archivo de ~65000 registros por 'n' hojas ocupadas] :-((
ademas, tampoco me gustaria ser un usuario *consultando* un control de lista con ~200k registros [asumiendo 3 hojas solamente]
OJO: requiere establecer en el proyecto de macros una referencia [menu: herramientas / referencias...]
-> a la libreria: Microsoft ActiveX Data Objects x.0 Library [el x.0 corresponde a la version mas baja de los equipos donde correra]
en las pruebas utilice un UserForm1 con un ListBox1 y el siguiente codigo en el evento '_initialize'
Private Sub UserForm_Initialize()
Acumula_Registros
Carga_Lista
End Sub
y las dos macros que hacen el trabajo son las siguientes [estas en un modulo de codigo estandar/general/normal/...]
-> modifica la constante *Ruta* como corresponda a tus necesidades
corre algunas pruebas [te sugiero empezar con POCOS *elementos/filas/hojas/...* para analizar el *rendimiento*]
si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.
Public Const Ruta As String = "c:\documents and settings\<usuario>\mis documentos"
Public Const Archivo As String = "lista.csv"
Dim LIsta As String, Temp As String
Sub Acumula_Registros(): Application.ScreenUpdating = False
Dim n As Byte, Acumula As Integer, Registra As Integer, Agrega As String
LIsta = Ruta & "\" & Archivo: Temp = Ruta & "\" & "temp.txt"
If Dir(LIsta) <> "" Then Kill LIsta
Acumula = FreeFile: Open LIsta For Append As #Acumula
With ThisWorkbook
For n = 2 To .Worksheets.Count
If Dir(Temp) <> "" Then Kill Temp
.Worksheets(n).Copy
With ActiveWorkbook
With .Worksheets(1)
.Rows("1:5").Delete: .Columns("a:b").Delete: End With
.SaveAs Temp, xlCSV: .Close False: End With
Registra = FreeFile: Open Temp For Input As #Registra
Agrega = Input(LOF(Registra) - 2, #Registra)
Close #Registra: Print #Acumula, Agrega
Next: Close #Acumula
End With
End Sub
Sub Carga_Lista()
Dim Conexion As ADODB.Connection, _
Registros As ADODB.Recordset, Consulta As String
Consulta = "select * from " & Archivo
Set Conexion = New ADODB.Connection
Conexion.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & Ruta & ";Extensions=asc,csv,tab,txt;"
Set Registros = New ADODB.Recordset
Registros.Open Consulta, Conexion, adOpenForwardOnly, adLockReadOnly, adCmdText
With UserForm1.ListBox1
.ColumnCount = Registros.Fields.Count
.Column = Registros.GetRows: End With
Registros.Close: Set Registros = Nothing
Conexion.Close: Set Conexion = Nothing
If Dir(Temp) <> "" Then Kill Temp
If Dir(LIsta) <> "" Then Kill LIsta
End Sub
~ jose ~
es curioso, he estado probando lo que propones pero no funciona bien.
Lo estoy ejecutando con F8 y veo que el archivo CSV se crea
correctamente, con los datos de las hojas 2 y 3 (por hacer pruebas),
pero en la macro 'Carga_Lista' es donde está el fallo, en el listbox1
me muestra el contenido de la hoja 2 EXCEPTO la última fila con datos
que de la hoja 2 es la 65536 y la primera fila que sería la 6, el
contenido de la hoja 3 que son sólo 3 filas no me muestra ninguna.
Otra pega más es que me muestra el listbox en una columna así que los
datos están separados por comas, tendría que aparecer 3 columnas,
además aparecen de una forma muy curiosa, por ejemplo en la primera
columna tengo 123 en la segunda 212 y la tercera 456 el resultado en
el lisbox es 123,2124 raro no?
Bueno, pues aquí dejo esto vale?
Venga! un saludo y gracias a todos
jose
_propuesta de Hector_
Héctor Miguel
> es curioso, he estado probando lo que propones pero no funciona bien.
> Lo estoy ejecutando con F8 y veo que el archivo CSV se crea correctamente, con los datos de las hojas 2 y 3 (por hacer pruebas)
> pero en la macro 'Carga_Lista' es donde esta el fallo, en el listbox1 me muestra el contenido de la hoja 2
> EXCEPTO la ultima fila con datos que de la hoja 2 es la 65536 y la primera fila que seria la 6
> el contenido de la hoja 3 que son solo 3 filas no me muestra ninguna.
> Otra pega mas es que me muestra el listbox en una columna asi que los datos estan separados por comas
> tendria que aparecer 3 columnas, ademas aparecen de una forma muy curiosa
> por ejemplo en la primera columna tengo 123 en la segunda 212 y la tercera 456 el resultado en el lisbox es 123,2124 raro no?
> Bueno, pues aqui dejo esto vale?...
1) el que no te muestre la fila 6 [primera del archivo .CSV que se genera correctamente segun confirmas, y hasta donde se]...
se debe a que las maquinarias de consulta a bases de datos *asumen* [por omision] que el primer registro son los *titulos*
y para poder *administrar* este parametro necesitas *cambiar de proveedor* modificando las siguientes dos partes de la macro:
a) la linea que establece la conexion:
-> de:
Conexion.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & Ruta & ";Extensions=asc,csv,tab,txt;"
-> a:
Conexion.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Ruta & _
"\;Extended Properties=""text;HDR=No;FMT=Delimited"""
b) la linea que *abre* [o hace uso de la conexion para abrir] el *RecordSet*
-> de:
Registros.Open Consulta, Conexion, adOpenForwardOnly, adLockReadOnly, adCmdText
-> a:
Registros.Open Consulta, Conexion, adOpenStatic, adLockReadOnly, adCmdText
2) el resto de las *fallas* que comentas:
- la omision de la *utima* fila de la primera hoja -65536-
- la omision de los registros/filas de todas las hojas siguientes
- que todos los registros se muestran en *una sola columna* -y separados por comas-
-> *me hace suponer* que [probablemente]...
o... en tu configuracion regional el separador de listas y argumentos NO es la coma
o... tendrias que modificar la EXTenxsion del archivo de .CSV a .TXT
o... tendrias que modificar la linea con la instruccion que hace la carga del RecordSet al ListBox [p.e.]
de: -> .Column = Registros.GetRows
a: -> .List = Registros.GetRows
3) es probable que necesites hacer pruebas *combinando* las posbilidades anteriores y comentar los resultados -?-
esto, porque he probado con una segunda hoja llena hasta la fila 65536 y otra con solo 10 filas y... *sin problemas* ;)
o [si lo prefieres] puedes consultar temas relacionados con el uso da ADO y la obtencion de datos externos [p.e.] desde:
Como abrir archivos de texto delimitado que utilizan el texto IIsam del proveedor Jet
http://support.microsoft.com/kb/q262537/
-> Microsoft Active Data Objects y Microsoft ADO Ext.2.x for DDL and Security
otras fuentes de informacion:
-> http://tinyurl.com/2ejcmp [http://preview.tinyurl.com/2ejcmp]
-> Carl Prothman
ODBC Driver for TextFiles
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForText
OLEDB Provider for Microsofr Jet TextFiles
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJetText
Ivan
> SÓLO se trabaja desde el formulario, por eso si esto que pido no se
> puede realmente no pasa mucho, ya haré un ListBox que sólo muestre las
> coincidencias de la búsqueda de datos.
aparte de la propuesta de Hetor, y aunque a lo mejor es una salida de
'pata de banco' (y ademas no tengo ni idea sobre el tema)
¿y un Spreadsheet no podria hacer el apaño?
como digo no tengo ni idea de como se carga, pero tengo entendido que
acepta muchas mas filas
bueno solo como idea
un saludo
Ivan
Héctor Miguel
>> SOLO se trabaja desde el formulario, por eso si esto que pido no se puede realmente no pasa mucho
>> ya hare un ListBox que solo muestre las coincidencias de la busqueda de datos.
>
> aparte de la propuesta de Hector, y aunque a lo mejor es una salida de 'pata de banco'
> (y ademas no tengo ni idea sobre el tema)... un Spreadsheet no podria hacer el apa#o?
> como digo no tengo ni idea de como se carga, pero tengo entendido que acepta muchas mas filas...
[bueno... como suelo decir]... tal parece que todo tiene sus *pros* y sus *contras* :-((
algunos comentarios en seguida y... si cualquier duda [o informacion adicional]... comenta/s/n ?
saludos,
hector.
1) la propuesta del listbox, aunque tiene su lentitud en la generacion de los archivos de texto...
funciona *aceptablemente bien* [incluso] desde la version '97 ;)
2) los objetos SpreadSheet [de las librerias OWC - Office Web Components]
a) [de entrada] ya requieren de una version minima de office/excel 2000 [OWC v-9.0] en adelante
b) en la version *minima* [2000] SI tienen un *limite* en sus filas de... [adivina] -> 65536 :((
no corri pruebas en versiones posteriores [por si alguien se decide a hacer lo propio] :))
3) en todo caso, el codigo que *debiera* funcionar [a excepcion de lo comentado para la version 2000] es:
Private Sub UserForm_Activate()
Dim n As Byte, Filas As Long, Base As Long: Base = 1
With ThisWorkbook
For n = 2 To .Worksheets.Count
With .Worksheets(n)
Filas = .Range("b6").End(xlDown).Row - 5
Me.Spreadsheet1.ActiveSheet _
.Range("a" & Base & ":c" & Base + Filas - 1).Value = _
.Range("c6:e" & Filas + 5).Value
End With
Base = Base + Filas
Next
End With
End Sub
notas:
1) no se puede usar en el evento '_initialize', puesto que como el formulario aun no es mostrado
-> el objeto Spreadsheet1 [obviamente] no tiene aun una *ActiveSheet*
2) si el formulario se presenta como *No Modal*, habria que cuidar de los re/disparos del evento '_activate'
3) falta *darle forma* [visualmente] al control Spreadsheet y *administrar* algunas de sus propiedades [como]:
- si se quiere [o no] mostrar los encabezados [fila/columna], los menus, si sera *editable*, etc. etc. etc.
Ivan
hola Hector,
no se si le servira a OP (entre las limitaciones que comentas mas por
lo que me ha parecido que no admite mas de 260000 (aprox) registros
(uffff)) pero yo tenia ganas de saber un poco mas sobre este control y
esto podria abrir la veda. La verdad es que tu codigo es muy rapido
(me ha cargado 200000 +/- en 2/3 segundos).
aunque ahora no tengo tiempo,si no te importa, y espero que con el
permiso de OP, me gustaria consultarte algunas cosillas sobre este
control y sobre unos problemillas que me han surgido al intentar
preparar otra propuesta con un listbox
si puedo esta tarde/noche te las envio por aqui
un saludo y gracias por lo que me toca
Ivan
~ jose ~
hector he estado probando todo lo que me has dicho pero sin resultado
del todo satisfactorio.
He modificado el código como propones, haciendo pruebas por partes,
quiero decir que modificaba una parte del código y probaba, modificaba
la otra parte que proponias y probaba. He cambiado la extensión csv
por la de txt, también donde dice xlCSV por xlTEXT para guardar el
archivo en este formato, he cambiado el separador de listas que tenia
";" por ",", he reiniciado el pc por si acaso no lo habia pillado
bien, en fin... muchas muchas pruebas y lo más que he conseguido es
que me muestre todo el contenido (que no es poco) pero en una sola
columna no en tres. Esto lo he conseguido reemplazando esas dos partes
de código qeu decias en tu post anterior, PERO hay una pega. Como ya
comentaba antes (creo) tengo muchas celdas que están vacias, de la
primera columna siempre hay algún dato de las otras dos hay muchisimas
que no tienen datos, si hago pruebas así no me muestra el contenido
bien, pero si relleno todas las celdas vacias me muestra todo el
contenido desde la primera fila hasta la última, lo unico, eso, que me
muestra todo en una columna separado por comas.
Sobre el Spreadsheet decirte que en excel 2003 y el control MO
Spreadsheet 11 el nº de filas son 262144.
Sobre los enlces que propones, la verdad no lo he mirado, están casi
todos en inglés y no me entero de mucho, pero si me dices que por
cojones tengo que probarlos ya me intentaré apañar con algún
traductor.
Tengo una idea, ya que tengo problemas con el separador de listas, se
podría pasar cada una de las hojas a un archivo Access y después
recuperar todo el contenido en el listbox, vamos, lo que hacemos con
el csv pero con el Access. Quizás es mejor?
Bueno, muchas gracias y un saludo, vale?
jose
PD: la prueba que me dices que te funcionó, me puedes madar el archivo
por e-mail?
Héctor Miguel
__ 1 __
> ... Como ya comentaba antes (creo) tengo muchas celdas que estan vacias [...]
1) [si no me equivoco *demasiado*]... esta parte de *muchas celdas vacias* es la primera vez que lo comentas -???-
__ 2 __
> Sobre el Spreadsheet decirte que en excel 2003 y el control MO Spreadsheet 11 el nº de filas son 262144.
2) te alcanzaria perfectamente para el contenido de 4 hojas *llenas* [65531 x 4 = 262124] ;)
si en la realidad necesitaras mostrar algun contenido superior a esta cifra... -???-
__ 3 __
> Sobre los enlces que propones, la verdad no lo he mirado, estan casi todos en ingles y no me entero de mucho
> pero si me dices que por cojones tengo que probarlos ya me intentare apa#ar con algun traductor.
3) [creo que] no estaria *de sobra* :)) [pero vamos viendo si conseguimos evitarlos] ;)
__ 4 __
> ... ya que tengo problemas con el separador de listas, se podria pasar cada una de las hojas a un archivo Access
> y despues recuperar todo el contenido en el listbox... lo que hacemos con el csv pero con el Access. Quizas es mejor?
4) [yo ?]... de access... me sigo considerando *todo un neofito*... ni fu... ni fa... :-((
-> si encuentras la manera de *mandar bloques* de excel a access de manera *acumulativa* [como en los OWC]
despues podrias usar el *RecordSet* de ADO para *levantar* de la tabla de access al control en vba-excel -?-
__ 5 __
PD: la prueba que me dices que te funciono, me puedes madar el archivo por e-mail?
5) lo unico que *conservo* son los conocimientos adquiridos [generalmente NO los archivos de las pruebas] :-((
si no funciona lo del pase por access... puedo volver a *armar* un ejemplo [con sus consabidas *restricciones* coma ? punto y coma ?]
saludos,
hector.
Ivan
aqui vuelvo de nuevo, mas por vicio y por aprender que otra cosa, sobre todo por que estoy [casi] seguro que a Jose mi
propuesta no le va a interesar (entre otras cosas por que ni siquiera esta acabada, aparte de que implica 'sacrificar'
algunas filas de cada hoja), ademas de que estoy convencido (a falta de meterle mano, que se la metere seguro pronto) de
que la propuesta con el archivo de texto es mucho mejor,
he estado tanteando un poco la via del listbox 'actualizable' segun el listindex y aunque quedan varios flecos (quizas
alguno complicadillo) por lo menos parece que da el pego
se trata de cargar el listbox con:
.-si es la 1ª hoja=> los registros + los 'n' [nº de filas visibles en el listbox - 1] primeros registros de la
siguiente (previamente copiados en la anterior, de aqui viene el prescindir de n registros + 2 por hoja)
.-si es la ultima con sus registros mas el ultimo de la anterior insertado previamente en la fila 6
.-si es intermedia con ambas cosas
e ir renovandolo al llegar al ultimo /primer registro.(de momento lo hago en mouseUp ).
esta probado con 3 hojas. Las 2 primeras hasta la fila 65524 y la utima con varios cientos menos y el cambio entre
listas apenas es perceptible
algunos de los problemas que quedan por resolver serian:
1º controlar las diferentes teclas de avance retroceso (no creo que sea [demasiado] dificil)
2º simular/sustituir el scrollbar del list mediante uno autonomo (dentro de un frame, por el orden z) y sincronizarlo
con el nº total de registro. Tampoco creo que sea demasiado complicado aunque si liosillo
3º el que creo me dara mas quebraderos de cabeza y directamente relacionado con el anterior, coordinar los diferntes
eventos del listbox y el scrollbar
bueno, solo por si se te/os ocurre alguna idea para el scrollbar y porque ya que esta pues al final lo pongo.
en cuanto a las dudas que te comentaba, Hector, la del listbox ya la he resuelto (los dichosos eventos) y sobre el
spreadsheet no he podido meterle mano apenas, asi que me reservo para cuando haga algunas pruebas (las uno a las que
seguro que apareceran cuando pruebe la propuesta del csv)
bueno estos son los codigos (he dejado solo lo referente al listbox, pues con el scrollbar estaba todavia muy verde)
los unicos requisitos serian que la 1ª hoja de las listas ea la 2, que la ultima fila de datos de todas las hojas(menos
la ultima) fuese 65524 y el nº de filas en el listbox 11 . Aunque se pueden modificar en las constantes del inicio,< y
por supuesto la primera fila la 6 y columnas b/c:e>
'**********************
Const F As Long = 65524, plusF As Long = 10
Dim n_Hjs As Byte
'---------------
Sub CargarListbox()
Dim hj As Integer, ni As Long
With ListBox1
hj = Val(.Tag)
If .ListIndex = 0 And Val(.Tag) > 2 Then
hj = Val(.Tag) - 1
ElseIf .ListIndex = .ListCount - 1 And Val(.Tag) < n_Hjs Then
hj = Val(.Tag) + 1
End If
With ThisWorkbook.Worksheets(hj)
ListBox1.List = .Range("c6:e" & .[b65536].End(xlUp).Row).Value
End With
ni = ((.ListCount - 1) * -(hj < Val(.Tag)))
.ListIndex = ni + IIf(hj < Val(.Tag), -plusF, plusF)
.TopIndex = ni
.Tag = hj
End With
End Sub
'------------
Sub FilasExtra()
Dim n As Byte
If n_Hjs < 3 Then Exit Sub
Application.ScreenUpdating = False
With ThisWorkbook
For n = 3 To n_Hjs
.Worksheets(n).Range("b6:e" & 5 + plusF).Copy .Worksheets(n - 1).Range("b" & F + 1)
Next
n = n_Hjs - 1
Do While n > 1
.Worksheets(n).Range("b" & F & ":e" & F).Copy
.Worksheets(n + 1).Range("b6:e6").Insert xlShiftDown
n = n - 1
Loop
End With
Application.CutCopyMode = False
End Sub
'----------
Sub QuitarFilasExtra()
Dim n As Byte
If n_Hjs < 3 Then Exit Sub
Application.ScreenUpdating = False
With ThisWorkbook
For n = 3 To n_Hjs
.Worksheets(n).[b6:e6].Delete xlShiftUp
Next
n = n_Hjs - 1
Do While n > 1
.Worksheets(n).Range("b" & F + 1 & ":e" & F + plusF).Delete
n = n - 1
Loop
End With
Application.CutCopyMode = False
End Sub
'-----------
Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
With ListBox1
If .ListIndex = 0 Or .ListIndex = .ListCount - 1 Then CargarListbox
End With
End Sub
'----------
Private Sub UserForm_Initialize()
With ListBox1
.ColumnCount = 3: .Tag = 2
End With
With ThisWorkbook
n_Hjs = .Worksheets.Count
FilasExtra
With .Worksheets(2)
ListBox1.List = .Range("c6:e" & .[b65536].End(xlUp).Row).Value
End With
End With
End Sub
'---------------
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
QuitarFilasExtra
End Sub
'--------------------
un saludo
Ivan
PD: la verdad es que lo de las celdas vacias tambien es una novedad por aqui
~ jose ~
lo primero deciros que estaba equivocado, creía que os había comentado
lo de las celdas vacías, de todas formas ya decía entre paréntesis
"creo".
El solucionar este problema mediante Access, no se ni por donde
empezar, así que lo descarto, prefiero que me vuelvas a rearmar el
código teniendo en cuenta el separador de listas, lo que pasa que me
crea una duda, y si en otro pc hay otro separador de listas?, se
podría saber mediante código que separador de listas o argumentos
tiene un pc para así aplicar un código u otro?
Sobre el pedazo de curro que se ha pegado el amigo Ivan, decir que
tiene alguna pega que otra como bien dices como por ej:
Si en la "Hoja3" hay menos de 10 filas, se interrumpe el código en
esta línea:
ListBox1.List = .Range("c6:e" & .[b65536].End(xlUp).Row).Value
De momento no es el caso pero si quisiera que al pinchar sobre alguna
línea del listbox hiciera "algo" como por ejemplo rellenarse los
textbox si pincho en la primera fila me manda a la 11 y si pinchara en
la última si que se marcaria dicha fila pero vería la siguiente página
que esto tampoco es problema eh! sólo comentaba.
Estoy pensado que si para ver las siguientes hojas de datos en el
listbox tengo que pinchar sobre la fila del listbox, bien sea la
última o la primera, que te parece si hago un par de botones
"siguiente" y "atrás". De esta forma podría hacerlos con el RowSource
y así podría poner encabezados. Dejo este código al final del mensaje
por si a alguien le interesa.
De todas formas me gustaría probar el nuevo código que pido al
principio del mensaje a Hector porque así podría mostar todos los
registros a la vez en el listbox y además aprenderíamos a exportar e
importar datos.
Muchas gracias
Un saludo
jose
~ jose ~
Perdón, el código tiene aún errores...
~ jose ~
> >... Dejo este código al final del mensaje por si a alguien le interesa.
Este podría se el código para los botones siguientes y atrás.
En las propiedades del formulario le he dicho que son 3 columnas, el
tama#o de ellas y que muestre encabezados.
Private Sub CommandButtonSiguiente_Click()
Dim n As Integer
n = ActiveSheet.Index
If Worksheets(n).Index >= 2 And _
Worksheets(n).Index < Worksheets.Count Then
Worksheets(n + 1).Activate
ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(,
3).Address
End If
End Sub
Private Sub CommandButtonAtras_Click()
Dim n As Integer
n = ActiveSheet.Index
If Worksheets(n).Index > 2 Then
Worksheets(n - 1).Activate
ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(,
3).Address
End If
End Sub
un saludo,
jose
Ivan
ya como ultima propuesta, usando un scrollbar ([creo que] habria que colocarlo dentro de un frame para poder cubrir la
barra dell propio listbox <no he encontrado la propiedad que la oculte en el listbox> mediante el orden z)
he eliminado el pase de registros entre hojas (era para hacer un efecto mas continuo en los saltos pero no es necesario,
pues el usuario apenas lo notara) y he cogido el rowsource de la ultima propuesta de Jose
creo que funciona aceptablemente bien dando la impresion de una lista continua pudiendo utilizar las teclas de
diereccion e inicio y fin como si fuera todo uno y sin apenas notarse
aun puede aparecer alguna pega y no incluye todos los posibles controles de errrores (de hecho como tales no incluye
ninguno)
un saludo
Ivan
bueno este es el codigo (se puede modificar la ultima fila en la constante) y solo requiere un listbox y un scrollbar
Const FHj As Long = 65519
Dim SinEventos As Boolean
'-----------------
Private Sub ListBox1_Change()
If Not SinEventos Then ScrollBar1.Value = ValorScroll
End Sub
'-----------------
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim nh As Byte, nI As Byte, lI As Long
nI = ActiveSheet.Index
With ListBox1
Select Case KeyCode
Case vbKeyEnd
With Worksheets(Worksheets.Count): .Activate
lI = .Range("b5").End(xlDown).Row - 6: End With
Case vbKeyHome
Worksheets(2).Activate: lI = 0
Case Else
If .ListIndex > 0 And .ListIndex < Range("b5").End(xlDown).Row - 6 Then Exit Sub
If KeyCode = vbKeyUp And .ListIndex = 0 Then
If nI > 2 Then Worksheets(nI - 1).Activate: lI = FHj - 1
ElseIf KeyCode = vbKeyDown And .ListIndex = .ListCount - 1 Then
If nI < Worksheets.Count Then Worksheets(nI + 1).Activate: lI = 0
Else
End If
End Select
If ActiveSheet.Index <> nI Then CambiarListbox: .ListIndex = lI
End With
ScrollBar1.Value = ValorScroll
End Sub
'-----------------
Private Sub ScrollBar1_Change()
Dim nh As Byte
SinEventos = True
With ScrollBar1
nh = HojaFila(.Value)
If ActiveSheet.Index <> nh Then
Worksheets(nh).Activate
ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(, 3).Address
End If
ListBox1.ListIndex = FilaHoja(.Value) - 1
End With
SinEventos = False
End Sub
'-----------------
Sub CambiarListbox()
ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(, 3).Address
End Sub
'-----------------
Function ValorScroll() As Long
ValorScroll = (ListBox1.ListIndex + 1) + (FHj * (ActiveSheet.Index - 2))
End Function
'-----------------
Function NroFilas() As Long
Dim nHjs As Byte
With ThisWorkbook: nHjs = .Worksheets.Count
If nHjs > 1 Then NroFilas = (FHj * (nHjs - 2)) + _
.Worksheets(nHjs).[b5].End(xlDown).Row - 5
End With
End Function
'-----------------
Function HojaFila(valor As Long) As Byte
HojaFila = Int(valor / FHj) + (2 + (valor Mod FHj = 0))
End Function
'-----------------
Function FilaHoja(valor As Long) As Long
FilaHoja = valor - (FHj * (ActiveSheet.Index - 2))
End Function
'-----------------
Private Sub UserForm_Initialize()
Dim n As Integer
n = ActiveSheet.Index
If Worksheets(n).Index >= 2 And _
Worksheets(n).Index < Worksheets.Count Then
Worksheets(n + 1).Activate
ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(, 3).Address
End If
With ScrollBar1
.Min = 1: .Max = NroFilas
End With
End Sub
'-----------------
~ jose ~
El código que propones me dá fallo en el evento change del scrollbar
en la linea:
ListBox1.ListIndex = FilaHoja(.Value) - 1
final.
Un saludo
jose
Ivan
> El código que propones me dá fallo en el evento change del scrollbar
> en la linea:
> ListBox1.ListIndex = FilaHoja(.Value) - 1
> Se interrumpe cuando llego al final del listbox o cuando subo desde el
> final.
tienes razon, se me habia pasado restarle las [5] filas iniciales.En
el Keydown del list cambia el Fhj - 1 por Fhj - 6 asi=>
If nI > 2 Then Worksheets(nI - 1).Activate: lI = FHj - 6
Y aun asi se saltaba un registro (todavia no se muy bien porque).
Prueba a cambiar estas instrucciones al KeyUp=>
End Select
'esta =>> If ActiveSheet.Index <> nI Then
CambiarListbox: .ListIndex = lI
End With
''y esta =>> ScrollBar1.Value = ValorScroll
End Sub
y ponlas en el keyup
Private Sub ListBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
If ActiveSheet.Index <> nI Then CambiarListbox: ListBox1.ListIndex =
lI
ScrollBar1.Value = ValorScroll
End Sub
asi parece ir correctamente
de todas formas he visto alguna cosilla mas, aparte de añadir otras.
No se si estaras interesado, de todas formas cuando este +/- lo pongo
por si a alguien mas pudiera valerle.
un saludo y cualquier otro problema/solucion/alternativa que veas, si
quieres la pones
Ivan
Ivan
disculpame pero le he dado a enviar demasiado pronto
para este nuevo caso del keyup, deberias poner las variables nI y lI a
nivel del modulo (y sacarlas de keydown). Posiblemente se puede evitar
pero de momento asi va bien (parece)
Ivan
>
> - Mostrar texto de la cita -
y hola de nuevo,
otro de los problemas que surgian estaba aqui=>
Private Sub UserForm_Initialize()
Dim n As Integer
n = ActiveSheet.Index
aqui=>>
=>> If Worksheets(n).Index >= 2 And _
=>> Worksheets(n).Index < Worksheets.Count Then
=>> Worksheets(n + 1).Activate
=>> ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(,
3).Address
=>> End If
With ScrollBar1
.Min = 1: .Max = NroFilas
End With
End Sub
la verdad es que copie tu codigo sin analizarlo (craso error)
podrias cambiar ese fragmento por este otro=>
If n >= 2 And n <= Worksheets.Count Then _
ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(,
3).Address
un saludo
Ivan
Ivan
al final me he liado, me he liado y esto es lo que ha quedado, por si le puede servir a alguien:
''*************************en el modulo del formulario***********************
'---------------------------------------------------------------------------------------
' Fecha : 27/09/07 01:02
' Proposito : simular listbox cargado con varias hojas con filas ocupando
'toda o gran parte de la hoja. Todas las hojas menos la ultima deben tener
'el mismo numero de filas con datos (constante FHj). Esta hecho para cargar
'las columnas 'C:E' . La columna 'B' debe tener el maximo de filas con datos y sin
'ninguna celda vacia entre medias
' Requisitos: en un userform: 1 listbox(ListBox1),1 frame(Frame1),1 scrollbar(scrollbar1)
'y 4 Commandbutton(cmdPrimero,cmdAnterior,cmdSiguiente ycmdUltimo)
'---------------------------------------------------------------------------------------
Option Explicit
Const FHj As Long = 65531 ' nº filas con datos fijo en todas las hojas menos la ultima
Dim SinEventos As Boolean, nI As Byte, lI As Long 'nI y lI se podrian evitar, pero ....
'-----cambia el rowsource del listbox
'
Sub CambiarListbox()
ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(, 3).Address
End Sub
'--- devuelve el nº de hoja correspondiente al valor pasado
' en funcion del nº fijo de registros por hoja especificdo en FHj
'
Function HojaFila(valor As Long) As Byte
HojaFila = Int(valor / FHj) + (2 + (valor Mod FHj = 0))
End Function
'--- devuelve el nº de fila de la hoja activa correspondiente al valor pasado
' en funcion del nº fijo de registros por hoja especificdo en FHj
'
Function FilaHoja(valor As Long) As Long
FilaHoja = valor - (FHj * (ActiveSheet.Index - 2))
End Function
'--- devuelve el nº total de registros/filas de todas las hojas menos la 1ª, es decir,
' el nºfijo(FHj) de registros*hoja de la 2ª a la penultima mas los de la ulitima
'
Function NroFilas() As Long
Dim nHjs As Byte
With ThisWorkbook: nHjs = .Worksheets.Count
If nHjs > 1 Then NroFilas = (FHj * (nHjs - 2)) + _
.Worksheets(nHjs).[b5].End(xlDown).Row - 5
End With
End Function
'-------buscaa el value del scrollbar equivalente a la fila de la hoja activa
'
Function ValorScroll2(FilaList As Long) As Long 'FilaList = listindex (pej)
ValorScroll2 = (FilaList + 1) + (FHj * (ActiveSheet.Index - 2))
End Function
'---psrs habilitar7deshabilitar los botones
'
Sub HabilitarBotones()
Dim pos As Long
With ScrollBar1
cmdPrimero.Enabled = .Value > .Min Or ListBox1.ListCount < 2
cmdAnterior.Enabled = .Value > .Min Or ListBox1.ListCount < 2
cmdSiguiente.Enabled = .Value < .Max Or ListBox1.ListCount < 2
cmdUltimo.Enabled = .Value < .Max Or ListBox1.ListCount < 2
End With
End Sub
'---para colocar los controles en el formulario (las medidas/posicion del
' listbox se pueden modificar en la 1ª linea de su With, en el resto se puede
' modificar al gusto los margenes entre los botones y el listbox y el ancho
' del frame portaScroll, se supone que los botones son iguales de tamaño entre si
'
Sub AjustarControles()
Dim h As Single, l As Single, lb As Single, nc As Byte, Botones
With ListBox1
.Top = 70: .Left = 70: .Height = 105: .Width = 310
.ColumnCount = 3: .ColumnHeads = True
.ColumnWidths = [c6].Width & ";" & [d6].Width & ";" & [e6].Width
Frame1.Height = .Height: Frame1.Top = .Top
Frame1.Width = 14: Frame1.Left = .Left + .Width - Frame1.Width
l = ((.Width - Frame1.Width) - (cmdPrimero.Width * 4)) / 5
h = .Top + .Height + 5: lb = l + .Left
End With
With ScrollBar1
.Height = Frame1.InsideHeight: .Top = 0
.Width = Frame1.InsideWidth: .Left = 0
End With
Botones = Array("cmdPrimero", "cmdAnterior", "cmdSiguiente", "cmdUltimo")
For nc = LBound(Botones) To UBound(Botones)
With Controls(Botones(nc))
.Top = h: .Left = lb: lb = lb + .Width + l
End With
Next
End Sub
'---carga los registros al abrir (para dejar mas despejado
' el initialice para otros menesteres)
'
Sub CargarListasDeInicio()
Dim f As Long
If ActiveSheet.Index > 1 Then
ListBox1.RowSource = "c6:e" & Range("b5").End(xlDown).Row
f = ActiveCell.Row
If f < 6 Then f = 0 Else f = f - 6
With ScrollBar1
.Min = 1: .Max = NroFilas: .Value = ValorScroll2(f)
End With
End If
End Sub
'-----------------
Private Sub cmdAnterior_Click()
With ScrollBar1: .Value = .Value - 1: End With
End Sub
'-----------------
Private Sub cmdPrimero_Click()
Worksheets(2).Activate
CambiarListbox
ScrollBar1.Value = 1
End Sub
'-----------------
Private Sub cmdSiguiente_Click()
With ScrollBar1: .Value = .Value + 1: End With
End Sub
'-------------
Private Sub cmdUltimo_Click()
Worksheets(Worksheets.Count).Activate
CambiarListbox
With ScrollBar1: .Value = .Max: End With
End Sub
'-----------------
Private Sub ListBox1_Change()
On Error GoTo ListBox1_Change_Error
If Not SinEventos Then ScrollBar1.Value = ValorScroll2(ListBox1.ListIndex)
HabilitarBotones
On Error GoTo 0
Exit Sub
ListBox1_Change_Error:
End Sub
'-----------------
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim nh As Byte ', nI As Byte, lI As Long
nI = ActiveSheet.Index
With ListBox1
Select Case KeyCode
Case vbKeyEnd
With Worksheets(Worksheets.Count): .Activate
lI = .Range("b5").End(xlDown).Row - 6: End With
Case vbKeyHome
Worksheets(2).Activate: lI = 0
Case Else
If .ListIndex > 0 And .ListIndex < Range("b5").End(xlDown).Row - 6 Then Exit Sub
If KeyCode = vbKeyUp And .ListIndex = 0 Then
If nI > 2 Then Worksheets(nI - 1).Activate: lI = FHj - 1
ElseIf KeyCode = vbKeyDown And .ListIndex = .ListCount - 1 Then
If nI < Worksheets.Count Then Worksheets(nI + 1).Activate: lI = 0
Else
End If
End Select
End With
End Sub
'----------------
Private Sub ListBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If ActiveSheet.Index <> nI Then CambiarListbox: ListBox1.ListIndex = lI
ScrollBar1.Value = ValorScroll2(lI)
End Sub
'-----------------
Private Sub ScrollBar1_Change()
Dim nh As Byte
On Error GoTo ScrollBar1_Change_Error
SinEventos = True
With ScrollBar1
nh = HojaFila(.Value)
If ActiveSheet.Index <> nh Then
Worksheets(nh).Activate
CambiarListbox
End If
ListBox1.ListIndex = FilaHoja(.Value) - 1
End With
SinEventos = False
On Error GoTo 0
Exit Sub
ScrollBar1_Change_Error:
End Sub
'---------------------
'
Private Sub UserForm_Initialize()
AjustarControles
CargarListasDeInicio
Frame1.ZOrder
End Sub
'*********************************************************************
un saludo
Ivan
Ivan
se ve que mi palabra no vale mucho
se me ha pasado esto=> el scrollbar debe ir dentro del Frame (da lo mismo las dimensiones, posicion, etc.., pero que
este dentro)
un saludo
Ivan
~ jose ~
muy bien, muy currado, yo estaba trabajando en esto que pongo al final
del prensete. Lo que pasa que el tuyo está mejor, muestra todo como si
fuera una lista completa, el mio hay que pasar de página en página y
tampoco he controlado el "Enable" de los botones como tú. Lo único que
tiene el mio es que se podría utilizar en excel 2007 utilizando el
millón de filas, o en versiones de excel que tengan más (en un futuro)
o menos cantidad de filas que 65536 por hoja, en cambio el tuyo tal
como está ahora se emplearían 65531(ya que las 5 primeras me las
reservo para otros menesteres) y (caso NO probable) que pasaría si se
emplea en versiones anteriores a excel97?. Otra pequeña cosa que tiene
el mio es que tiene un lable con el nº total de registros, sin más!.
Bueno, pues haré una combinación de soluciones, cojeré cosas de mi
código y otras del tuyo, vale?
De todas formas si Hector lee este post me gustaría (si quiere) que
pusiera el código que me comentó, el que trataba el "Separador de
listas".
Bueno, el formulario para el que he hecho este código (como mínimo)
tiene que tener ListBox1, Lable5, CommandButtonAtras,
CommandButtonSiguiente y en las propiedades del listbox1 establecer 3
columnas
Muchas gracias y un saludo, vale?
jose
Dim n As Integer, HojaActiva As Integer
Private Sub CommandButtonSiguiente_Click()
n = ActiveSheet.Index
With ListBox1
If .ColumnHeads = False Then .ColumnHeads = True
End With
If n >= 2 And n < Worksheets.Count Then
Worksheets(n + 1).Activate
ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(,
3).Address
End If
End Sub
Private Sub CommandButtonAtras_Click()
Dim n As Integer
n = ActiveSheet.Index
With ListBox1
If .ColumnHeads = False Then .ColumnHeads = True
End With
If Worksheets(n).Index > 2 Then
Worksheets(n - 1).Activate
ListBox1.RowSource = "c6:" & Range("b5").End(xlDown).Offset(,
3).Address
End If
End Sub
Private Sub UserForm_Initialize()
TotalReg = 0
For n = 2 To Worksheets.Count
Worksheets(n).Activate
Reg = Evaluate("=COUNTA(B:B)") - 1
TotalReg = TotalReg + Reg
Next
Label5.Caption = TotalReg
Worksheets(2).Activate
Range("a1:a2").Clear
Range("a1").Select
Selection.End(xlDown).Select
Range("a1") = Right(ActiveCell.Address(rowabsolute:=False), _
(Len(ActiveCell.Address(rowabsolute:=False)) - 2))
ListBox1.ColumnHeads = True
If Evaluate("=COUNTA(B:B)") = 1 Then
ListBox1.RowSource = "c6:e6"
Range("b6").Select
Else
ListBox1.RowSource = ("c6:e" & Range("b5").End(xlDown) + 5)
Range("b5").Select
Selection.End(xlDown).Select
End If
End Sub
Ivan
>Lo único que
> tiene el mio es que se podría utilizar en excel 2007 utilizando el
> millón de filas, o en versiones de excel que tengan más (en un futuro)
> o menos cantidad de filas que 65536 por hoja,
hola Jose, la verdad es que, aunque no conozco el 2007 y con el no te
lo podria asegurar, con el resto precisamente se podria adptar sin
ningun problema con solo cambiar el valor de la constante FHj por el
valor que quieras (de hecho por eso utilice una constante, facilmente
modificable), el unico requisito, surgido de intentar adptarlo a tu
consulta, es que el nº de filas en todas las hojas menos en la ultima
(en la cual tendria que tener igual o menos[aunque con una ligera
modificacion ...]) el nº de filas sea igual
en ese sentido ya te digo que tampoco creo que hubiera ningun problema
en 2007 (o posterior), lo unico que si ya el tener mas de cienmil
registros a elegir en un listbox me produce sofocos(pensndo en el
usuario) si ya le metes varios millones ni te cuento
> en cambio el tuyo tal como está ahora se emplearían 65531
en realidad lo que admite (hasta el 2003 incluido) es el maximo de
filas admisible en tu consulta => 65531 + 5 = 65536
en cuanto a las versiones previas al 97, salvo que estas no admitan
alguna de las propiedades y/o metodos y/o funciones utilizadas (en
cuyo caso supongo se podrian adaptar a las admitidas) de nuevo no veo
donde podria estar el problema (una vez modificada [o condicionada] la
constante (fruto de tu consulta)
de todas formas como te digo los codigos estan adaptados a las
circunstancias/requisitos/datos expuestos por ti, pero pueden/podrian
ser muchisimo mas genericos en muchos de sus condicionantes, ...pero
creo que esa seria otra historia
un saludo
Ivan
Ivan
y otra modificacion(por simplificar)
para los botones primero y ultimo. Aunque el trabajo lo hacen
igual,esto les evita trabajo innecesario (uno, que se complica la
vida. Seguro que hay algunas mas como esta)
'-----------------
Private Sub cmdPrimero_Click()
ScrollBar1.Value = 1
End Sub
'-----------------
Private Sub cmdUltimo_Click()
With ScrollBar1: .Value = .Max: End With
End Sub
un saludo
Ivan
~ jose ~
> hola Jose, la verdad es que, aunque no conozco el 2007 y con el no te
> lo podria asegurar, con el resto precisamente se podria adptar sin
> ningun problema con solo cambiar el valor de la constante FHj por el
> valor que quieras (de hecho por eso utilice una constante, facilmente
> modificable),
con este código se adaptaría a todas las versiones de excel, el
resultado es
el nº de filas de la hoja:
range("a1").select
FHj= Right(ActiveCell.Address(rowabsolute:=False), _
(Len(ActiveCell.Address(rowabsolute:=False)) - 2))
> > en cambio el tuyo tal como está ahora se emplearían 65531
>
> en realidad lo que admite (hasta el 2003 incluido) es el maximo de
> filas admisible en tu consulta => 65531 + 5 = 65536
si, si, eso es lo que he dicho, la frase "entera" que expuse fue:
en cambio el tuyo tal como está ahora se emplearían 65531
(ya que las 5 primeras me las reservo para otros menesteres)
> en cuanto a las versiones previas al 97, salvo que estas no admitan
> alguna de las propiedades y/o metodos y/o funciones utilizadas (en
> cuyo caso supongo se podrian adaptar a las admitidas) de nuevo no veo
> donde podria estar el problema (una vez modificada [o condicionada] la
> constante (fruto de tu consulta)
El problema era que no sé que versión de excel tiene el "usuario
final"
si lo supiera simplemente habría que cambiar la constante como tú
bien dices, sea para excel 97, anteriores o incluso 2007, pero con el
código que propongo para la constante solucionaría el problema.
Por lo demás muy bien, gran trabajo Ivan.
Venga! un saludo, vale?
jose
~ jose ~
a mi también me pasa mogollón!!!
Ivan
> >...(uno, que se complica la vida....
>
> a mi también me pasa mogollón!!!
hola de nuevo, Jose, (soy un pesado)
solo un par de detalles que he ido viendo en mi propuesta.
Por un lado creo que convendria volver a poner el contenido de KeyUp
el Keydown (detras del end select), al menos eso me ha parecido en
alguna de las pruebas, pues al dispararse despues del change en
ocasiones parece hacer cosas rarillas
por otro he 'descubierto' otra curiosidad: al bajar por el listbox con
la tecla felcha abjo, al llegar al final rebota hasta el principio de
la ultima hoja(aunque no siempre creo) y vuelve a bajar. La solucion
que se me ha ocurrido de momento (supongo que habra otras) ha sido
declarar otra variable boolean en el modulo que condicione el change
del scrollbar si la tecla es esta y el registro el ultimo. Parece
funcionar. Mas o menos asi:
en el area de declaraciones: Por cierto si vuelves a meter en el
Keydown las instrucciones que cambie al keyup, puedes volver a poner
las variables en el proc. y quitarlas de aqui)
Dim sinTecla As Boolean
en el evento keydown del listbox modificas a esto=>
ElseIf KeyCode = vbKeyDown And .ListIndex = .ListCount - 1 Then
If nI < Worksheets.Count Then Worksheets(nI + 1).Activate: lI
= 0 Else _
sinTecla = True: KeyCode = 0: Exit Sub
Else
en el keyup pones simplemente=>
sinTecla = false
y al inicio del change del scrollbar=>
if sinTecla then exit sub
bueno, creo que no voy a mirar mucho mas, porque sino, mas que un hilo
va a parecer una madeja
un saludo
Ivan
PD: respecto al resto, como tu mismo comentas, creo que no resultara
dificil hacerlo multi version
Ivan
me he vuelto a complicar la vida
olvidate de la variable y cambia /añade solo esto=>
If nI < Worksheets.Count Then Worksheets(nI + 1).Activate: lI = 0 Else
KeyCode = 0: Exit Sub
un saludo
Ivan
~ jose ~
> me he vuelto a complicar la vida
Madre mia! así dá gusto, tu y yo tenemos varias cosas en común, cuando
nos centramos en algo... parecemos maños.
Si que parece esto una madeja, jejeje, hace cuanto que no ves un hilo
tan largo como este???, hace tiempo también "provoqué" algún hilo muy
largo, pero no como este.
Bueno, pues gracias y hasta pronto, vale?
Un saludo,
jose