can someone solve this problem for me
I have a workbook with my own application event handler
I also have a button that first makes a chart as an object on the active sheet and shows it on a form.
What happens is this:
if I unprotect-/and protect, the sheet_change_event that has some code that unlocks and expands a table on the sheet depending on my input, won't work anymore. However the class that deals with these application_events is still active. (?myAPP is nothing)
It's got something todo with creating and deleting the chart
All the other forms that need to unprotect/protect don't have this problem.
What can I do to solve the problem??
Here is some code
Code
Dim HuidigeGrafiek As Chart
Dim Bnaam As String
'Krijg de handle van het formulier, voor gebruik in de API's
Me.Caption = "Grafieken"
m_hWnd = FindWindow(vbNullString, Me.Caption)
Set HuidigeGrafiek = ActiveSheet.ChartObjects(1).Chart
' Sla de grafiek op als GIF
Bnaam = ThisWorkbook.Path & Application.pathSeparator & "temp.gif"
HuidigeGrafiek.Export Filename:=Bnaam, FilterName:="GIF"
ActiveSheet.ChartObjects(1).Delete
ActiveWorkbook.Worksheets("Data").Protect
' Toon de grafiek
Image1.Picture = LoadPicture(Bnaam)
Application.ScreenUpdating = True
Kill ThisWorkbook.Path & Application.pathSeparator & "temp.gif"
VerwijderXSluiten
Me.knpSluiten.Default = True
End Sub
Sub MaakEenGrafiek(r)
Dim wksFormules As Worksheet
Dim TijdelijkeGrafiek As Chart
Dim CatTitels As Range
Dim BronBereik As Range, BronGegevens As Range
Application.ScreenUpdating = False
gbNegeerEvents = True ' We willen niet dat de Sheet_Activate event onnodig uitgevoerd
' wordt. Zodoende wordt ook de fout vermeden omdat scrollrow
' niet ingesteld kan worden met een grafiekobject op het werkblad.
Set wksFormules = ActiveWorkbook.Worksheets("Formules")
With wksFormules
.Calculate
Set CatTitels = .Range("A5:M5")
Set BronBereik = .Range(.Cells(r, 1), .Cells(r, 13))
End With
Set BronGegevens = Union(CatTitels, BronBereik)
' Voeg een grafiek toe
Set TijdelijkeGrafiek = Charts.Add
' Pas de grafiek aan
With TijdelijkeGrafiek
.ChartType = xlColumnClustered
.SetSourceData Source:=BronGegevens, PlotBy:=xlRows
.HasLegend = False
.ChartTitle.Characters.Text = "Taxi inkomsten-/uitgaven"
.ChartTitle.Font.Size = 14
.ChartTitle.Font.Bold = True
.SeriesCollection.NewSeries
.SeriesCollection(2).Values = "=Formules!R22C2:R22C13"
.SeriesCollection(2).Name = "Uitgaven"
.Axes(xlCategory).TickLabels.Font.Size = 10
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
ActiveWorkbook.Worksheets("Data").Unprotect
.Location Where:=xlLocationAsObject, Name:="Data"
End With
' Pas de grootte van het grafiefobject aan
With ActiveSheet.ChartObjects(1)
.Width = 300
.Height = 150
.Visible = False
End With
End Sub
Display More
thanks,
Richard