(code below mostly borrowed from "Excel 2002 VBA" pub. by WROX)

Using the code below, if the user is on Sheet1 and double-clicks on a cell in column Q (e.g. Q5), a combobox is inserted at Q5 and populated (see code). When the user chooses from the combobox, his choice is put into Q5 (using

.TopLeftCell.Value = .List(.ListIndex)

after which the combobox is deleted.

When I first tested (e.g in cell Q5), I went crazy because the value was somehow inserted in cell P4. (One row up and one column left.) Note that Excel's zoom setting was at 75%. When I changed the zoom to 100%, the code ran fine and the selected value was inserted (correctly) in Q5.

I used a message box to check the address of TopLeftCell and, in fact the value of that property (for the same combobox) changed when I changed the zoom setting.

Any suggestions on how I can fix this? I thought my code for the size of the combobox fit the control to the size of the cell, but must be more to this.

Thanks in advance.
Bill


<code below in module for Sheet1 >

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Columns("Q")) Is Nothing Then
Call AddDropDown(Target)
Cancel = True
End If
End Sub

< code below in Module 1 >

Sub AddDropDown(Target As Range)
Dim ddBox As DropDown
Dim vaProducts As Variant
Dim i As Integer

vaProducts = Array( _
"No Schedule At All", _
"Different results with different criteria", _
"Schedules wrong (times, train#, etc.)", _
"Schedules missing", _
"Schedules from useless stations", _
"Have to choose stations", _
"Ticket not matching", _
"Ticket missing", _
"Other")
With Target
Set ddBox = Sheet1.DropDowns.Add(.Left, .Top, .Width, .Height)
End With
With ddBox
.OnAction = "EnterProdInfo"
For i = LBound(vaProducts) To UBound(vaProducts)
.AddItem vaProducts(i)
Next i
End With
End Sub

Private Sub EnterProdInfo()
With Sheet1.DropDowns(Application.Caller)
.TopLeftCell.Value = .List(.ListIndex)
.Delete
End With
End Sub