Userform causes Excel to crash

S

Stu W

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
 
J

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
 
J

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.
 
S

Stu W

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?
 
S

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.
Click to expand...

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.
 
J

johnboy

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.
 
S

Stu W

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?
 
S

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.
 
S

Stu W

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

Stu W

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.
 
P

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
 
S

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.
 
S

Stu W

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.
 
S

Stu W

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Spin button to scroll up&down a database with a userform 7
Need VBA solution to find 1st and 2nd occurrences of non- zero, thenreturn column header value 7
MuliColumn Combo 3
Nested loop Programing Challenge 3
Print macro 2
fill in data that is not empty 1
how to solve the following in excel 1
Error with calling Format sub 7

Top