BBM
07-14-2008, 03:10 AM
Hi All,
Can anyone help? Im using Excel 2003:
I have a spreadsheet that has a dropdown in cell b2:
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
The "DropdownChange" handler takes the selected value and drops it into the underlying cell:
Private Sub DropDownChange()
With ActiveSheet.DropDowns(Application.Caller)
.TopLeftCell.Value = .List(.ListIndex)
End With
End Sub
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
Can anyone help? Im using Excel 2003:
I have a spreadsheet that has a dropdown in cell b2:
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
The "DropdownChange" handler takes the selected value and drops it into the underlying cell:
Private Sub DropDownChange()
With ActiveSheet.DropDowns(Application.Caller)
.TopLeftCell.Value = .List(.ListIndex)
End With
End Sub
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