then try:

Sub LoopTheValListProps()
Dim v, i&, d%
With Range("a1")
On Error Resume Next
With .Validation
If .Type = 3 Then v = Evaluate(.Formula1)
End With
d = UBound(v, 2)
If d = 1 Then v = Application.Transpose(v)
For i = LBound(v) To UBound(v)
If Len(v(i)) 0 Then
.Value = v(i)
Call PrintMe
End If
Next
End With
End Sub

Sub PrintMe()
MsgBox Range("A1")
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Co-op Bank wrote :

Sorry its from a validation drop down box. cheers

"Tom Ogilvy" wrote:

Is the dropdown box from an autofilter? If so, there is no way to
access that list. You can build a list you can loop through by
using the Advanced filter with the unique option.

If it is from data validation, then just use the source data you
used to populate the list.

--
Regards,
Tom Ogilvy

"Co-op Bank" wrote in message
...
Hello,

I have a frustrating problem, I have a dropdown box in Excel
which holds around 1,000 numbers (cost centres). When I select a
number the values on
the
spreadsheet change to those respective to the selected cost
centre (i.e. sales £, volume).

What I would like is a macro to loop through every cost centre in
the dropdown box and print for all 1,000 selections in the
dropdown. Is this possible? Otherwise i'd have to go through them
all one by one to print
them
off.

Any help much appreciated. Regards Brian Taylor, Co-op Bank,
Manchester,
UK