Control on worksheet
 

If you use controls from the Forms toolbar, you can assign the same procedure to
multiple controls.

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

Remember that this code goes in a General module--not behind the worksheet.

And I didn't know what the variable w did--I changed the .value to 0.


Arran wrote:

I'm new to VBA, so excuse the basic nature of this question. Thought I had
read some where you could assign the some macro to more than one control. I
have experimented with changing the code below without any success. Is it
possible, if so what corrections are required?

Many thanks
Arran

Sub control_on_worksheet()
Dim mypick As Variant
With Worksheets("April 05 - April 06").DropDowns("Drop Down 13, Drop
Down 14")
' set the value of mypick to the index number
' of the item chosen in the drop-down.
mypick = .ListIndex
'extract the actual item and put it into
'the active cell on the worksheet.
ActiveCell.Value = .List(mypick)
'empty out the drop-down.
.Value = w
End With
End Sub

--

Dave Peterson