Problem with .xlsm file adding code via VBA

[Kanji]

Hi everyone!

I'm working with a subroutine that creates a new workbook. This workbook is a report that I want to customize by adding a combo box with an event 'OnChange' associated.

In order to add the event procedure from my subroutine to the new workbook, I use the VBIDE object model and, in special, the following code:

[VBA]Sub BeginProcess()
On Error GoTo Error_Handler
Dim wbkReport As Workbook
Dim vbpProject As Object
Dim vbcComponent As Object
Dim codModule As Object
Dim lngLine As Long
Workbooks.Add
Set wbkReport = ActiveWorkbook
With wbkReport.Worksheets(1)

.OLEObjects.Add "Forms.ComboBox.1" 'Add a combo box

.OLEObjects(1).Name = "cbxMonth" 'Its inner name

.Range("A5").Value = "January"

.Range("A6").Value = "February"

.OLEObjects(1).ListFillRange = .Name & "!A5:A6" 'Data source

End With

'Now, let's create the event procedure for this combo box

Set vbpProject = wbkReport.VBProject

Set vbcComponent = vbpProject.VBComponents(wbkReport.Worksheets(1).CodeName)

Set codModule = vbcComponent.CodeModule

lngLine = codModule.CreateEventProc("Change", "cbxMonth") 'On change event

codModule.InsertLines lngLine + 1, " VBA.Msgbox ""You choose "" & cbxMonth.Value"

'Finally, save the changes

wbkReport.SaveAs ThisWorkbook.Path & "\Report.xlsm", xlOpenXMLWorkbookMacroEnabled
GoTo Final
Exit Sub
Error_Handler:
VBA.MsgBox "An error has ocurred"
Final:
Set wbkReport = Nothing
Set codModule = Nothing
Set vbcComponent = Nothing
Set vbpProject = Nothing
End Sub
[/VBA]

Problem: if the users doesn't fire the event procedure changing the combo value and then closes this file -saving or not changes, however- when they want to open it back, the event procedure will not work: the code was not saved although I used the .SaveAs method in the subroutine that generates the new workbook.

It seems that it's not enough to save as .xlsm and use the right constant for the FileFormat argument; you must fire the event (!!).

Thank you in advance!

[Aussiebear]

What about a on close event placed within the This Workbook which calls this routine to ensure it is used?

[Aflatoon]

The joys of ActiveX - try saving in an OnTime procedure instead:

   Dim wbkReport As Workbook
Sub BeginProcess()
'    On Error GoTo Error_Handler
    Dim vbpProject As Object
    Dim vbcComponent As Object
    Dim codModule As Object
    Dim lngLine As Long
    Dim objOLE As OLEObject
    Workbooks.Add
    Set wbkReport = ActiveWorkbook
    With wbkReport.Worksheets(1)
         
        Set objOLE = .Shapes.AddOLEObject("Forms.ComboBox.1").OLEFormat.Object  'Add a combo box
         
        objOLE.Name = "cbxMonth" 'Its inner name
         
        .Range("A5").Value = "January"
         
        .Range("A6").Value = "February"
         
        objOLE.ListFillRange = .Name & "!A5:A6" 'Data source
         
    End With
     
     'Now, let's create the event procedure for this combo box
     
    Set vbpProject = wbkReport.VBProject
     
    Set vbcComponent = vbpProject.VBComponents(wbkReport.Worksheets(1).CodeName)
     
    Set codModule = vbcComponent.CodeModule
     
    lngLine = codModule.CreateEventProc("Change", "cbxMonth") 'On change event
     
    codModule.InsertLines lngLine + 1, " VBA.Msgbox ""You choose "" & cbxMonth.Value"
     
     'Finally, save the changes
     Debug.Print codModule.countoflines
     DoEvents
   Application.OnTime Now(), "SaveIt"
    GoTo Final
    Exit Sub
Error_Handler:
    VBA.MsgBox "An error has ocurred"
Final:
    Set codModule = Nothing
    Set vbcComponent = Nothing
    Set vbpProject = Nothing
End Sub
Sub SaveIt()
    wbkReport.SaveAs ThisWorkbook.Path & "\Report.xlsm", xlOpenXMLWorkbookMacroEnabled
End Sub

[Aflatoon]

Cross-post here and now solved it seems.

[Kanji]

Solved.

I'm playing with the code and find that the problem only appears with the event procedures -object modules-: if you add a 'dummy' standard module, class module, etc. -procedures inside are not needed- instead only event procedures, Excel will save correctly all the code generated via VBIDE in your '.xlsm'.

So it's a bizarre behaviour with the '.xlsm' but with an easy fix.

Thank you all.