ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Restricting what a 'print' macro should print (https://www.excelbanter.com/excel-programming/317230-restricting-what-print-macro-should-print.html)

Richard H Knoff

Restricting what a 'print' macro should print
 
I have two macros that print two sheets with diagrams and
statistics for organizational units that are designated by codes,
like "abc", "abcd", "aca", "acabd" etc. The codes are listed in a
range named "All" in a sheet named 'List'. The first macro prints
only results for units selected in the 'List' sheet. The other
macro prints results for every unit listed in the range "All". The
mechanism involves a cell named "Filter", which retrieves the
codes one by one from the selection or the range "All". Then Excel
computes results, and prints the sheets.

A cell named "Restrict" is used in a validation rule which limits
what values the "Filter" cell accepts. The validation rule goes
like this:

=(LEFT(B1;LEN(Restrict))=Restrict)

In effect, it tells the "Filter" cell not to accept any code that
doesn't begin with the letters given in the "Restrict" cell. This
works nicely when the user tries to enter values into "Filter" -
but it has no effect when she runs the print macros.

So - I need to amend the print macros to have them ignore codes
that do not comply with the validation rule. Alternatively, they
may return an error message if the user tries to "print all" or to
print a selection with an "illegal" code.

The relevant parts of the two macros appears below.

PRINT SELECTED UNITS
For Each cell_in_loop In Selection
Sheets("Diagram").Unprotect Password:="password"
Set TargetSheet = Sheets("Diagram")
TargetSheet.Range("Filter").Value = ActiveCell.Value
Sheets(Array("Diagram", "Statistics")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("List").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Next

PRINT ALL
For Each cell_in_loop In Range("All")
Sheets("Diagram").Unprotect Password:="password"
Set TargetSheet = Sheets("Diagram")
TargetSheet.Range("Filter").Value = ActiveCell.Value
Sheets(Array("Diagram", "Statistics")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("List").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Next

Would this be a simple matter??


All times are GMT +1. The time now is 09:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com