Thanks Dave
Will try it and let you know.

Arran

"Dave Peterson" wrote:

Maybe this would be better:

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 Application.CountA(myRng) = 0 Then
MsgBox "Add some names"
Else
If .ListIndex = 0 Then
'do nothing
Else
myRng.Cells(1).Offset(.ListIndex - 1).Copy _
Destination:=ActiveCell
.ListIndex = 0
End If
End If
End With
End Sub


Dave Peterson wrote:

I added the msgbox later, but not in the message!

If .ListIndex = 0 Then
msgbox "your message here"

Arran wrote:

Hi again Dave

Code I posted is what you gave me in 'Programming' yesterday. Everything
working perfectly OK thank you. This post is to pick up on a couple of areas
I over looked.
Your new one deals with the Formatting that was missing, but not the Msgbox
part. I wanted a Msgbox to display something like €œAdd Names to ListSheet2€�
if the user clicks the Drop down when the Input Range List is empty. List is
populated manually on a different sheet. Hope that makes sense

Thanks
Arran

Code I posted is what you gave me in 'Programming' yesterday. Everything
working perfectly OK thank yo
"Dave Peterson" wrote:

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


--

Dave Peterson

--

Dave Peterson