Lista de validación en Microsoft Excel, plantillas protegidas, macros y rangos variables

Hola Fernando, estoy teniendo problemas con la lista de validación, es en el punto a., cuando defino el nombre de la lista:
=Princ!$C$50:$C$272(Princ!$C$50;CONTARA('Mails.xlt'!direcciones)-1;0)
Esto esta fallando, ya que no puedo definirla como tal, solo puedo así en el cuadro de dialogo:
=Princ!$C$50:$C$272
Cuando voy a Validación, Datos, y pongo =direcciones, me dice 'la lista actualmente evalúa un error, desea continuar', le doy el si y no puedo abrir la lista (ya destildé el "Mensaje de error" de la lista de validación). Aunque no la he hecho correr a la macro, no he detectado en ninguna parte que la guarde en la plantilla,¿se puede?, es decir abro un nueva planilla a partir de mi plantilla Mails.xlt, en c4 tengo mi lista desplegable, agrego un nuevo nombre, si no esta en la lista que tiene un rango que va desde c50 a c272, lo debería agregar a la planilla y a la plantilla también, eso debería hacer, se que es medio complicado de hacer, pero te pido me ayudes. Como un extra ademas seria piola que la ambas listas, la de la plantilla y la planilla se ordenaran alfabéticamente, pero si es mucho joder, no lo hagas, total eso lo puedo hacer luego yo. Ah, una cosa importantísima que se me pasó: la plantilla está protegida, pero supongo que eso no es problema porque defino el rango de la lista y unos cuantos más y los desbloqueo solo a esos. Fernando, desde ya mil gracias, por todo, Un abrazo, Esteban,
Te paso tu macro, por si la borraste:
La solución al primer problema de tu segunda pregunta, comprende tres pasos:
a.- Definir un nombre de rango variable para tu lista primaria.
Esto significa que no será fijo sino que variará sus límites según la cantidad de datos que tenga la lista. Al definir el nombre (Ctrl + F3) deberías colocar una fórmula como esta:
=Lista!$C$4:DESREF(Lista!$C$4;CONTARA(Lista!$C$4:Lista!$C$130)-1;0)
Supuesto que ella empiece en la celda C4 de la hoja "Lista"
b.- En la solapa de "Mensaje de error" de tu lista de validación, quita el tilde en la casilla "Mostrar mensaje de error si se introducen datos no válidos"
Obviamente, en la solapa "Configuración" el rango de la lista deberá ser el que definiste en el punto a.-
Por ejemplo:
=milist
c.- Usando el editor de Visual Basic (Alt + F11), pegar el siguiente macro en el panel de la hoja donde estuviera la lista de selección:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ListRange As Range
Set ListRange = Sheets("Lista").Range("milist")
If Target.Address(False, False) = "C7" Then
If ListRange.Find(Target, , , xlWhole) Is Nothing And Not (IsEmpty(Target)) Then
Application.ScreenUpdating = False
itemnew = Target.Value
Sheets("Lista").Select
ListRange.Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Value = itemnew
ActiveCell.Offset(1).Interior.ColorIndex = 33
With Selection
.CurrentRegion.Select
.Sort ListRange
.End(xlUp).Select
End With
Sheets("Hoja1").Select
Application.ScreenUpdating = True
End If
End If
Set ListRange = Nothing
End Sub
1

1 respuesta

Respuesta
1
Antes de ver esta pregunta, te he enviado el archivo con la solución propuesta.
Allí notarás que, para la definición del rango variable, utilizo la función DESREF, tal como te indiqué expresamente en la respuesta anterior.
En tu caso particular, al definir el rango para "direcciones", coloca en la casilla inferior, lo siguiente:
=Princ!$C$50:DESREF(Princ!$C$50;CONTARA(Princ!$C$50:Princ!$C$450)-1;0)
Entendiendo que tu lista base de validación está en la hoja "Princ" desde la celda C50 hasta donde tenga el último valor. Por ello el rango de CONTARA es bastante extenso.
Tal vez no lo notaste pero la macro, efectivamente, ordena la lista alfabéticamente una vez incorporado el dato nuevo, aunque no lo habías solicitado originalmente.
Finalmente, para agilizar el trámite de actualización de la plantilla, incorporaría este procedimiento al grabar el archivo (es decir dentro de la macro MySave).
De todos modos, pegando este procedimiento en el módulo insertado, puedes colocar una llamada a él en donde desees o dejarla como una rutina independiente:
Sub ActXLT()
Application.ScreenUpdating = False
'abre plantilla
ChDir "C:\Mis documentos"
Workbooks.Open FileName:="C:Mails.xlt", Editable:=True
'borra lista anterior
Sheets("Lista").Select
Range("milist").Select
Selection.ClearContents
ActiveCell.Select
'copia lista nueva
ActiveWindow.ActivatePrevious
Sheets("Lista").Select
Range("milist").Copy
ActiveCell.Select
ActiveWindow.ActivateNext
'pega lista nueva sobre anterior
ActiveSheet.Paste
'Graba y cierra Plantilla
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="C:Mails.xlt", FileFormat:=xlTemplate, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
>Bueno esteban te mando por e-mail una nueva version del archivo que ajusta la macro de grabación e incorpora esta subrutina.
Un abrazo!
Fernando

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas