reposition combo box **

[Atishoo]

does anyone know how to make a combo box position itself close to the active
cell each time a cell is selected??
have tried a few ideas but they all seem to take forever to execute in VBA

 

[Rick Rothstein \(MVP - VB\)]

Depending on where you got the control from, one of these
Worksheet_SelectionChange event code should do what you want...

For Forms Toolbar Drop Down Control
===================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet.Shapes("Drop Down 5")
.Top = Target.Top + Target.Cells.Height
.Left = Target.Left
End With
End Sub

For ActiveX ComboBox Control
===================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet.ComboBox1
.Top = Target.Top + Target.Cells.Height
.Left = Target.Left
End With
End Sub


Rick

 

[Atishoo]

Thanks I used the active x version and it worked brill thanks!!
Dont suppose you would know how I might make a different combo box position
itself in the same way depending on the column or range the active cell falls
within and return to a default position (off screen somewhere hidden or just
hide itself) when the active cell doesnt fall in that column or range! God
thats a long winded question!!

 

[Rick Rothstein \(MVP - VB\)]

This should do what you want (just change the "D" inside the Range
function call to the actual range you want to show the combo box for)....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet.ComboBox2
If Not Intersect(Target, Range("D")) Is Nothing Then
.Visible = True
.Top = Target.Top + Target.Cells.Height
.Left = Target.Left
Else
.Visible = False
End If
End With
End Sub

Rick