vba - sugerencias - validacion de datos en excel lista desplegable

Devuelve el texto de un cuadro desplegable en lugar del número de índice

Question

Tengo el siguiente código VBA (de MS Access 2007). El código crea un nuevo libro de trabajo y agrega un menú desplegable a una celda. Este pequeño fragmento agrega un menú desplegable a una celda en particular y le agrega algunos elementos.

Dim myRng As Range
Dim myDD As Dropdown
Set myRng = wSheet.Cells(row, col)
With myRng
    Set myDD = .Parent.DropDowns.Add(Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
    myDD.AddItem "msg1"
    myDD.AddItem "msg2"
    myDD.LinkedCell = .Parent.Cells(row, col + 2).Address(external:=True)
End With

Todo esto funciona muy bien y cuando abro la hoja de cálculo obtengo un cuadro combinado donde quiero y se muestran los elementos. Sin embargo, cuando selecciono un elemento, en el menú desplegable de Excel, la celda vinculada muestra 1 o 2 (el número de índice). Me gustaría que muestre msg1 o msg2 .

es posible?

Answer 1

Algunas opciones

Puede poner un menú desplegable de validación de datos en la celda en lugar de un objeto desplegable. Esto devuelve los resultados reales en lugar del índice. Si aún necesita una celda vinculada separada, puede poner una fórmula que simplemente copie la celda dv

Sub MakeDv()

    Dim wSheet As Worksheet
    Dim myRng As Range

    Set wSheet = ActiveSheet

    Set myRng = wSheet.Cells(row, col)
    myRng.Validation.Add xlValidateList, , , "msg1,msg2"
    wSheet.Cells(row, col + 2).Formula = "=" & myRng.Address

End Sub

Otra opción es no usar la propiedad LinkedCell y usar una macro para escribir el valor. Asigna esta macro a la lista desplegable

Sub ShowDDResult()

    Dim dd As DropDown

    Set dd = ActiveSheet.DropDowns(Application.Caller)

    ActiveSheet.Cells(row, col + 2).Value = dd.List(dd.Value)

End Sub

Eso puede no ser tan fácil si está creando la hoja de trabajo desde cero desde Access porque tendría que agregar la macro. La última opción es usar la propiedad ListFillRange para completar el menú desplegable. Coloque la lista en un rango y use una fórmula fuera de LinkedCell para sacar la fecha de la lista

Sub testdd()

    Dim wSheet As Worksheet
    Dim myRng As Range
    Dim myDD As DropDown
    Dim rList As Range
    Dim aList(1 To 2, 1 To 1) As String

    Set wSheet = ActiveSheet
    Set rList = wSheet.Range("D1:D2")

    Set myRng = wSheet.Cells(row, col)
    aList(1, 1) = "msg1": aList(2, 1) = "msg2"
    rList.Value = aList

    With myRng
        Set myDD = .Parent.DropDowns.Add(Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
        myDD.ListFillRange = rList.Address
        myDD.LinkedCell = wSheet.Cells(row, col + 2).Address
        wSheet.Cells(row, col + 3).Formula = "=INDEX(" & rList.Address & "," & myDD.LinkedCell & ",1)"
    End With

End Sub

Answer 2

Estaba tratando de encontrar una manera más ordenada de hacerlo, así que también podría rescatar esta pregunta :) Así es como he estado resolviendo este problema. Creé una matriz de los elementos con los que quiero llenar el menú desplegable. Luego, usando esta matriz, puede devolver la cadena asociada con el índice que obtiene del menú desplegable.

Primero crea una función para devolver una matriz de cadenas:

'' Returns a string array of drop down items
function dditems() as string()

    Dim array(2) As String

    array(1) = "cats"
    array(2) = "dogs"

    dditems = array

end function

A continuación, utilice este conjunto para completar su menú desplegable:

'' To populate your drop down
sub populatedd()

    dim dd As DropDown
    dim i As Integer 
    dim itemsArray() As String

    '' Create the dd object and item array
    set dd = Worksheets("Sheet1").DropDowns("Drop Down 1")
    set itemsArray = dditems()

    '' Loop through the array to populate the drop down
    for i = 1 to UBound(itemsArray)

        dd.AddItem (itemsArray(i))

    next i
end

Luego, al usar esta matriz nuevamente, puede usar el siguiente código para asociar la cadena con el índice desplegable seleccionado:

'' Get the string associated with the index
sub showDDResult()

    dim dd As DropDown
    dim itemsArray() As String

    '' Create the dd object and item array
    set dd = Worksheets("Sheet1").DropDowns("Drop Down 1")
    set itemsArray = dditems()

    '' dd.ListIndex returns index, call to array returns correct string
    MsgBox("Item selected is " & itemsArray(dd.ListIndex))
end