HELP! - Printing a sheet with values populated from dropdown box

G

Guest

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
 
T

Tom Ogilvy

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 said:
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,
Click to expand...
UK
 
K

keepITcool

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 :
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Y/N and print job sequencer 10
Summing two text boxes on a form - HELP! 1
Combo box values from another Access File 4
Populate User Form Text Box from Worksheet? 1
SQL query to display values in a Combo box 2
Populate Adjacent Cells with value after selecting from dropdown 1
Dropdown Box to Select Data from Another Sheet 2
Data from sheet, through userform to text box on the sheet. 4

Top