Inserting VBA code...

[Kris]

Hi.
I have a code which inserts some lines of code to new workbook..

Sub AddComboBoxCode2(VBCodeMod As CodeModule, sCodeName As String)
With VBCodeMod
.InsertLines .CountOfLines + 1, "Private Sub ComboBox1_Change()"
.InsertLines .CountOfLines + 1, " Dim TargetPivotTable As
PivotTable"
.InsertLines .CountOfLines + 1, " Dim units As String"
.InsertLines .CountOfLines + 1, " On Error GoTo ErrHandler"
.InsertLines .CountOfLines + 1, " Select Case ComboBox1.Value"

{...}


Everything works fine if VBA is open.
When I close VBA no code is added to workbook.


Did it happen to somebody?

 

[Kris]

Hi.
I have a code which inserts some lines of code to new workbook..

Sub AddComboBoxCode2(VBCodeMod As CodeModule, sCodeName As String)
With VBCodeMod
.InsertLines .CountOfLines + 1, "Private Sub ComboBox1_Change()"
.InsertLines .CountOfLines + 1, " Dim TargetPivotTable As
PivotTable"
.InsertLines .CountOfLines + 1, " Dim units As String"
.InsertLines .CountOfLines + 1, " On Error GoTo ErrHandler"
.InsertLines .CountOfLines + 1, " Select Case ComboBox1.Value"

{...}


Everything works fine if VBA is open.
When I close VBA no code is added to workbook.


Did it happen to somebody?

I found something.

..CodeName return empty string when vba is closed, and a name of sheet
when VBA is open.


So, next question.
Why .CodeName which is a actual value of parameter to function listed
above doesn't work with VBA closed.

 

[Bob Phillips]

You could force a recompile in your code with

Application.VBE.CommandBars.FindControl(ID:=578).Execute


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Chip Pearson]

You could force a recompile in your code with

Application.VBE.CommandBars.FindControl(ID:=578).Execute

That will fail if a compile is not required (the compile command
is grayed out). Instead, try

Dim Ctrl As Office.CommandBarControl
Set Ctrl = Application.VBE.CommandBars.FindControl(ID:=578)
If Ctrl.Enabled = True Then
Ctrl.Execute
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Kris]

I found something.

.CodeName return empty string when vba is closed, and a name of sheet
when VBA is open.


So, next question.
Why .CodeName which is a actual value of parameter to function listed
above doesn't work with VBA closed.

I like to write to myself.

I found the problem.
CodeName property is not set if VBA is not open at least once.
it is done by design in Excel.


This lines of code solve the problem.

Dim vbp As Object
Set vbp = ActiveWorkbook.VBProject