I have the following VBA code (from MS Access 2007). The code creates a new workbook and adds a dropdown to a cell. This small snippet adds a drop down to a particular cell and the adds some items to it.
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
This all works great and when I open the spreadsheet I get a combo box where I want and the items are displayed. However when I select an item the from the drop down in Excel the linked cell shows 1 or 2 (the index number). I would like it to show either msg1 or msg2.
Is this possible?
A few options.
You could put a data validation drop down in the cell rather than a Dropdown object. This returns the actual results rather than the index. If you still need a separate linked cell, you can put a formula that simply copies the dv cell
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
Another option is not to use the LinkedCell property and use a macro to write the value. Assign this macro the Dropdown
Sub ShowDDResult()
Dim dd As DropDown
Set dd = ActiveSheet.DropDowns(Application.Caller)
ActiveSheet.Cells(row, col + 2).Value = dd.List(dd.Value)
End Sub
That may not be so easy if you're creating the worksheet from scratch from Access because you'd have to add the macro. The final option is to use the ListFillRange property to fill the Dropdown. Put the list in a range and use a formula off of the LinkedCell to pull the date out of the list
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
I was trying to find a neater way of doing this so might as well ressurect this question :) Here's how I have been solving this problem. I create an array of the items I want to fill the drop down with. Then using this array you can return the string associated with the index you get from the drop down.
First create a function to return an array of strings:
' 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
Then use this array to populate your drop down:
' 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
Then by using this array again you can use the following code to get the string associate with the drop down index selected:
' 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
