Move a drop down via VBA?
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 |
Move a drop down via VBA?
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. -- Regards, Tom Ogilvy Mat wrote in message ... 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 |
Move a drop down via VBA?
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 ;) -- Patrick Molloy Microsoft Excel MVP ---------------------------------- "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy Mat wrote in message ... 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 |
All times are GMT +1. The time now is 08:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com