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


"Atishoo" wrote in message
...
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

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)" wrote:

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" wrote in message
...
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

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


"Atishoo" wrote in message
...
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)" wrote:

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" wrote in message
...
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