Excel

Question

I have been trying to print a document that has a drop down list so that each an every list can get printed automatically , but to no avail. I need to print out only the selected range. What should I do? Please help. A macro that I used didn't help me.

nikolinode · Answer

f_osolo75&nbsp;You can use a macro to print all items in a drop-down list.&nbsp;Here is an example of a macro that can help you print all items in a listSub PrintAll()
    Dim lCt As Long
    With ActiveSheet.DropDowns("Drop Down 4")
        For lCt = 1 To .ListCount
            .ListIndex = lCt
            If Len(.List(.Value)) &gt; 0 Then
                ActiveSheet.PrintOut
            End If
        Next
    End With
End SubYou can modify this macro to suit your needs.For example, you can change the name of the drop-down list from “Drop Down 4” to the name of your drop-down list.You can also specify the range you want to print by adding a range argument to the&nbsp;PrintOut&nbsp;method.&nbsp;&nbsp;I hope this helps!&nbsp;

f_osolo75 · Answer

Thanks. How do I add a range argument to the PrintOut method?

nikolinode · Answer

f_osolo75&nbsp;Print by adding a range argument to the PrintOut methodYou can use a loop to iterate through the items in the drop-down list and print the selected range for each item.Here’s an example that assumes the drop-down list is a data validation list in cell A1 on Sheet1 and the range to print is B2:C3 on Sheet1.Sub PrintDropDownList()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim val As Variant

Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("A1").Validation.Formula1
    rng = Right(rng, Len(rng) - 1)
    Set rng = ws.Range(rng)

For Each cell In rng.Cells
        val = cell.Value
        ws.Range("A1").Value = val
        ws.Range("B2:C3").PrintOut
    Next cell
End SubThis code sets the value of cell A1 to each item in the drop-down list and then prints the range B2:C3.You can adjust the worksheet and range references to match your specific needs.&nbsp;I hope this helps!

f_osolo75 · Answer

I tried to run the code, but it responded with an error "type mismatch". The phrase ".Formula1" was also highlighted.

nikolinode · Answer

f_osolo75&nbsp;Here is an example with step by step instracition (see the file too):Create a named range for the cells that contain the drop-down list options. To do this, select the cells that contain the options and then click on the “Formulas” tab. In the “Defined Names” group, click on “Define Name” and enter a name for the range.Create a drop-down list in a cell by going to the “Data” tab and clicking on “Data Validation” in the “Data Tools” group. In the “Data Validation” dialog box, select “List” in the “Allow” drop-down list. In the “Source” field, enter the name of the named range that you created in step 1.Create a VBA macro that uses the value of the cell with the drop-down list to determine which print area to use. Here is an example of such a macro:Sub PrintSelectedRange()'Get the value of the cell with the drop-down listDim selectedOption As StringselectedOption = Range("A1").Value'Select the print area based on the selected optionIf selectedOption = "Examination" ThenRange("C9:R86").SelectElseIf selectedOption = "score" ThenRange("W4:AB46").SelectEnd If'Set the print area to the selected rangeWith ActiveSheet.PageSetup.PrintArea = Selection.AddressEnd With'Print the selected rangeActiveSheet.PrintOutEnd SubThis macro gets the value of the cell with the drop-down list (in this example, cell A1) and uses it to determine which range of cells to select as the print area. You can modify this code to match your specific needs.&nbsp;I hope this helps!

Forum Discussion

Excel

Share

Resources