Dynamically add event to a chart

[iamareplicant]

Experts,

I have read about 100 posts and still cannot add a mouse_up event (and event code) to an Excel chart that has just been created from within my MS Access app.

This chart in Excel is not a chart on a sheet, it is, well, a chart chart. It IS the entire "sheet." In the Excel ojects window, is show the chart object as, well, a chart object, not, for example, Sheet1, or Sheet 2, etc. Rather, it is Chart1.

OK. Here is the code snippit from within my MS Access module:

Dim objExcelApp As Excel.Application
Dim objExcelWB As Excel.Workbook
Dim objExcelSheet As Excel.Worksheet
Dim ModEvent As CodeModule 
Dim LineNum As Long 
Dim Proc As String 
Dim EndS As String 
Dim Ap As String 
Dim Tabs As String 
Dim LF As String 
Dim objSheetCode  As Object
Dim cht As Chart
 
On Error GoTo errHandler
Ap = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
EndS = "End Sub"


SubName = "Public Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, " & _
            " ByVal x As Long, ByVal y As Long)" & LF

Proc = " Dim ElementID As Long, Arg1 As Long, Arg2 As Long " & LF
Proc = Proc & "  Dim myX As Variant, myY As Double " & LF
Proc = Proc & "  With ActiveChart " & LF
    'With ActiveChart
Proc = Proc & " .GetChartElement x, y, ElementID, Arg1, Arg2 " & LF

Proc = Proc & " If ElementID = xlSeries Or ElementID = xlDataLabel Then " & LF
Proc = Proc & " If Arg2 > 0 Then " & LF
Proc = Proc & " myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2) " & LF
Proc = Proc & " myY = WorksheetFunction.Index(.SeriesCollection(Arg1).Values, Arg2)" & LF
 
Proc = Proc & " g_strClickedVal = myX " & LF
Proc = Proc & " frmValue.Show " & LF
Proc = Proc & " End If " & LF
Proc = Proc & " End If " & LF
Proc = Proc & " End With " & LF

Set objExcelApp = CreateObject("Excel.Application")
Set objExcelWB = objExcelApp.Workbooks.Open(GetSpecialfolder(CSIDL_DESKTOP) & "\ACT Excel\" & strFile & ".xls")

Set cht = objExcelWB.Charts("Chart1")
 
objExcelWB.VBProject.VBComponents(cht.CodeName).CodeModule.AddFromString Proc
'it errors out at the line above
'I will need some help too with next line below
Set ModEvent = objExcelWB.VBProject.VBComponents(objSheetCode).CodeModule

With ModEvent

LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With
Exit Sub
errHandler:
objExcelApp.Quit
        MsgBox Err.Number & " " & Err.Description
        Set objExcelSheet = Nothing
        Set objExcelWB = Nothing
        Set objExcelApp = Nothing
End Sub

I have tried to treat the chart as a sheet in the first line that errors (as noted above), and no go. Actually, I tried countless arrangments to get this code to work (treat the chart as sheet, treat the chart as a chart) and have obviously not succeeded.

Bottom line: how can I add an entire event sub routine to a chart and do so from within my MS Access app???

Please help me before I lose what little hair on my head I have left. This is driving me nuts...

TIA

JBG

 

[rmikesmith]

objExcelWB.VBProject.VBComponents(cht.CodeName).CodeModule.AddFromString Proc
'it errors out at the line above

What error are you getting? I don't have Access available but when I mocked this up using your code in MS Word the Chart MouseUp event procedure was added correctly and fully functional (I replaced your actual event procedure code with a simple MsgBox).


Regards,
Mike

 

[iamareplicant]

I was getting type mismatch. I was able to just now solve the issue with some duct tape (here is the snippit that replaced the line that was erroring)

For i = 1 To objExcelWB.VBProject.VBComponents.Count
                    
If objExcelWB.VBProject.VBComponents.Item(i).Name =           "Chart1" Then
              
                    objExcelWB.VBProject.VBComponents.Item(i).CodeModule.AddFromString SubName & Proc & EndS
Exit For
End If
            Next

Sorry for the alignment...