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:

Sub Process()
 
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

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!

 

[Norie]

Why not just create a workbook/worksheet template with the combobox, and it's event code?

When you need to create a new file just copy that, code and all.

So you wouldn't need to use code to create the combobox and it's code.

 

[Kanji]

Thanks Norie for your answer.

It's clear that the way you told me is easier but is not the right way in my case cause I must change the properties of the destiny worksheet also the combo box -events included- dinamically on deppends of certain data.

However, it's a very strange matter: although saving changes -.xlsm-, the code is not saved.

Thank you again!

 

[Norie]

Why couldn't you change the porperties of the destination sheet?

Also what changes to you need to make to the code?

 

[Kanji]

[SOLVED] Problem with .xlsm file adding code via VBA

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.