PDA

View Full Version : Problem with .xlsm file adding code via VBA



Kanji
07-14-2011, 10:40 AM
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 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


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
07-16-2011, 01:18 AM
What about a on close event placed within the This Workbook which calls this routine to ensure it is used?

Aflatoon
07-18-2011, 01:57 AM
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
07-18-2011, 06:47 AM
Cross-post here (http://www.mrexcel.com/forum/showthread.php?t=564346) and now solved it seems.

Kanji
07-20-2011, 01:54 AM
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.