help! insert variable value in vbcomponent.codemodule

[reneuend]

Here is a subroutine that works, except that the variable, chartName is not translated properly in the event routine that the code module creates.

Here is the event code that is generated during runtime:

Private Sub CommandButton1_Click()
    sChartName = chartName
    MsgBox "Previous code" & sChartName
End Sub
 
Private Sub CommandButton2_Click()
    sChartName = chartName
    MsgBox "Forward:" & sChartName
End Sub

Note that charName should be a value, but I don't know how to expose it in the codemodule.

Here is the code to generate the event routines above:

Sub AddCmdButton(ByVal chartName As String)
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object
Dim StartLine As Long
Dim nTopOffset As Integer
Dim sCmdBtn As String
    nTopOffset = 200
    Set objXL = GetObject(, "Excel.Application")
    Set objActiveWkb = objXL.Application.ActiveWorkbook
    nNumCharts = 1
    For jnx = 1 To nNumCharts
        For inx = 1 To 2
        
            sAdd = "Forms.CommandButton." & inx
        
            
            With objActiveWkb.ActiveSheet.OLEObjects.Add("Forms.CommandButton.1")
            'With objActiveWkb.ActiveSheet.OLEObjects.Add(sAdd)
            
                If inx Mod 2 = 0 Then 'forward
                    .Left = 150
                    .Object.Caption = "Forward >"
                Else
                    .Left = 35
                    .Object.Caption = "< Previous"
                End If
                .Top = 250 + (nTopOffset * (jnx - 1))
                .Width = 100
                .Height = 30
        
            End With
            
            sCmdBtn = "CommandButton" & inx
        
            With ActiveWorkbook.VBProject.VBComponents("Sheet10").CodeModule
                StartLine = .CreateEventProc("Click", sCmdBtn) + 1
                If inx Mod 2 = 0 Then
                    .InsertLines StartLine, _
                    "    MsgBox ""Forward:"" & sChartName"
                    .InsertLines StartLine, _
                    "    sChartName = chartName"
                Else
                    .InsertLines StartLine, _
                    "    MsgBox ""Previous code"" & sChartName"
                    .InsertLines StartLine, _
                    "    sChartName = chartName"
                End If
            End With
        Next 'inx (buttons)
    Next 'jnx (chart)

End Sub  'AddCmdButton

If you look at the following code in the subroutine shown above, you'll see where the event routine is getting generated and the chartName value that is not getting translated.
How do I pass the chartName value into the codemodule?????

                    .InsertLines StartLine, _
                    "    MsgBox ""Forward:"" & sChartName"
                    .InsertLines StartLine, _
                    "    sChartName = chartName"

To run this routine use the following call"

    Dim varid As String
    varid = "Test"
    Call AddCmdButton(varid)

 

[mikerickson]

Although I have serious reservations about code that writes code, this should do it.

.InsertLines StartLine, "    sChartName = " & chartName

Back to my reservations, why are you using code to write code?

If you want one command button to act on different charts at different times, there are other (more stable) ways to pass the chart name. Passing the chartName as a Public variable is probably the easiest way. Changing a CustomDocumentProperty is another.

 

[reneuend]

Thanks mikerickson.

Actually, I'll be making a set of buttons for each chart. The charts are created dynamically and there can be any number of charts from 1 to maybe 30. The client wants to be able to step through the changes in a moving range. I plan on passing the chart name to this codemodule and then calling a routine that will change the current moving range series data to either "previous" or "forward" to another set of data. Thus changing the series data on the chart.

This is the only way I could think of pulling it off. If there is a better way, I'm all ears and would appreciate anything you can offer.

....again, thanks for responding!