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).C odeModule
StartLine = .CreateEventProc("Change", "WorkSheet") + 1
.InsertLines StartLine, "Call OnPTSelectionChange"
End With

End Sub

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(S heetName).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" wrote in message
. 16...
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).C odeModule
StartLine = .CreateEventProc("Change", "WorkSheet") + 1
.InsertLines StartLine, "Call OnPTSelectionChange"
End With

End Sub

"Bob Phillips" wrote in
:

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 White" wrote in message
. 16...
"Bob Phillips" wrote in
:

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).E xecute

which executes the DebugCompile 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 Phillips" wrote in
:


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).E xecute

which executes the DebugCompile Project command.

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



So, did my suggestion help you?


Definitely! Thank you, very much!

Bob