Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent-looping through data validation list then printing as per each value
Hey all,
I feel like this is a simple issue but am trying to get my head around using VBA. I need a code that loops thorugh a data validation list and as it selects each value in the list, prints out the active worksheet. (guessing some sort of loop is needed)sorry for sounding like such a beginner. thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent-looping through data validation list then printing as per each value
The following code should get you started. It assumes that
Worksheets(1).Range("A1") has a List type validation with the list pointing to a range of cells. There is no error check to ensure that this is true. Sub AAA() Dim DV As Validation Dim S As String Dim DVRng As Range Dim R As Range Set DV = Worksheets(1).Range("A1").Validation S = DV.Formula1 S = Mid(S, 2) Set DVRng = DV.Parent.Worksheet.Range(S) For Each R In DVRng DV.Parent.Value = R.Value DV.Parent.Worksheet.PrintOut Next R End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "kidkarma" wrote in message oups.com... Hey all, I feel like this is a simple issue but am trying to get my head around using VBA. I need a code that loops thorugh a data validation list and as it selects each value in the list, prints out the active worksheet. (guessing some sort of loop is needed)sorry for sounding like such a beginner. thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent-looping through data validation list then printing as per each value
If the data validation list is from a range on the sheet and you don't know
what it is (if you do, just loop through that). In each case, assume I5 is the cell with the data validation: Sub LoopListRange() Dim as String, rng as Range, cell as Range s = Evaluate(Activesheet.Range("I5").Validation.Formul a1) set rng = Activesheet.Range(s) for each cell in rng Activesheet.Range("I5").Value = cell.Value Activesheet.Printout Next End Sub If the list is hand entered such as John,Jack,Jill,Mary Sub LoopListArray() Dim s as Variant, i as Long s = Split(Activesheet.Range("I5").Validation.Formula1, ",") for i = lbound(s) to ubound(s) ActiveSheet.Range("I5").Value = s(i) ActiveSheet.Printout Next End Sub -- Regards, Tom Ogilvy "kidkarma" wrote in message oups.com... Hey all, I feel like this is a simple issue but am trying to get my head around using VBA. I need a code that loops thorugh a data validation list and as it selects each value in the list, prints out the active worksheet. (guessing some sort of loop is needed)sorry for sounding like such a beginner. thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent-looping through data validation list then printing as per each value
correction on the first one
Sub LoopListRange() Dim rng as Range, cell as Range Set rng = Evaluate(Activesheet.Range("I5").Validation.Formul a1) for each cell in rng Activesheet.Range("I5").Value = cell.Value Activesheet.Printout Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... If the data validation list is from a range on the sheet and you don't know what it is (if you do, just loop through that). In each case, assume I5 is the cell with the data validation: Sub LoopListRange() Dim as String, rng as Range, cell as Range s = Evaluate(Activesheet.Range("I5").Validation.Formul a1) set rng = Activesheet.Range(s) for each cell in rng Activesheet.Range("I5").Value = cell.Value Activesheet.Printout Next End Sub If the list is hand entered such as John,Jack,Jill,Mary Sub LoopListArray() Dim s as Variant, i as Long s = Split(Activesheet.Range("I5").Validation.Formula1, ",") for i = lbound(s) to ubound(s) ActiveSheet.Range("I5").Value = s(i) ActiveSheet.Printout Next End Sub -- Regards, Tom Ogilvy "kidkarma" wrote in message oups.com... Hey all, I feel like this is a simple issue but am trying to get my head around using VBA. I need a code that loops thorugh a data validation list and as it selects each value in the list, prints out the active worksheet. (guessing some sort of loop is needed)sorry for sounding like such a beginner. thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation list: how do i 'force' a user to enter data from the list? | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Programming | |||
urgent. looping thru all records and applynig formula to get new results | Excel Programming |