[VBA] Loop through Data Validation and Print Output
Results 1 to 4 of 4

Thread: [VBA] Loop through Data Validation and Print Output
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default [VBA] Loop through Data Validation and Print Output

    I have the following code with which I want to loop through all the data validation options available in the drop down list and copy/paste that output of the data validation option into MS Word.

    The code "works"... However, when it pastes the report into MS Word, it pastes the last list option selected by the user instead of looping through the list and changing the report responses.

    Say, the data validation has "Option1" and "Option2".. For each of the Options there is a template/report that has different values depending on the data validation selection. When I run the code, it will copy and paste the report for Option1 or Option2 (regarding on which was last selected by the user) TWICE (because there are only two options in the dropdown)....

    Any idea how I could fix this?

    Code:
    Sub GenerateReports()
    
    Dim dvCell, inputRange, c As Range
    Dim i As Long
    Dim objWord, objDoc As Object
    
    Set dvCell = Worksheets("Reports").Range("D3")
    
    Set inputRange = Evaluate(dvCell.Validation.Formula1) 'My data validation is not a formula but a dynamic range instead
    
    Set objWord = CreateObject("Word.Application")
    
    Set objDoc = objWord.Documents.Add
    
    i = 1
    
    For Each c In inputRange
    
        dvCell = c.Value
     
        ActiveWindow.View = xlNormalView
        Range("A9:G48").Select
        Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        objWord.Visible = True
        objWord.Selection.Paste
        objWord.Selection.TypeParagraph
        
    i = I + 1
    
    Next c
    
    End Sub

  2. #2
    New Member
    Join Date
    May 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through Data Validation and Print Output

    Please help

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,830
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: [VBA] Loop through Data Validation and Print Output

    The problem lies with the way you're declaring your variables. For example, in the following declaration...

    Code:
    Dim dvCell, inputRange, c As Range
    ...since a type has not been specified for dvCell and inputRange, the variables are typed as a Variant. So, in your code, first you assign a range object to dvCell, but then you assign it a value. In doing so, dvCell changes from an object variable assigned a range object to a regular variable assigned a value. So now this means that dvCell no longer refers to the range object, which in this case is cell D3. Therefore, try declaring your variables this way...

    Code:
    Dim dvCell As Range, inputRange As Range, c As Range
    Dim i As Long
    Dim objWord As Object, objDoc As Object
    However, your code can be re-written as follows...

    Code:
    Sub GenerateReports()
    
        Dim objWord As Object
        Dim objDoc As Object
        Dim dvCell As Range
        Dim inputRange As Range
        Dim c As Range
        Dim i As Long
        
        ActiveWindow.View = xlNormalView
        
        Set dvCell = Range("D3")
        
        Set inputRange = Evaluate(dvCell.Validation.Formula1) 'My data validation is not a formula but a dynamic range instead
        
        Set objWord = CreateObject("Word.Application")
        
        objWord.Visible = True
        
        Set objDoc = objWord.Documents.Add
        
        For Each c In inputRange
            dvCell.Value = c.Value
            Range("A9:G48").CopyPicture Appearance:=xlScreen, Format:=xlPicture
            objWord.Selection.Paste
            objWord.Selection.TypeParagraph
        Next c
        
        Set objWord = Nothing
        Set objDoc = Nothing
        Set dvCell = Nothing
        Set inputRange = Nothing
    
    
    End Sub
    Hope this helps!
    Last edited by Domenic; May 29th, 2019 at 09:48 AM.

  4. #4
    New Member
    Join Date
    May 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Loop through Data Validation and Print Output

    Awesome explanation, it works now. Thanks you very much for your help!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •