Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Probeer onze RefTreeAnalyser
de beste Excel formule auditing tool.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > Macro Verspreiden > Toolbars
This page in English

Excel-macro's geschikt maken voor anderen.

Taakbalken

Een veelgebruikte methode om toegang te verschaffen tot macro’s is via taak- of werkbalken.

Het maken van een nieuwe werkbalk kan wederom via het menu Beeld>>Werkbalken>>Aanpassen. Op het tabje Taakbalken van het dialoogvenster "Aanpassen" kan door op de knop Nieuw te klikken een nieuwe werkbalk worden gemaakt. Excel zal eerst vragen naar de naam van de nieuwe werkbalk en vervolgens een werkbalk zonder knoppen laten verschijnen. Let op: deze gaat soms verscholen achter het dialoogvenster en kan zichtbaar gemaakt worden door dat venster even te verslepen.

Hoewel werkbalken ook lokaal opgeslagen worden, is er één verschil met aanpassingen aan menubalken. Zelfgemaakte werkbalken kunnen namelijk aan een werkmap worden gekoppeld.

Dit gaat als volgt: Rechtsklikken op werkbalk, Aanpassen kiezen, dan Toevoegen klikken (zie afbeelding 7). Doe dit pas als de werkbalk helemaal klaar is.

Afbeelding 7, Dialoogvenster werkbalk toevoegen aan werkmap

Mocht de werkmap al een werkbalk bevatten met dezelfde naam (deze is dan aan de rechterkant van het dialoogvenster te zien), verwijder deze dan eerst.

Wat gebeurt er nu als het bestand met de werkbalk geopend wordt:

Als het bestand wordt geopend, kijkt Excel of er een werkbalk in het systeem staat met dezelfde naam. Zo ja, dan doet Excel niets en wordt de "systeem" balk gebruikt. Zo nee, dan wordt de balk van de werkmap gekopieerd naar het systeembestand (excel10.xlb).

Als de werkmap waarin de macro’s staan waarnaar de werkbalk verwijst onder een andere naam wordt opgeslagen (Bestand, Opslaan als...), dan zal Excel de verwijzingen (naar de macro’s) automatisch aanpassen naar het nieuwe bestand. Dit is een oorzaak van veel verwarring. Als het nieuwe bestand namelijk wordt gesloten en het oude weer wordt geopend, dan verwijzen de knoppen op de werkbalk naar de macro’s in het nieuwe bestand. Zodra op één van de knoppen wordt gedrukt, zal Excel het nieuwe bestand openen. Om deze verwarring te voorkomen en om ervoor te zorgen dat de werkbalk actueel blijft, moet de werkmap worden voorzien van code die bij het sluiten van de werkmap de werkbalk weghaalt (deze wordt dan niet verwijderd uit het bestand maar alleen uit het systeem bestand). Bij herladen wordt dan de balk uit het bestand weer gebruikt (zie listing 2).

Listing 2

(In de Thisworkbook module)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("JouwWerkbalk").Delete
End Sub

Natuurlijk zal het gelijktijdig openen van twee verschillende bestanden met dezelfde werkbalk dan het probleem geven dat slechts de werkbalk van het eerste bestand aanwezig zal zijn. Sluiten van één van beide bestanden zorgt dan tevens voor verwijdering van de werkbalk en dus resteert een bestand zonder "besturing". Sluiten en weer openen van het betreffende bestand is dan de enige remedie. Een dergelijke situatie kan zich voordoen wanneer men verschillende bestanden heeft met dezelfde macro’s en werkbalk(en). Het kan dan verstandig zijn de macro’s te scheiden van de data en bijvoorbeeld in een invoegtoepassing te plaatsen.

Een andere mogelijkheid is het creëren van de werkbalk middels VBA code (zie listing 3):

Listing 3

Option Explicit

Sub RemoveBar()
    On Error Resume Next
    Application.CommandBars("xlUtilDemo1").Delete
End Sub

Sub CreateBar()
    Dim oBar As CommandBar
    Dim oControl As CommandBarControl
    RemoveBar
    Set oBar = Application.CommandBars.Add
    oBar.Name = "xlUtilDemo1"
    oBar.Visible = True
    Set oControl = oBar.Controls.Add(ID:=1, Before:=1)
    oControl.OnAction = "ButtonClicked"
    oControl.FaceId = 275
    oControl.Caption = "Click me!"
    Set oControl = Nothing
    Set oBar = Nothing
End Sub

Sub ButtonClicked()
    MsgBox "Je knop werkt!!!"
End Sub

De Sub Createbar maakt 1 werkbalk aan met daarop 1 knopje. Klikken op dat knopje zorgt ervoor dat het programmaatje ButtonClicked gestart wordt.

Om ervoor te zorgen dat de werkbalk wordt gemaakt bij openen en weer verdwijnt bij sluiten van het bestand kan de code uit listing 4 opgenomen worden in de "Thisworkbook" module:

Listing 4

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveBar
    RemoveMenu
End Sub

Private Sub Workbook_Open()
    CreateBar
    MakeMenu
End Sub

Deze methode heeft een nadeel. Als de gebruiker Excel gaat sluiten, maar op annuleren klikt zodra Excel vraagt of de bestanden opgeslagen moeten worden, dan is bovenstaande Workbook_BeforeClose subroutine al uitgevoerd en de knoppenbalk (en het menu) dus verdwenen. Dit kan als volgt worden voorkomen:

Definieer in een normale module een publieke variabele:

Public bMeClosing As Boolean

En verander de code in de thisworkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveBar
    RemoveMenu
    If Not bMeClosing Then
        Application.OnTime Now, "Createbar"
        Application.OnTime Now, "MakeMenu"
    End If
End Sub

Hoe werkt dit nu. Als de gebruiker zelf Excel sluit zal de variabele bMeClosing onwaar zijn en wordt een Ontime event ingesteld, welke na afloop van alle event macro’s (in dit geval het Workbook_BeforeClose event) zal starten. Omdat Excel echter sluit gebeurt dit niet en is de werkbalk netjes verwijderd. Indien de gebruiker echter besluit het sluiten te annuleren wordt alsnog de macro "Createbar" gestart en keert de werkbalk terug. Als de utility zelf gesloten moet worden (bijvoorbeeld wanneer de gebruiker besluit de invoegtoepassing uit te schakelen), dan is het natuurlijk gewenst dat de werkbalk niet weer terugkeert. Zet hiertoe eerst de variabele bMeClosing op Waar:

bMeClosing = True

en sluit dan het bestand, bijvoorbeeld via

ThisWorkbook.Close SaveChanges:=False

Overigens kan door deze methode het voorbeeld bestand alleen gesloten worden door op de knop "Bestand sluiten" te klikken op het werkblad Blad1.