problem w/ size of combobox & using TopLeftCell
 

(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


--
Bill_excelforum
------------------------------------------------------------------------
Bill_excelforum's Profile: http://www.excelforum.com/member.php...o&userid=32280
View this thread: http://www.excelforum.com/showthread...hreadid=520327