Print function selecting wrong Type of Change option excel vba

by

OP says:

When I select “Return from leave” under the drop down “Type of Change”, my print macro prints as a “salary” change type, not “return from leave”

Assuming that the

“salary” change type

corresponds to the “default print” i.e.:

ws.PageSetup.PrintArea = ActiveWorkbook.Worksheets("Form Lists").Range("i2")

It seems that the reason the code provided always prints the default range, it’s because the lines that determine the printed output are validating the ActiveSheet.Name instead of the value in the “Type of Change field and print”

Solution proposed:
Change these lines to reflect the cell where the “Type of Change field and print” is located:

Replace ActiveSheet.Name with the corresponding cell.address i.e.: F10 and update as required the comparisons against “v1.20” and “v1.21”

If Right(ActiveSheet.Name, 5) = "v1.20" _
    Or Right(ActiveSheet.Name, 5) = "v1.21" _
        Or str = "Corporate" Then
            ws.PageSetup.PrintArea = ActiveWorkbook.Worksheets("Form Lists").Range("H2")

Else
    ws.PageSetup.PrintArea = ActiveWorkbook.Worksheets("Form Lists").Range("i2")

End If

It should become (comparison values shown as a reference, they should be updated in line with the choices in the drop-down list) :

If ActiveSheet.Range("F10").Value2 = "Return from leave" _
    Or str = "Corporate" Then
        ws.PageSetup.PrintArea = ActiveWorkbook.Worksheets("Form Lists").Range("H2")

Else
    ws.PageSetup.PrintArea = ActiveWorkbook.Worksheets("Form Lists").Range("i2")

End If

Note:
1. Avoid using ActiveWorkbook and ActiveSheet, suggest to replace all instances of them by: ThisWorkbook and datasheet respectively.
2. Additionally, I would suggest to review and incorporate the use of With statement and Select Case statement throughout your procedure.

More Related Contents:

  1. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  2. What is the difference between .text, .value, and .value2?
  3. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  4. Check if the file exists using VBA
  5. How can I create a progress bar in Excel VBA?
  6. Compare double in VBA precision problem
  7. Row count on the Filtered data
  8. Excel VBA Macro: User Defined Type Not Defined
  9. Automate IE via Excel to fill in a dropdown and continue
  10. Run-Time error 1004 Excel 2013
  11. VBA rounding problem
  12. Pasting an Excel range into an email as a picture
  13. Excel VBA to Export Selected Sheets to PDF
  14. Controlling Internet Explorer local intranet using Excel VBA
  15. 2 ways for “ClearContents” on VBA Excel, but 1 work fine. Why? [duplicate]
  16. Excel 2013 VBA Clear All Filters macro
  17. Rounding in MS Access
  18. Count unique values in Excel
  19. Get length of array?
  20. Bad data selection using VBScript , on date field values
  21. Sum function in VBA
  22. Find last row in range
  23. Differences between Excel’s Form Controls & ActiveX Controls
  24. How to save Excel Workbook to Desktop regardless of user?
  25. How to add an attachment to an email using VBA in Excel
  26. How to change Format of a Cell to Text using VBA
  27. Excel VBA Run-time error ’13’ Type mismatch
  28. ThisWorkbook.FullName returns a URL after syncing with OneDrive. I want the file path on disk
  29. SAP Extract into EXCEL via VBA out of memory
  30. reading entire text file using vba

Leave a Comment