Dropdowns and Zoom??

[BBM]

Hi All,

Can anyone help? Im using Excel 2003:

I have a spreadsheet that has a dropdown in cell b2:

[VBA]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ddBox As DropDown

ActiveSheet.DropDowns.Delete
If Not Intersect(Target, Range("B2")) Is Nothing Then
With Target
Set ddBox = DropDowns.Add(.Left, .Top, .Width, .Height)
End With
With ddBox
.OnAction = "DropDownChange"
.AddItem "One"
.AddItem "Two"
.AddItem "Three"
End With

End If
End Sub

[/VBA]

The "DropdownChange" handler takes the selected value and drops it into the underlying cell:

[VBA]

Private Sub DropDownChange()
With ActiveSheet.DropDowns(Application.Caller)
.TopLeftCell.Value = .List(.ListIndex)
End With

End Sub

[/VBA]

Trouble is though, it often puts it into the wrong cell. This appears to be down to the zoom level.
I have traced this through to ddBox.TopLeftCell <> Target! after the original "set" ...

This ones got me completely stumped, anybody know whats going on here?


Many Thanks!
BBM

[mikerickson]

If you link the dropdown to B2, that should do what you want.
The .LinkedCell could be added to the other properties at time of creation.

Or better, use Validation to create your dropdown list.