why does userform close after programming worksheet event with CreateEventProc or AddFromString

[tr00per]

I have a problem that I hope someone might be able to help with.
Thanks in advance for any help.

Background:
For an excel project, I have created a userform that runs automatically
upon opening the workbook. To simplify the example, let's say the
userform contains only a textbox and commandbutton controls. The
commandbutton control adds a new worksheet to the workbook (using
Worksheets.Add) and then sets the .Name of the newly added worksheet
equal to the .Value of the textbox on the userform. The code then uses
..CreateEventProc to programmatically code an event (let's say the:
Worksheet_Calculate(), event). Lastly, I "close" the VBE with:
Application.VBE.MainWindow.Visible = False. All of the above works
correctly - i.e. the worksheet is added to the workbook and is
correctly named, Worksheet_Calculate() event is correctly written to
the new worksheet, etc.

Finally, the problem...the userform closes. I would prefer that the
userform remain open with the user's previous input values still
present in the form. I'm not really sure why the userform goes away,
but it seems like the project stops running. Is it possible to regain
view of the userform after closing the VBE? Or, do I possibly have to
tediously save the relevant properties (.Value, .Visible, etc) of each
control so that I can at least re-show the userform and reset the
properties after closing the VBE?

Also, the userform still closes / is no longer accessible even if I use
..AddFromString instead of .CreateEventProc. The one advantage I found
using .AddFromString is that the VBE doesn't launch.

Sorry for being so long-winded about this. If you would like to see
the actual code that I have written, please let me know so that I can
get back to you. Otherwise, I hope you can help me with this or at
least get me pointed in the right direction toward a solution.

Thanks again,
Coop
(e-mail address removed)

 

[tr00per]

THE CODE:
ThisWorkbook --
Private Sub Workbook_Open()
UserForm1.Show
End Sub

UserForm1 --
Private Sub CommandButton1_Click()
Module1.DoStuff
End Sub

Module1 --
Public Sub DoStuff()
Dim VBProj As VBProject
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim testCodeString As String

Application.ScreenUpdating = False

''Add a new worksheet to the workbook
Worksheets.Add
''rename the worksheet per the user's textbox input value
ActiveSheet.name = UserForm1.TextBox1.Value

''write event for the new worksheet
Set VBProj = ThisWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
If (VBComp.Properties("Name").Value = ActiveSheet.name)
Then
Set VBCodeMod = VBComp.CodeModule

'
''--------------------------------------------------------------------------------------
' ''METHOD 1: Use .CreateEventProc
'
''--------------------------------------------------------------------------------------
' ''create Worksheet_SelectionChagne event
' With VBCodeMod
' StartLine = .CreateEventProc("SelectionChange",
"WorkSheet") + 1
' .InsertLines StartLine, _
' " ''test line - comment 1" & vbCr & _
' End With
'
' ''create Worksheet_Calculate event
' With VBCodeMod
'ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
' StartLine = .CreateEventProc("Calculate",
"Worksheet") + 1
' .InsertLines StartLine, _
' " ''test line - comment 1" & vbCr & _
' " ''test line - comment 2" & vbCr & _
' " 'UserForm1.Show vbModeless"
' End With
' ''--END METHOD 1
CODE-------------------------------------------------------------------



''--------------------------------------------------------------------------------------
'' METHOD 2: Use .AddFromString

''--------------------------------------------------------------------------------------
testCodeString = _
"Public Sub Worksheet_Calculate()" & vbCr & _
" ''test line - comment 1" & vbCr & _
"End Sub"

VBCodeMod.AddFromString testCodeString
''--END METHOD 2
CODE-------------------------------------------------------------------
End If
End If
Next

Application.ScreenUpdating = True
End Sub

** THE END ** ...no other code required. If anyone has trouble getting
this to run, please let me know.

Thanks again for any help I can get.

Troy
(e-mail address removed)

 

[michelxld]

Hello

I hope this helps you


Public Sub DoStuff()
Dim VBProj As VBProject
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim testCodeString As String

Application.ScreenUpdating = False
If UserForm1.TextBox1.Value = "" Then Exit Sub

''Add a new worksheet to the workbook
Worksheets.Add
''rename the worksheet per the user's textbox input value
ActiveSheet.Name = UserForm1.TextBox1.Value

''write event for the new worksheet
Set VBProj = ThisWorkbook.VBProject
Set VBComp = VBProj.VBComponents(ActiveSheet.CodeName)
Set VBCodeMod = VBComp.CodeModule

With VBCodeMod
StartLine = .CreateEventProc("SelectionChange", "WorkSheet") + 1
..InsertLines StartLine, _
" ''test line - comment 1" & vbCr
End With

With VBCodeMod
StartLine = .CreateEventProc("Calculate", "Worksheet") + 1
..InsertLines StartLine, _
" ''test line - comment 1" & vbCr & _
" ''test line - comment 2" & vbCr & _
" 'UserForm1.Show vbModeless"
End With

Set VBProj = Nothing
Set VBComp = Nothing
Set VBCodeMod = Nothing
Application.ScreenUpdating = True

Application.VBE.MainWindow.Visible = False
'UserForm1.TextBox1.SetFocus
End Sub



Regards,
michel

 

[tr00per]

Hello michelxld,

Thank you for replying to my post. Unfortunately, even with your
recommended changes, I'm still not quite getting the results that I
want. Writing to the event procedures still seems to be killing off
the userform. maybe you could take another look or tell me what sort
of results you get when you run the code.

Thanks for your help again,
Troy
(e-mail address removed)

 

[michelxld]

Hello Troy

at the end of the procedure , VBE is closed and the UserForm take the
Focus at the front

test with Excel2002 & WinXP


regards,
michel

 

[tr00per]

Thanks to everyone who replied here and sent emails. I finally got it
all figured out and just wanted to post the result to help others who
might have a similar problem. First the code, then the explanation.

THE CODE:
Public Sub DoStuff()
Dim VBProj As VBProject
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim testCodeString As String

Application.ScreenUpdating = False

''add a new (temporary) workbook
Workbooks.Add

''rename the activesheet per the user's textbox input value
ActiveSheet.Name = UserForm1.TextBox1.Value

''write event for the new worksheet
Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
If (VBComp.Properties("Name").Value = ActiveSheet.Name)
Then
Set VBCodeMod = VBComp.CodeModule


''--------------------------------------------------------
'' METHOD 2: Use .AddFromString

''--------------------------------------------------------
testCodeString = _
"Public Sub Worksheet_Calculate()" & vbCr & _
" Dim prompt As String" & vbCr & vbCr & _
" ''test line - comment 1" & vbCr & _
" prompt = ""RUNNING: Worksheet_Calculate""" &
vbCr & _
" MsgBox prompt" & vbCr & _
"End Sub"

VBCodeMod.AddFromString testCodeString
''--END METHOD 2
CODE-------------------------------------

End If
End If
Next

''move the new sheet with event code to the other workbook
Sheets(1).Move Before:=Workbooks("TestWorkbook.xls").Sheets(1)

''activate and close/delete the previously added (temporary)
workbook
Workbooks(Workbooks.Count).Activate
ActiveWorkbook.Close (False)

Application.ScreenUpdating = True
End Sub

THE EXPLANATION:
I got this idea from another post where the post-er mentioned being
able to write code to a worksheet in a different workbook, but not the
workbook running the userform. So, I decided to try creating a
temporary workbook, writing the Worksheet event code in a sheet in the
new workbook and then just moving the worksheet to the old workbook.
This worked...Now, the original workbook contains a new sheet (named by
the user), the new sheet contains the appropriate event code, and the
userform stays open - ready for another user input (worksheet name).

If anyone would like to chat about this further, please email me since
i'll probably stop watching this thread.

Happy programming...
Troy
(e-mail address removed)