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