[VBA] Event aan WorkSheet toewijzen in een Macro

[RwD]

Ik ben een Excel bestand aan het maken voor het bijhouden van wedstrijdgegevens en voor iedere rubriek maak ik met een macro een nieuwe sheet aan in het actieve Excel Workbook. Nu had ik gevonden dat het heel handig was om een "Worksheet_Change" event te maken om bij het wijzigen van een aantal cellen een macro uit te voeren. Met deze macro's verberg ik kolommen en pas ik gegevens aan die wel/niet horen bij de gekozen waarde in de aangepaste cel.

Zoals ik al zei maak ik met een macro de nieuwe sheet aan. Ik voeg met diezelfde macro dus ook alle teksten en formules toe. Het enige dat met niet lukt is om zo'n change event toe te voegen.

Na wat zoeken vond ik dit op MSDN, waarin getoond wordt hoe ik een Macro programmatisch toe kan voegen: How To Create and Call an Excel Macro Programmatically from VB. Dit krijg ik echter niet werkend voor de "Worksheet_Change" functie. Het probleem is dat ik de specifieke module niet benaderd krijg en ook niet weet waar ik hem vinden kan omdat alles na "ActiveWorkbook.VBProject" beveiligd lijkt ("ActiveWorkbook.VBProject.Name" geeft bijv een melding dat de toegang hiertoe "not trusted" is)

Hier vond ik maar 1 topic dat een beetje helpt: [VBA] Excel eventhandler tijdens runtime toewijzen. Hierin wordt ook verwezen naar een beveiligingsinstelling, maar deze kan ik onmogelijk op iedere pc uitschakelen aangezien ik dit bestand feitelijk weg geef aan iedereen die het wil gebruiken.

Nu ben ik gelukkig wel slim genoeg om een andere manier te gebruiken om het gewenste resultaat te krijgen; ik kopieer een lege sheet waarbij ik de events al heb ingesteld. Hierdoor kan ik waarschijnlijk zelfs een andere macro aanroepen zodat ik het alsnog programmatisch aan kan passen. Enige jammere is dat ik dus een (verborgen) sheet moet hebben. En het maakt het ook nog een beetje vreemd om uberhaupt met een macro een sheet te vullen omdat ik bijna alle formules al in die sheet er bij kan zetten.

Samengevat
Hoe voeg ik in Excel met een macro het "Worksheet_Change" event toe aan een willekeurige sheet in mijn Workbook?

ps, vragen naar code die ik al heb heeft geen zin omdat hetgeen ik geprobeerd heb nooit voor meer dan 1 regel werkte (Wel heb ik de code uit de links hierboven)

[Verwijderd]

deze instelling kan gewijzigd worden via tools->macro->security->trusted publishers->trust access to visual basic project. als dit is aangevinkt, kan code programmatorisch aan het vb project toegevoegd worden.
als dit - blijkbaar - geen optie is, dan zal je via een gebeurtenisafhandelaar in een klasse module (event class module) de code dynamisch toewijzen aan een sheet_change gebeurtenis. als je daar niet mee verder raakt zal ik later wel wat relevante code posten.

[RwD]

De eerste die je noemt had ik al gevonden, bij Excel 2007 zit deze trouwens niet op dezelfde plaats maar In het Trust center bij de Macro settings.

Ik heb nog gekeken naar de class module en de classes die ik dus toe kan voegen. Het zal nu wel te laat zijn want ik zie niet meteen hoe ik hier een eventhandler voor een worksheet kan toevoegen tenzij dat mijn klasse een worksheet "extend" en ik daarom ook de event handlers toe kan voegen.

Een voorbeeld zoals je zegt zou ik best fijn vinden (hoeft geen exacte oplossing te zijn zolang het maar duidelijk is hoe ik een event handler kan toevoegen aan/voor een sheet die ik in een macro aan maak)

Maar nu eerst: bedtijd

[Boss]

Ik weet niet of het een optie is voor je, maar als je voor verschillende categorieen steeds een nieuwe sheet aanmaakt, klinkt het alsof je beter met een database (zoals Access) aan de slag kan gaan. Wat als je nu het ontwerp van je 'template' wilt aanpassen? Ga je dan 1 voor 1 al je sheets af om het door te voeren?

[Creepy]

Excel en VBA hoort thuis in Officesuites en -software. Zie ook Waar hoort mijn topic?

[RwD]

@Creepy:
In principe met je eens, mijn keuze was gebaseerd op het door mij aangehaalde topic dat bijna om exact hetzelfde vraagt en in de door mij gekozen locatie staat. De huidige plaats van dat topic zal destijds vast niet bestaan hebben? Dat krijg je er van als de oude topics niet verplaatst worden

Boss schreef op vrijdag 18 mei 2007 @ 08:25:
Ik weet niet of het een optie is voor je, maar als je voor verschillende categorieen steeds een nieuwe sheet aanmaakt, klinkt het alsof je beter met een database (zoals Access) aan de slag kan gaan. Wat als je nu het ontwerp van je 'template' wilt aanpassen? Ga je dan 1 voor 1 al je sheets af om het door te voeren?

Nou, ik hoef de sheets niet één voor één af te gaan omdat ik ze genereer met een macro. De layout en de werking zijn namelijk alleen relevant tijdens de wedstrijd. Hierna wordt het Excel bestand opgeslagen en hooguit nog bekeken om de stand van toen te zien of om uit te printen. De minimaal nodige houdbaarheid is zeg maar hooguit een weekend. Bovendien hoeft iedere sheet maar voor één onderdeel gebruikt te worden en in een ongunstig geval is dat drie uur. En tenslotte; er lopen incidenteel mensen voorbij die wel iets kunnen met Excel dus als ik een fout zou hebben gemaakt of de sheet is ergens niet toereikend dan zouden er nog aanpassingen gemaakt kunnen worden. Dit laatste is zeker niet onhandig; ik heb een dergelijk probleem gehad op een wedstrijd waarbij ik handmatig zo'n zelfde soort sheet heb aangepast om bij het type wedstrijd te kunnen passen aangezien de juiste sheet hiervoor niet aanwezig was...

Aanpassingen die ik maak zijn dus beschikbaar vanaf de volgende wedstrijd.

Ik ben nog even aan het denken over _heretic_'s antwoord. Hij klinkt alsof hij zeker is dat het op die manier mogelijk is, dus daar ga ik nog even naar zoeken... (Maar hulp nog steeds welkom)

[Verwijderd]

bij deze de code om een klassemodulegebeurtenisafhandelaar op te zetten:
maak een nieuwe klassemodule aan genaamd WSChangeEventHandler met de volgende code:

Visual Basic:

Option Explicit

Public WithEvents WorkSheetChangeHandler As Excel.Application

Private Sub WorkSheetChangeHandler_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  MsgBox Sh.Name
  'select case sh.name
    'case "sheet1"
      'voer code uit voor sheet1
    'case "sheet2", "sheet4"
      'voer code uit voor sheet2 & 4
    'case "sheet3"
      'voer code uit voor sheet3
  'end select
  'enz...
End Sub

maak een gewone module aan met eender welke naam die de klasse zal instantiëren en zodoende de gebeurtenisafhandelaar in het leven roept:

Visual Basic:

Option Explicit

Public clsEventHandler As WSChangeEventHandler

Public Sub GebeurtenisAfhandelaarInitialiseren()
  Set clsEventHandler = New WSChangeEventHandler
  Set clsEventHandler.WorkSheetChangeHandler = Excel.Application
End Sub

zoals je zoet kan je met select case of andere tests op het sh object & target object in de klasse module de code per sheet laten uitvoeren zoals gewenst.

[RwD]

Dat was exact waar ik naar zocht. Ik had bijna gelijktijdig (maar iets later) dan het lezen van dit ook een link op MSDN gevonden waarbij ze iets dergelijks uitleggen (namelijk: How to create application-level event handlers in Excel). Maar jouw hulp is iets preciezer wat ik nodig heb, daarmee kom ik er makkelijk uit.

Tips voor mensen die dit lezen:

• De GebeurtenisAfhandelaarInitialiseren functie kan automatisch uitgevoerd worden door deze aan het open event van het Workbook toe te voegen.
• Let op dat de class module ook daadwerkelijk "WSChangeEventHandler" genoemd moet worden. _heretic_ zegt dat wel, maar hij meent het ook! (ging bij mij 5 minuten lang fout )

_heretic_ bedankt!

[RwD]

Ok, en voor de volledigheid dan maar:

De code die _heretic_ postte was op zich zelf eigenlijk vrij nutteloos aangezien het makkelijker is die code handmatig toe te voegen aan het Workbook. Maar de code die _heretic_ postte was waardevol omdat ik op basis hiervan de code wist te maken waarmee ik een event kon toevoegen aan nieuw gecreeerde sheets. hierbij de code die ik er van gemaakt heb:

In class module "WSChangeEventHandler"

Visual Basic:

Option Explicit

Public WithEvents WSChangeEventHandler As Worksheet
Private Sub WSChangeEventHandler_Change(ByVal Target As Range)
  MsgBox Target.Value
End Sub

In module:

Visual Basic:

Option Explicit

Public clsEventHandler As WSChangeEventHandler

Public Sub InitializeEventHandlers()
  Dim newWorkSheet As Worksheet
  Set newWorkSheet = Sheets.Add
  Set clsEventHandler = New WSChangeEventHandler
  Set clsEventHandler.WSChangeEventHandler = newWorkSheet
End Sub

Let op dat ik in dit voorbeeld maar even een sheet aanmaak...

Het enige dat ik nu nog voor elkaar moet krijgen is dat deze event handlers ook blijven nadat het workbook gesloten word. Bij het opnieuw openen van het bestand zijn ze namelijk weg en niet iedere sheet heeft de handlers nodig.... Kan deze code niet permanent toegevoegd worden aan een sheet (of code die automatisch de juiste handlers toekent bij het openen)

Naar tevredenheid voor mij is de volgende code waarmee het event ook bij het openen van een workbook toegekend word aan de sheets (let wel dat dit voorbeeldcode is, ik selecteer de sheets anders dan op naam)

Visual Basic:

Private Sub Workbook_Open()
    Dim oWorkSheet As Object
    For Each oWorkSheet In Application.Sheets
        If oWorkSheet.Name <> "Instellingen" Then
            Set clsEventHandler = New WSChangeEventHandler
            Set clsEventHandler.WSChangeEventHandler = oWorkSheet
        End If
    Next oWorkSheet
End Sub

[ Voor 16% gewijzigd door RwD op 18-05-2007 18:54 ]