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 wrote:
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.

You could force a recompile in your code with

Application.VBE.CommandBars.FindControl(ID:=578).E xecute


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kris" wrote in message
...
Kris wrote:
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.

You could force a recompile in your code with

Application.VBE.CommandBars.FindControl(ID:=578).E xecute

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


"Bob Phillips" wrote in
message ...
You could force a recompile in your code with

Application.VBE.CommandBars.FindControl(ID:=578).E xecute


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kris" wrote in message
...
Kris wrote:
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.

Kris wrote:

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