Cómo cambiar el valor en la lista desplegable de Excel

Cómo cambiar el valor en la lista desplegable de Excel

Tengo una columna que usa valores del conjunto predefinido (una lista desplegable), implementada usandoValidaciones de datos. Ahorasi cambio algún valor en la lista, el menú desplegable (cuando esté abierto) ofrecerá inmediatamente este nuevo valor. Sin embargo,la tabla no se actualizará automáticamente, lo que significa que algunos de los valores de la columna no serán válidos hasta que los solucione manualmente.

He visto soluciones basadas en macros/VBA, pero ¿no existe alguna forma inteligente directamente en la interfaz de usuario de Excel?

Respuesta1

Como mencioné en los comentarios, la única forma de hacerlo sería con VBA.

Aquí hay una opción. Agregué comentarios a lo largo del código. Esto supone que está utilizando un rango con nombre para la lista de validación denominada "Lista" y que está en la misma hoja que las celdas que se están validando.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant


    Set isect = Application.Intersect(Target, ThisWorkbook.Names("List").RefersToRange)
    If Not isect Is Nothing Then
        ' Get previous value of this cell
        Application.EnableEvents = False
        With Target
            vNewValue = .Value
            Application.Undo
            vOldValue = .Value
            .Value = vNewValue
        End With

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                If .Validation.Type = 3 And .Validation.Formula1 = "=List" And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell
        Application.EnableEvents = True
    End If
End Sub

También puedes descargar elhoja de cálculo de ejemploMe reuní para probar esto. (¡Contiene macros!)

Respuesta2

¿Pero no existe alguna forma inteligente directamente en la interfaz de usuario de Excel?

Creo que conozco uno, al menos parece satisfacer todas tus peticiones:

  1. Necesitas configurarRegión nombrada que cambia dinámicamentecomo fuente para la Validación de Datos. Esto se puede lograr usando OFFSETla función. Suponiendo que tiene la lista de valores para el cuadro desplegable en la columna A de Sheet1(el nombre solo importa para la fórmula), la celda A1tiene un encabezado, por ejemplo List of values, y los valores se colocan al principio A2y debajo, debe hacer lo siguiente: vaya a la cinta Formulas > Name Manager, cree una nueva región ( llamémoslo Items) y establezca su región NO apuntando a las celdas, sino usando la siguiente fórmula: =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1).
  2. ColocarValidación de datosreglas: en lugar de apuntar al rango de celdas de la Lista, escriba =Items; de este modo, su región nombrada se utilizará como fuente de elementos de la lista.
  3. Como resultado de lo anterior, recibirá una lista verdaderamente dinámica que cumple con todos sus requisitos: puede cambiar/agregar elementos libremente a la columna A, y estos cambios se reflejarán inmediatamente en el menú desplegable la próxima vez que la use. Al mismo tiempo, los viejos valores permanecerán intactos.

Utilizo esta solución durante aproximadamente 2 años en mi trabajo. ¡Espero que también te resulte útil!

PD: aquí está el archivo de muestra real:Menú desplegable dinámico

información relacionada