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