** NORMAS DEL FORO **
Inicio del foro Inicio del foro > Otros de Microsoft: Windows y Office > Excel
  Mensajes nuevos Mensajes nuevos RSS - Lista dinámica con macro
  Preguntas frecuentes Preguntas frecuentes  Buscar en el foro   Eventos   Registro Registro  Iniciar sesion Iniciar sesion

Tema cerradoLista dinámica con macro

 Responder Responder
Autor
Mensaje
Galathea Ver desplegable
Habitual
Habitual
Avatar

Unido: 15/Septiembre/2012
Localización: España
Estado: Sin conexión
Puntos: 109
Enlace directo a este mensaje Tema: Lista dinámica con macro
    Enviado: 09/Septiembre/2017 a las 22:57
Saludos; bueno estoy intentando crear una macro para crear una lista dinámica pero me da error constantemente y no sé a que se debe, o es que no es posible.

Este es el código con el que me da error:

Private Sub CommandButton3_Click()

Dim fila As Long

For fila = Cells(2000, "A").End(xlUp).Row To 1 Step -1

If Cells(fila, 1).Value > 0 Then

Cells(fila, 1).Select

With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=DESREF(Componentes!A:A;3;;CONTARA(Componentes!$A$4:$A$100))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Elija una Opción:"
        .ErrorTitle = "Aviso !!!"
        .InputMessage = "" & Chr(10) & "" & Chr(10) & "Debe Selecionar una Opción de la Lista."
        .ErrorMessage = "" & Chr(10) & "" & Chr(10) & "Debe Selecionar una Opción de la Lista."
        .ShowInput = True
        .ShowError = True
    End With
    
End If

Next
    
End Sub

En rojo es donde me muestra el error.

Sim embargo si lo hago de forma manual creando la lista desde datos y validación lo toma perfecto. Pero necesito hacerlo con macro.

Con otra rutina veo que sí funciona (Abajo) pero me muestra filas vacías a pesar de tener cliqueado omitir blancos.

Range("C2:C10000").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Componentes!$A$4:$A$25"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Elija una Opción:"
        .ErrorTitle = "Aviso !!!"
        .InputMessage = "" & Chr(10) & "" & Chr(10) & "Debe Selecionar una Opción de la Lista."
        .ErrorMessage = "" & Chr(10) & "" & Chr(10) & "Debe Selecionar una Opción de la Lista."
        .ShowInput = True
        .ShowError = True
    End With

Si pongo el primer código en un botón me da error; pero si pongo el segundo no.

Y no entiendo por qué.

Les agradezco de antemano.

Un saludo.
he escrito tanta inútil cosa, sin descubrirme, sin dar conmigo.
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 4927
Enlace directo a este mensaje Enviado: 10/Septiembre/2017 a las 01:09
¿A que te refieres con «... una lista dinámica...»?

Creo que lo que pretendes (con tu código) es: Lista desplegable en celdas con validación de datos por Lista.

Por cierto que; validar celdas una por una (incluso mediante macros) no es lo más recomendable, menos aún si las celdas que validas ya tienen datos (es lo que parece que intenta hacer tu macro).

La validación, cuando realmente es efectiva es cuando se aplica a celdas vacías, para que los datos que admitan sean datos válidos. También es efectiva para que no se sustituyan datos válidos por datos no válidos, pero ese caso es menos frecuente, ya que 'normalmente' los datos se entran una vez y permanecen para ser consultados.

Mañana, si tengo tiempo, le echo una mirada mas detenida a tu código.


Pregunta lo que no sabes, recuérdalo para cuando te pregunten y ofrece tu ayuda (reconforta).

Mi sitio_web con ejemplos Excel.
Arriba
Galathea Ver desplegable
Habitual
Habitual
Avatar

Unido: 15/Septiembre/2012
Localización: España
Estado: Sin conexión
Puntos: 109
Enlace directo a este mensaje Enviado: 10/Septiembre/2017 a las 01:24
Así es, es para utilizar una lista desplegable.
La lista puede encontrarse en blanco o contener ya datos, aunque estos solamente se pueden modificar con la lista; no admite otros valores.

Lo que pretendo es que la lista nunca se las filas que va a contener, y tengo que darlo lo máximo de la hoja; pero quiero que cuando despliegue la lista no me muestre lo que está vació o en blanco por tener un rango de filas tan grande.

Es lo que necesito, todo lo demás me funciona correctamente, pero no entiendo por qué me da error en la función que utilizo. Seguiré intentando, a ver si doy con la tecla.

Muchas gracias.


Editado por Galathea - 10/Septiembre/2017 a las 01:26
he escrito tanta inútil cosa, sin descubrirme, sin dar conmigo.
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 4927
Enlace directo a este mensaje Enviado: 10/Septiembre/2017 a las 13:27

Por partes:


-> No entiendo que la validación tenga que ser con macro (salvo que quieras aprender a hacerlo).

-> «Omitir blancos» no quiere decir que si la lista origen tiene celdas vacías no muestre esas celdas, lo que quiere decir es que si el usuario borra una celda validada (eso es un blanco) lo permita, si la desmarcas no permite borrar contenido.

-> La lista desplegable mostrará lo que tú le digas que muestre. Para que la lista desplegable no tenga 'blancos', el rango origen no debe tener celdas vacias.

-> El código (aparte de lo comentado en mi mensaje de ayer,) puedes intentar con...


Private Sub CommandButton3_Click()

Dim fila As Long, miRango$

fila = Worksheets("Componentes").Cells(Rows.Count, 1).End(xlUp).Row

miRango = Worksheets("Componentes").Range("A4:A" & fila).Address

For fila = Cells(2000, "A").End(xlUp).Row To 1 Step -1

If Cells(fila, 1).Value > 0 Then

Cells(fila, 1).Select

With Selection.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="=Componentes!" & miRango

.IgnoreBlank = True

' Aquí el resto de tu código.



Pregunta lo que no sabes, recuérdalo para cuando te pregunten y ofrece tu ayuda (reconforta).

Mi sitio_web con ejemplos Excel.
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 4927
Enlace directo a este mensaje Enviado: 10/Septiembre/2017 a las 13:52

Si 'no puedes evitar' que el rango origen para la lista desplegable tenga celdas vacías, puedes filtrar (con filtro avanzado) a otro rango y direccionar la validación al nuevo rango.



Por ejemplo si tenemos libre  Componentes!K:M, podría ser:


Sub NoBlancos()

Worksheets("Componentes").Range("A3:A2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("K1:K2"), CopyToRange:=Range("M1"), Unique:=True

End Sub



Pregunta lo que no sabes, recuérdalo para cuando te pregunten y ofrece tu ayuda (reconforta).

Mi sitio_web con ejemplos Excel.
Arriba
Galathea Ver desplegable
Habitual
Habitual
Avatar

Unido: 15/Septiembre/2012
Localización: España
Estado: Sin conexión
Puntos: 109
Enlace directo a este mensaje Enviado: 10/Septiembre/2017 a las 17:29
Gracias, al final he conseguido solventarlo.

Hice la siguiente modificación:

Range("C2:C10000").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Componentes"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Elija una Opción:"
        .ErrorTitle = "Error !!!"
        .InputMessage = "" & Chr(5) & "" & Chr(5) & "Debe Selecionar una Opción de la Lista."
        .ErrorMessage = "" & Chr(3) & "" & Chr(3) & "Debe Selecionar una Opción de la Lista."
        .ShowInput = True
        .ShowError = True
    End With

1. Creo un nombre de rango.
2. Utilizo una lista de validación y como función le indico  DESREF y CONTARA, que unidas hacen lo que buscaba.

Con esto en la lista siempre me va a mostrar todas las filas que tengan contenido en la hoja componentes, y no me limita el rango de la lista ni tener que estar modificandola.

El motivo de hacerlo así es porque el libro lo van a trabajar gente sin nociones de Excel, y tengo que darlo fácil, y sin que ellos tengan que modificar.

Gracias por las respuestas, me las apunto pues seguro me servirán también.

Pueden cerrar el hilo.
he escrito tanta inútil cosa, sin descubrirme, sin dar conmigo.
Arriba
 Responder Responder
  Compartir tema   

Ir al foro Permisos de foro Ver desplegable