Userform causes Excel to crash

[Guest]

I have a VBA add-in that I've created for Excel. There is a user form. The
add-in creates a command button on the Standard toolbar that, when clicked,
causes the userform to display. The add-in compiles and installs without
error, and adds the button to the toolbar as expected. However when clicking
the button, Excel crashes with no warning or dialog box. I need to find out
what I'm doing that is causing the userform to cause Excel to crash. I'm
wondering if its related to not having a fully qualified object or something
like that. Any help would be appreciated.

Here's the code that invokes the user form. It is in a module called
modStartForm:
====================================================
Public Sub ShowUFMain()
On Error GoTo ErrorRoutine

ufMain.Show
ErrorRoutine:
modErrorHandle.ErrorHandling "modStartform.ShowUFMain"

End Sub
====================================================
There is some code relating to the invocation of the user form that is
associated with the user form itself. The userform is called ufMain. Here
is the relevant code:
====================================================
Private Sub UserForm_Activate()
On Error GoTo ErrorRoutine

'Populate the Project Name
If ActiveSheet.PageSetup.CenterHeader = "" Then
For mRow = 1 To 20
For mCol = 1 To 20
If Left(Cells(mRow, mCol), 8) = "PROJECT:" Then
ufMain.pg1ProjTitle_txt = Mid(Cells(mRow, mCol), 11)
Next
Next
Else
ufMain.pg1ProjTitle_txt = ActiveSheet.PageSetup.CenterHeader
End If
ErrorRoutine:
modErrorHandle.ErrorHandling "ufMain.UserForm_Activate"
End Sub
====================================================
Private Sub UserForm_Initialize()
On Error GoTo ErrorRoutine

Dim mCount, mRow, mCol As Integer
'''''''''''''''''''''''''''''''''
'Really, all this sub does is to populate the form with values
'''''''''''''''''''''''''''''''''

'Page 1

'Populate the various combo boxes
If ufMain.pg1ColorCompletedColor_ddl.ListCount = 0 Then
For mCount = 1 To 9
ufMain.pg1ColorCompletedColor_ddl.AddItem
ufMain.pg1ColorMSColor_ddl.AddItem
ufMain.pg1ColorParentColor_ddl.AddItem
ufMain.pg1ColorStartDateColor_ddl.AddItem
Next mCount
End If

'Populate rows you just created
With ufMain.pg1ColorCompletedColor_ddl
.List(0, 0) = 16777215
.List(0, 1) = "Clear"
.List(1, 0) = 12632256
.List(1, 1) = "Gray"
.List(2, 0) = 255
.List(2, 1) = "Red"
.List(3, 0) = 0
.List(3, 1) = "Black"
.List(4, 0) = 65280
.List(4, 1) = "Green"
.List(5, 0) = 65535
.List(5, 1) = "Yellow"
.List(6, 0) = 16711680
.List(6, 1) = "Blue"
.List(7, 0) = 16711935
.List(7, 1) = "Magenta"
.List(8, 0) = 16776960
.List(8, 1) = "Cyan"

End With

With ufMain.pg1ColorMSColor_ddl
.List(0, 0) = 16777215
.List(0, 1) = "Clear"
.List(1, 0) = 12632256
.List(1, 1) = "Gray"
.List(2, 0) = 255
.List(2, 1) = "Red"
.List(3, 0) = 0
.List(3, 1) = "Black"
.List(4, 0) = 65280
.List(4, 1) = "Green"
.List(5, 0) = 65535
.List(5, 1) = "Yellow"
.List(6, 0) = 16711680
.List(6, 1) = "Blue"
.List(7, 0) = 16711935
.List(7, 1) = "Magenta"
.List(8, 0) = 16776960
.List(8, 1) = "Cyan"
End With

With ufMain.pg1ColorParentColor_ddl
.List(0, 0) = 16777215
.List(0, 1) = "Clear"
.List(1, 0) = 12632256
.List(1, 1) = "Gray"
.List(2, 0) = 255
.List(2, 1) = "Red"
.List(3, 0) = 0
.List(3, 1) = "Black"
.List(4, 0) = 65280
.List(4, 1) = "Green"
.List(5, 0) = 65535
.List(5, 1) = "Yellow"
.List(6, 0) = 16711680
.List(6, 1) = "Blue"
.List(7, 0) = 16711935
.List(7, 1) = "Magenta"
.List(8, 0) = 16776960
.List(8, 1) = "Cyan"
End With

With ufMain.pg1ColorStartDateColor_ddl
.List(0, 0) = 16777215
.List(0, 1) = "Clear"
.List(1, 0) = 12632256
.List(1, 1) = "Gray"
.List(2, 0) = 255
.List(2, 1) = "Red"
.List(3, 0) = 0
.List(3, 1) = "Black"
.List(4, 0) = 65280
.List(4, 1) = "Green"
.List(5, 0) = 65535
.List(5, 1) = "Yellow"
.List(6, 0) = 16711680
.List(6, 1) = "Blue"
.List(7, 0) = 16711935
.List(7, 1) = "Magenta"
.List(8, 0) = 16776960
.List(8, 1) = "Cyan"
End With

'Initialize Reporting Period txtboxes
If ufMain.pg2ReportingMonth_txt = "" Then ufMain.pg2ReportingMonth_txt =
Month(Date) - 1
If ufMain.pg2ReportingYear_txt = "" Then ufMain.pg2ReportingYear_txt =
Year(Date)

'Initialize form fields from HKEY_CURRENT_USER registry

'First, check to see if hive keys exist, and if not, then create it
' and populate it with default values
If RegistryKeyExists(mBaseKey, mKeyName) = False Then
InitializeRegistryHive

'Now, copy values from registry into form fields
LoadRegistryValuesIntoFormPage1
LoadRegistryValuesIntoFormPage2
LoadRegistryValuesIntoFormPage3
LoadRegistryValuesIntoFormPage4

'Load the Pg1 Template Combo Boxes
LoadPg1TemplateComboBoxes

''''''''''''''''''''''''
'Add code to goto the Project Title control on page 1
''''''''''''''''''''''''''

'ufMain.MultiPage1.Pages ("Page1")
'Me.txtProjTitle.SetFocus
ErrorRoutine:
modErrorHandle.ErrorHandling "ufMain.userform_Initialize"
End Sub

 

[Jim Cone]

modErrorHandle is run every time in each sub.
I don't believe that is actually what you want.
Add "Exit Sub" as a separate line just before the "ErrorRoutine:" line.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Stu W" <[email protected]>
wrote in message
I have a VBA add-in that I've created for Excel. There is a user form. The
add-in creates a command button on the Standard toolbar that, when clicked,
causes the userform to display. The add-in compiles and installs without
error, and adds the button to the toolbar as expected. However when clicking
the button, Excel crashes with no warning or dialog box. I need to find out
what I'm doing that is causing the userform to cause Excel to crash. I'm
wondering if its related to not having a fully qualified object or something
like that. Any help would be appreciated.

Here's the code that invokes the user form. It is in a module called
modStartForm:
====================================================
Public Sub ShowUFMain()
On Error GoTo ErrorRoutine

ufMain.Show
ErrorRoutine:
modErrorHandle.ErrorHandling "modStartform.ShowUFMain"

End Sub
====================================================
There is some code relating to the invocation of the user form that is
associated with the user form itself. The userform is called ufMain. Here
is the relevant code:
====================================================
Private Sub UserForm_Activate()
On Error GoTo ErrorRoutine

'Populate the Project Name
If ActiveSheet.PageSetup.CenterHeader = "" Then
For mRow = 1 To 20
For mCol = 1 To 20
If Left(Cells(mRow, mCol), 8) = "PROJECT:" Then
ufMain.pg1ProjTitle_txt = Mid(Cells(mRow, mCol), 11)
Next
Next
Else
ufMain.pg1ProjTitle_txt = ActiveSheet.PageSetup.CenterHeader
End If
ErrorRoutine:
modErrorHandle.ErrorHandling "ufMain.UserForm_Activate"
End Sub
====================================================
Private Sub UserForm_Initialize()
On Error GoTo ErrorRoutine

Dim mCount, mRow, mCol As Integer
'''''''''''''''''''''''''''''''''
'Really, all this sub does is to populate the form with values
'''''''''''''''''''''''''''''''''

'Page 1

'Populate the various combo boxes
If ufMain.pg1ColorCompletedColor_ddl.ListCount = 0 Then
For mCount = 1 To 9
ufMain.pg1ColorCompletedColor_ddl.AddItem
ufMain.pg1ColorMSColor_ddl.AddItem
ufMain.pg1ColorParentColor_ddl.AddItem
ufMain.pg1ColorStartDateColor_ddl.AddItem
Next mCount
End If

'Populate rows you just created
With ufMain.pg1ColorCompletedColor_ddl
.List(0, 0) = 16777215
.List(0, 1) = "Clear"
.List(1, 0) = 12632256
.List(1, 1) = "Gray"
.List(2, 0) = 255
.List(2, 1) = "Red"
.List(3, 0) = 0
.List(3, 1) = "Black"
.List(4, 0) = 65280
.List(4, 1) = "Green"
.List(5, 0) = 65535
.List(5, 1) = "Yellow"
.List(6, 0) = 16711680
.List(6, 1) = "Blue"
.List(7, 0) = 16711935
.List(7, 1) = "Magenta"
.List(8, 0) = 16776960
.List(8, 1) = "Cyan"

End With

With ufMain.pg1ColorMSColor_ddl
.List(0, 0) = 16777215
.List(0, 1) = "Clear"
.List(1, 0) = 12632256
.List(1, 1) = "Gray"
.List(2, 0) = 255
.List(2, 1) = "Red"
.List(3, 0) = 0
.List(3, 1) = "Black"
.List(4, 0) = 65280
.List(4, 1) = "Green"
.List(5, 0) = 65535
.List(5, 1) = "Yellow"
.List(6, 0) = 16711680
.List(6, 1) = "Blue"
.List(7, 0) = 16711935
.List(7, 1) = "Magenta"
.List(8, 0) = 16776960
.List(8, 1) = "Cyan"
End With

With ufMain.pg1ColorParentColor_ddl
.List(0, 0) = 16777215
.List(0, 1) = "Clear"
.List(1, 0) = 12632256
.List(1, 1) = "Gray"
.List(2, 0) = 255
.List(2, 1) = "Red"
.List(3, 0) = 0
.List(3, 1) = "Black"
.List(4, 0) = 65280
.List(4, 1) = "Green"
.List(5, 0) = 65535
.List(5, 1) = "Yellow"
.List(6, 0) = 16711680
.List(6, 1) = "Blue"
.List(7, 0) = 16711935
.List(7, 1) = "Magenta"
.List(8, 0) = 16776960
.List(8, 1) = "Cyan"
End With

With ufMain.pg1ColorStartDateColor_ddl
.List(0, 0) = 16777215
.List(0, 1) = "Clear"
.List(1, 0) = 12632256
.List(1, 1) = "Gray"
.List(2, 0) = 255
.List(2, 1) = "Red"
.List(3, 0) = 0
.List(3, 1) = "Black"
.List(4, 0) = 65280
.List(4, 1) = "Green"
.List(5, 0) = 65535
.List(5, 1) = "Yellow"
.List(6, 0) = 16711680
.List(6, 1) = "Blue"
.List(7, 0) = 16711935
.List(7, 1) = "Magenta"
.List(8, 0) = 16776960
.List(8, 1) = "Cyan"
End With

'Initialize Reporting Period txtboxes
If ufMain.pg2ReportingMonth_txt = "" Then ufMain.pg2ReportingMonth_txt =
Month(Date) - 1
If ufMain.pg2ReportingYear_txt = "" Then ufMain.pg2ReportingYear_txt =
Year(Date)

'Initialize form fields from HKEY_CURRENT_USER registry

'First, check to see if hive keys exist, and if not, then create it
' and populate it with default values
If RegistryKeyExists(mBaseKey, mKeyName) = False Then
InitializeRegistryHive

'Now, copy values from registry into form fields
LoadRegistryValuesIntoFormPage1
LoadRegistryValuesIntoFormPage2
LoadRegistryValuesIntoFormPage3
LoadRegistryValuesIntoFormPage4

'Load the Pg1 Template Combo Boxes
LoadPg1TemplateComboBoxes

''''''''''''''''''''''''
'Add code to goto the Project Title control on page 1
''''''''''''''''''''''''''

'ufMain.MultiPage1.Pages ("Page1")
'Me.txtProjTitle.SetFocus
ErrorRoutine:
modErrorHandle.ErrorHandling "ufMain.userform_Initialize"
End Sub

 

[Guest]

Thanks, Jim. I'll make that change, but that doesn't address the crash
problem.

 

[Jim Cone]

Also, I am betting that you do not have "Option Explicit" at the top of every module?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: special sorting, comparing, matching, listing, finding...)



"Stu W" <[email protected]>
wrote in message
Thanks, Jim. I'll make that change, but that doesn't address the crash
problem.

 

[Guest]

Yes, I do.

Also, I am betting that you do not have "Option Explicit" at the top of every module?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: special sorting, comparing, matching, listing, finding...)



"Stu W" <[email protected]>
wrote in message
Thanks, Jim. I'll make that change, but that doesn't address the crash
problem.

 

[Jim Cone]

My best guess would be the registry modifications are causing the crash.
Comment out that area and see how it works.
Beyond that I am out of ideas.
Jim Cone
San Francisco, USA



"Stu W" <[email protected]>
wrote in message
Yes, I do.

 

[Guest]

Here's an update... I can run the code if I first open up the vba
development environment and display the code. If I don't do that first, then
running the code causes Excel to crash. I don't have to change anything, I
just have to look at the code.

THere are times when I can run the code until I compile it and password
protect the code. Then it will cause Excel to crash.

Any ideas out there?

 

[shah shailesh]

Here's an update... I can run the code if I first open up the vba
development environment and display the code. If I don't do that first,
then
running the code causes Excel to crash. I don't have to change anything,
I
just have to look at the code.

In this case you can add below code as under:

Public Sub ShowUFMain()
On Error GoTo ErrorRoutine

With Application.VBE.MainWindow
.Visible = True
.Visible = False
End With


ufMain.Show
ErrorRoutine:
modErrorHandle.ErrorHandling "modStartform.ShowUFMain"

End Sub


You may clean the codes by exporting each modules & userforms & again import
them. You may download free addins for code cleaner. Have a google search.
You can also download my free comaddins "File Sizer" from Com-Addins page of
below site.

Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)
If You Can't Excel with Talent, Triumph with Effort.

 

[Guest]

Stu,
I had similar problem with an AddIn I created - after months of head
scratching I traced it to a worksheet_change event calling a macro which
should have only triggered when the form was visible.
Not sure if applies to your but thought may be helpful.

 

[Guest]

Well, interesting work-around idea. Doesn't really solve the source problem,
but might be a temporary solution until the real problem is identified.

When I run the code below I get a error message:

Run-time error '1004':
Programmatic access to Visual Basic Project is not trusted

any ideas?

 

[shah shailesh]

If you can enable Trust access to visual basic project, this error will not
display.

Tools>macros>Securities>Trusted Publishers> Tickmark Trust access to visual
basic project.


Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)
If You Can't Excel with Talent, Triumph with Effort.

 

[Guest]

Thanks, this works, and is a suitable work-around. But man, wish I didn't
have to do this.

 

[shah shailesh]

Have you clean the codes as mentioned in my earlier post?

Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)
If You Can't Excel with Talent, Triumph with Effort.

 

[Guest]

Yes, I've cleaned the code as suggested (both manually and using AppsPro's
code cleaner), but still have the same problem. I've isolated the problem to
five controls on the user form. Excel will crash on *any* of the following
statements:

ufMain.pg1Letter_ob.Value = True
ufMain.pg1Legal_ob = True
ufMain.pg1Portrait_ob = True
ufMain.pg1Landscape_ob = True
ufMain.pg1Shrink_chkbox = True

Excel does not crash if I include the following code in front of the above
statements:

Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.Visible = False

or if I open up the VBE prior to running the code.

All code compiles cleanly, and is password protected. I've used Option
Explicit on all code. I have other option buttons and checkboxes on the same
user form with statements as above that are not causing Excel to crash. I am
using a multi page control on the form, and all of the above are on the same
tab. I've tried moving these controls into and out of frames, with no
resolution to Excel crashing.

Stu W.

 

[Peter T]

Earlier you mentioned you need access to Visual Basic Project, why

Depending on what you are doing to what, where and when, you might be
triggering a recompile during run-time.

Regards,
Peter T

 

[shah shailesh]

ufMain.pg1Letter_ob.Value = True

Sound pg1Letter_ob is multipage control. What about other 4 controls?

Try to set 1 instead of true.

other options :

1.You may delete the multipage control & recreate new one.
2.Check to see the form's code module with controls event's sub opens, when
you double click on control.
3.Check to see it has a problem with other systems.
4.You can email me this workbook.



Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)
If You Can't Excel with Talent, Triumph with Effort.

 

[Guest]

Well, I would prefer not to have to have the add-in access the VB Project,
but that seems to be the only way I can prevent the execution of the four
problem statements from crashing Excel. Excel doesn't crash when running any
of those four statements if I've exposed the VBE prior to the statements.
So, its the only way I can run those statements without having Excel crash.

Doesn't make sense to me, but that's my experience.

 

[Guest]

Actually the multi-page control is called MultiPage1. All other controls on
any of the four pages of this control works, with the exception of the four
controls I've listed, which, along with other controls, are on the first tab
of MultiPage1. The _ob controls are Option Buttons, and the _chkbox is a
Check Box.

I've haven't tried to delete and recreate the MultiPage1 form because there
are lots of controls on this form, and it would be too difficult. I also
thought that running the code cleaner utility would, in essence, do pretty
much the same thing.

I have tried to set the value to False, with the same results.

I have added an additional Check Box control to the user form, but outside
the MultiPage form, and get the same results -- Excel crashes when I try to
set the control's value in VBA code.

I've tried deleting other controls to see if there is some sort of maximum
number of controls allowed on a multipage form, but that hasn't had any
positive impact on the problem.

 

[Anders Hane]

I have the same problem but in Excel 2000.

A file is created from an Excel template. The file contains a menubar where it is possible to open a user form. The form contains a MultiPage control. Accessing a certain textbox on one of the pages on the MultiPage control in ANY way causes Excel to crash. Here are some things I have tested to solve this.

Someone suggested that I should try to switch between sheets since this can mysteriously make Excel behave nicely. I added the following in the Workbook_Open event:

Application.ScreenUpdating = False
ActiveWorkbook.Sheets("MySheet 2").Select
ActiveWorkbook.Sheets("MySheet 1").Select
Application.ScreenUpdating = True

This lets me access the textbox control the first time the workbook is opened. If I try a second time (after shutting down Excel and opening the file again), Excel crashes.

Other things tried that didn't help:

- Changing the name of the textbox.
- Adding add a Wait statement before the line that causes the crash
- Moving the accessing statement from UserForm_Initialize() to UserForm_Activate()
- Replacing the actual textbox with a new one (or other type of control, I tried a label as well, just to make sure)
- Activate the page on the MultiPage control where the textbox is located before accessing the textbox.
- Moving all controls from the MultiPage control to the form and deleting the MultiPage control.

I also tried setting VBE visible on and off but for me that doesn't work.

Since I use Excel 2000 I don't have the setting "Trust access to visual basic project" but I have "Trust all installed add-ins and templates" checked instead.

One strange thing is that adding and removing an empty line in UserForm_Initialize() and then saving the file makes the problem go away the next time the file is opened. Save and open again and the problem is back.

Quite frustrating...