Move a drop down via VBA?

  • Thread starter Thread starter Mat
  • Start date Start date
M

Mat

I need to create a drop down in vba. I can do this, but
later it needs to be moved, but it MUST keep it's original
value. Is there a way I can do this? Is there a function
specifically for moving objects? Or am I going to have to,
somehow, get the selected value of the drop down, then
delete it, and re create it at i's new co-ordinates?
Thanks for your help.
Mat
 
If it is a shape, a shape has top, left, width and height properties.

Sub AAA()
With ActiveSheet.Shapes("Drop Down 1")
.Top = Range("B9").Top
.Left = Range("B9").Left
End With

End Sub


as an example placed the dropdown on top of Cell B9. This is a dropdown
from the forms toolbar. If you are talking about data=>validation using the
List type, you will need to store the value and re-create it.
 
and if its an ActiveX control, the same is true...


Sub Moveit(Target As Range)

With Sheet1.ComboBox1
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height
End With

End Sub

If you set the sheet's SelectionChange method to call this procedure,
passing the target cell, then the combo box will "follow" the selected cell
around the sheet....very annoying ;)
 
Back
Top