| © Matti Mattila, CPFA, CISA, CIA |
Footer & Header macro: Code #2 |
|||
| Previous item | Next item | Previous menu |
| Does our HeaderFooter Macro work? |
| ||||||||||||||||||||
| - | Yes, it does its job. A user can easily enter data in the header and in the footer of the worksheet, and change the worksheet orientation. The data once entered remains in the input form (UserForm1) as long as the Excel file (worksheets, workbook) is open. | ||||||||||||||||||||
| Problems | |||||||||||||||||||||
| - | When we close the workbook, and open it again, and run the macro, a blank input form is shown. If we enter no data in it, and click "Done", almost all header and footer information will be lost. Only name of the [update] date in the right header and name of the Workbook in left footer will be left. | ||||||||||||||||||||
| - | Another problem, related to the former, is lack of a button for interrupting header and footer update process. | ||||||||||||||||||||
| Development needs | |||||||||||||||||||||
| - | The macro needs to be improved so that text entered in the header and footer will not be erased (actually overwritten). | ||||||||||||||||||||
| - | The macro should show us the existing header and footer information. Best place for it is the input form, where the information can be easily edited, when needed. | ||||||||||||||||||||
| - | A "Cancel" button is needed in the input form to enable user to interrupt the header and footer information update process. | ||||||||||||||||||||
| - | It would be convenient, too, if the macro would give the sheet (tab) the name that we enter in left footer. | ||||||||||||||||||||
| Modification of UserForm1 | |||||||||||||||||||||
| - | We add a new button in UserForm1: CommandButton2, and give it a name "Cancel". We rename CommandButton1 ("Done") as "Do". The new UserForm1 is shown in the picture #1. | ||||||||||||||||||||
| - | We will add to the "Cancel" button the code to the right. "Cnl" is a public variable, that keeps its value available in both UserForm1 and Module1. We insert here also the line "UserForm1.Hide", because also clicking "Cancel" should hide the input form. | ||||||||||||||||||||
| |||||||||||||||||||||
| Modification of Module1 | |||||||||||||||||||||
| - | The code in red makes the "Cancel" button work. We initialize it as "False". If "Cancel" button is clicked, value of "Cnl" changes to "True". In that case the "If .. Then" statement makes execution of the macro jump to "EndOfMacro". | ||||||||||||||||||||
| - | The code in blue makes sure that there will be only two alternatives in "ComboBox1". | ||||||||||||||||||||
| - | The code in green loads the text in left header, center header, right header, left footer, center footer and right footer in the input form. From the right header only 10 first characters are loaded because creation date of the workbook is expected to be expressed with them, e.g. "dd.mm.yyyy", "mm/dd/yyyy" or "yyyy/mm/dd". | ||||||||||||||||||||
| - | The code in purple makes the macro to rename to worksheet (tab) in accoradance with the name we input in "FL". | ||||||||||||||||||||
