I plopped a dropdown from the Forms toolbar on a worksheet.

I assigned an input range (rightclick|format control|control tab) to that
dropdown.

Then I assigned this code to the dropdown.

Option Explicit
Sub control_on_worksheet()

Dim myDD As DropDown
Dim myRng As Range

Set myDD = ActiveSheet.DropDowns(Application.Caller)
With myDD
Set myRng = Application.Range(.ListFillRange)
If .ListIndex = 0 Then
'do nothing
Else
myRng.Cells(1).Offset(.ListIndex - 1).Copy _
Destination:=ActiveCell
.ListIndex = 0
End If
End With
End Sub


If the list contains formulas, then the formula is copied and pasted. But this
could be changed to copy|paste special|values followed by paste special|formats.



Arran wrote:

I would like to add some additional lines of code into the below. If button
is clicked and the ListIndex is empyt I wouold like a Msg box to appear
prompting for the ListIndex to be populated. Also to enable the formated
conditions of items in the ListIndex to appear in the new location.

Any help appreciated

Option Explicit
Sub control_on_worksheet()
Dim myPick As Long
Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(Application.Caller)
With myDD
myPick = .ListIndex
'activecell.value = .list(mypick)
'or to avoid an extra variable
ActiveCell.Value = .List(.ListIndex)
.Value = 0
End With
End Sub

--

Dave Peterson