Why does this bring up VB?

[Bob White]

Create a new workbook, then start VB and paste the following code into a
new code module. Close VB. Run the macro from Excel. The macro runs
correctly, yet VB reopens. Is there a way to either keep VB from opening
or a programmatic way to close it after it does?

Thanks for the help!

Bob

_____________________________________________________

Sub test()
Dim SheetName As String
SheetName = "XYZZY"
Set WS = ActiveWorkbook.Sheets.Add
WS.Name = SheetName
'
For i = 1 To ThisWorkbook.VBProject.VBComponents.Count
Set c = ThisWorkbook.VBProject.VBComponents(i)
If c.Type = 100 Then 'vbext_ct_document =100
If c.Properties("Name") = SheetName Then
ThisWorkbook.VBProject.VBComponents(i).Properties("_CodeName") = SheetName

End If
Next i
Set c = Nothing
'
With ActiveWorkbook.VBProject.VBComponents(SheetName).CodeModule
StartLine = .CreateEventProc("Change", "WorkSheet") + 1
.InsertLines StartLine, "Call OnPTSelectionChange"
End With

End Sub

 

[Bob Phillips]

Hi Bob,

Here is amended code.

I found three errors in this, so I don't know how you got it to work . I
also added some code to manage if the sheet already exists

Sub VBAWindow()
Dim ws As Worksheet
Dim SheetName As String
Dim i, c, StartLine

SheetName = "XYZZY"
On Error Resume Next
Set ws = ActiveWorkbook.Sheets.Add
ws.Name = SheetName
On Error GoTo 0
'
For i = 1 To ThisWorkbook.VBProject.VBComponents.Count
Set c = ThisWorkbook.VBProject.VBComponents(i)
If c.Type = 100 Then 'vbext_ct_document =100
If c.Name = SheetName Then

ThisWorkbook.VBProject.VBComponents(i).Properties("_CodeName") = SheetName
End If
End If
Next i
Set c = Nothing
'
With
ActiveWorkbook.VBProject.VBComponents(Worksheets(SheetName).CodeName).CodeMo
dule
StartLine = .CreateEventProc("Change", "WorkSheet") + 1
.InsertLines StartLine, "Call OnPTSelectionChange"
End With

Application.VBE.MainWindow.Visible = False

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob White]

Hi Bob,

Here is amended code.

I found three errors in this, so I don't know how you got it to work
. I also added some code to manage if the sheet already exists

Sub VBAWindow()
Dim ws As Worksheet
Dim SheetName As String
Dim i, c, StartLine

SheetName = "XYZZY"
On Error Resume Next
Set ws = ActiveWorkbook.Sheets.Add
ws.Name = SheetName
On Error GoTo 0
'
For i = 1 To ThisWorkbook.VBProject.VBComponents.Count
Set c = ThisWorkbook.VBProject.VBComponents(i)
If c.Type = 100 Then 'vbext_ct_document =100
If c.Name = SheetName Then

ThisWorkbook.VBProject.VBComponents(i).Properties("_CodeName") =
SheetName
End If
End If
Next i
Set c = Nothing
'
With
ActiveWorkbook.VBProject.VBComponents(Worksheets (SheetName).CodeName).C
odeMo dule
StartLine = .CreateEventProc("Change", "WorkSheet") + 1
.InsertLines StartLine, "Call OnPTSelectionChange"
End With

Application.VBE.MainWindow.Visible = False

End Sub

Hi, Bob!

I appreciate the feedback. This is, by far, not the whole application.
In it, the worksheet was deleted if it existed. I was just trying to
get the core concept out here. For what I was trying to accomplish with
this snippet, I just let the variables be variants by default. They
aren't in the whole program.

The problem was that if the worksheet was created right before I tried
to insert the event procedure, Sheets(SheetName).CodeName returned a
null string *IF* VB isn't running. With VB open, CodeName is filled
appropriately. I found a few references via Google that the workbook had
to be saved and reopened, or there were other "workarounds".

This made it difficult, as I would never know the real CodeName for a
sheet that is getting repeatedly deleted and recreated, and VBComponents
doesn't take the tab name as an acceptable index.

What was frustrating was getting the kinks worked out of the code and
then having VB continually open up!

Thanks,

Bob

 

[Bob Phillips]

Hi, Bob!

I appreciate the feedback. This is, by far, not the whole application.
In it, the worksheet was deleted if it existed. I was just trying to
get the core concept out here. For what I was trying to accomplish with
this snippet, I just let the variables be variants by default. They
aren't in the whole program.

The problem was that if the worksheet was created right before I tried
to insert the event procedure, Sheets(SheetName).CodeName returned a
null string *IF* VB isn't running. With VB open, CodeName is filled
appropriately. I found a few references via Google that the workbook had
to be saved and reopened, or there were other "workarounds".

It can be easier than that Bob. The problem seems to be that if the VBE
window is closed, the codename doesn't get assigned as there is no
recompile. The recompile can be forced with this code

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

which executes the Debug>Compile Project command.

This made it difficult, as I would never know the real CodeName for a
sheet that is getting repeatedly deleted and recreated, and VBComponents
doesn't take the tab name as an acceptable index.

What was frustrating was getting the kinks worked out of the code and
then having VB continually open up!

So, did my suggestion help you?

 

[Bob White]

It can be easier than that Bob. The problem seems to be that if the VBE
window is closed, the codename doesn't get assigned as there is no
recompile. The recompile can be forced with this code

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

which executes the Debug>Compile Project command.

Ok. I'll give that a try, too.

So, did my suggestion help you?

Definitely! Thank you, very much!

Bob