Gebeurtenismacro om Excel-koptekst te wijzigen - Excel-tips

Donna uit Missouri vroeg:

Heeft u enig idee hoe ik het pad kan krijgen waar het document is gearchiveerd, aangegeven in de kop- of voettekst - of waar dan ook in het document. Ik kan de bestandsnaam krijgen met & f, maar ik kan niet achterhalen hoe ik het pad moet maken.

Allereerst begrijp ik dat Microsoft deze functionaliteit heeft toegevoegd aan Excel XP en ik bied ze complimenten omdat het een veel voorkomend probleem is. Alle lezers die al een upgrade naar Office XP hebben uitgevoerd, hebben de technieken in de tip van deze week niet nodig om dit probleem op te lossen, maar ze zullen nog steeds nuttig zijn om andere problemen op te lossen.

De oplossing voor deze tip is een speciaal type macro, een gebeurtenishandlermacro. We nemen de controle over Excel elke keer dat het op het punt staat onze werkmap af te drukken en het huidige pad aan de koptekst toe te voegen.

Veel Excel-gebruikers hebben zich verdiept in het opnemen van eenvoudige macro's. De macro's worden opgeslagen in een module genaamd Module1 of Module2 en worden onderdeel van uw project. Vandaag ga ik event handler macro's bespreken. Deze macro's bevinden zich op een speciale codemodule die is gekoppeld aan elk werkblad of werkmap.

Eerdere tips zoals de Enter Excel Time Without the Colon-tip hebben betrekking op de Worksheet_Change-gebeurtenis. De tip van vandaag vereist dat we wat code toevoegen aan de BeforePrint-gebeurtenis van Workbook.

Code die aan een evenement is toegevoegd, wordt uitgevoerd wanneer dat evenement wordt "geactiveerd". In dit geval, telkens wanneer de Excel-werkmap wordt afgedrukt, voordat het afdrukken begint, geeft Excel de controle over aan de VBA-code en staat alles dat u kunt specificeren in VBA-code automatisch toe voordat het wordt afgedrukt.

Ik ga ervan uit dat u nog niet bekend bent met event handler macro's. Ik ga precies doorlopen hoe je op de juiste plek komt om deze macro in te voeren.

Ik heb een werkmap met de naam 'Tip055 Sample.xls'. Nu de werkmap in Excel is geladen, ga ik naar alt = "" + F11 om de visuele basiseditor te starten. Het standaard uiterlijk van de editor is zoals rechts wordt weergegeven. Aan de linkerkant ziet u meestal een projectvenster gestapeld bovenop een eigenschappenvenster. Het grootste deel van de rechterkant van het scherm bevat een codepaneel. Als u geen macro's in uw werkmap heeft, is uw codepaneel grijs, zoals rechts wordt weergegeven.

Ik heb blauwe cursieve scriptwoorden aan de afbeelding toegevoegd om de drie panelen te identificeren - u zult deze niet in uw voorbeeld zien.

Het is belangrijk dat u het projectvenster in de VB-editor kunt zien. Als uw weergave van de VB-editor het projectvenster niet bevat, drukt u op Ctrl + R om het projectvenster te bekijken. Of klik op het onderstaande werkbalkpictogram:

Het projectvenster toont een project voor elke geopende Excel-werkmap en elke geïnstalleerde invoegtoepassing. Klik op het grijze plusje naast de naam van uw werkmap om het project voor uw werkmap uit te vouwen. Klik vervolgens op het grijze plusje naast de map Microsoft Excel Objects om de objectmap uit te vouwen. U zou nu één item voor elk werkblad moeten zien en één item genaamd ThisWorkbook.

Klik met de rechtermuisknop op het item voor ThisWorkbook en selecteer Bekijk code in het pop-upmenu.

U hebt nu waarschijnlijk een groot leeg wit codepaneel aan de rechterkant van het scherm. Er zijn twee vervolgkeuzemenu's bovenaan het codepaneel met (Algemeen) en (Declaraties).

• Selecteer Werkmap in de vervolgkeuzelijst aan de linkerkant.
• De rechter vervolgkeuzelijst is nu gevuld met alle programmeerbare gebeurtenissen die aan de werkmap zijn gekoppeld. Er zijn gebeurtenissen hier die code uitvoeren telkens wanneer de werkmap wordt geopend, geactiveerd, gedeactiveerd, enz. Vandaag willen we code schrijven in de gebeurtenis BeforePrint, dus selecteer BeforePrint in de rechter vervolgkeuzelijst.

Merk op dat elke keer dat u iets selecteert in de rechter vervolgkeuzelijst, de VBA-editor de begin- en eindregel met code voor u in de codemodule schrijft. De eerste keer dat u de linker vervolgkeuzelijst wijzigt in Workbook, heeft u waarschijnlijk standaard het begin van een Workbook_Open-subroutine ontvangen. Als u geen Workbook_Open-procedure gaat schrijven, kunt u overwegen om deze lege procedure te verwijderen.

Nu, aan het schrijven van de VBA-code. Er zijn een aantal handige variabelen die u kunt gebruiken.

• ActiveWorkbook.Path retourneert het pad van de werkmap. Het zou eruit kunnen zien als "C: My Documents MrExcel".
• ActiveWorkbook.FullName retourneert het pad en de bestandsnaam van de werkmap. Het kan eruit zien als "C: Mijn documenten MrExcel Tip055 Sample.xls".

U kunt deze variabele toewijzen aan een van de volgende 6 posities:

Worksheets("Sheet1").PageSetup.LeftHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.CenterHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.RightHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.LeftFooter = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.CenterFooter = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.RightFooter = ActiveWorkbook.FullName

Hier zijn drie mogelijke voorbeeldmacro's.

Bij deze macro worden het pad en de bestandsnaam toegevoegd als de rechtervoettekst van het actieve werkblad:

Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName End Sub

Bij deze macro wordt het pad toegevoegd als de linkerkop van Sheet1 en als de middelste voettekst van Sheet2:

Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.LeftHeader = ActiveWorkbook.Path Worksheets("Sheet2").PageSetup.CenterFooter = ActiveWorkbook.Path End Sub

Als u de neiging hebt om de optie "Gehele werkmap" te gebruiken bij het afdrukken, zal deze versie de volledige naam als middelste voettekst aan alle bladen toevoegen:

Private Sub Workbook_BeforePrint(Cancel As Boolean) For Each Sh In ActiveWorkbook.Worksheets Sh.PageSetup.CenterFooter = ActiveWorkbook.FullName Next Sh End Sub

Volg een van deze voorbeelden of maak er zelf een. Als u klaar bent, sluit u de VBA-editor met Bestand> Sluiten en terugkeren naar Microsoft Excel.

Elke keer dat u een werkblad afdrukt, wordt de code uitgevoerd en wordt het huidige pad ingevoegd in de juiste kop- of voettekst die u in de VBA-code hebt aangegeven.

Enkele opmerkingen en waarschuwingen:

• Beginnende Excel-gebruikers zullen weinig idee hebben dat deze code in de werkmap staat. Wanneer ze de werkmap openen, krijgen ze mogelijk de beveiligingswaarschuwing dat het bestand macro's bevat, maar er zal geen waarschuwing zijn wanneer de VBA-code alles wat ze hadden als middelste voettekst mept en de padnaam daar plaatst. Dit kan leiden tot brandend maagzuur. Stel je voor dat over 5 jaar iemand je werkmap gebruikt en de nieuwe manager wil dat de bestandsnaam van de middelste voettekst naar de rechtervoettekst gaat. Deze persoon weet misschien dat hij de instellingen in Bestand> Pagina-instelling handmatig moet wijzigen, maar als hij niet weet dat de code er is, wordt hij er gek van, omdat de code voortdurend zijn voettekst terugzet.

  It is really unlikely that they will be able to find your code module, but just in case they do, you might want to leave a comment in the code module that directs them back to this website for an explanation.

  You also might want to add a comment to cell A1 reminding yourself that there is an event handler set up to change the print headers.

• There is a setting within VBA which prevents events from running. If your macro suddenly stops working, t is common to find that something within VBA has turned this setting to prevent the events from running. The common scenario is that a programmer starts a macro with:

  Application.EnableEvents = False '… macro here… Application.EnableEvents = True

  Als er een fout optreedt in de macro, worden de gebeurtenissen nooit meer ingeschakeld. Als dit gebeurt, is er weinig waarschuwing. Als u vermoedt dat uw gebeurtenishandlers niet worden uitgevoerd, gaat u naar de visuele basiseditor. Druk op Ctrl + g om een ​​onmiddellijk venster te openen. Typ in het directe deelvenster:

  Print Application.EnableEvents

  en druk op enter. Als u merkt dat dit is ingesteld op False, typt u de volgende regel in het directe deelvenster:

  Application.EnableEvents = True

  en druk op enter.

Met dank aan Donna voor een geweldige vraag. Tijdens het uitleggen van het antwoord was het een geweldige kans om het concept van Event Handlers in VBA uit te breiden.