reposition combo box **

  • Thread starter Thread starter Atishoo
  • Start date Start date
A

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
 
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
 
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!!
 
This should do what you want (just change the "D: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: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
 
Back
Top