Excel Crashing - Problem in VB Code

[clloydy]

I am currently building a tool that incorporates a variety of macros. These range from simple macros to the more complex and include setting full screen and focus, disabling buttons and changing displayed images based on access levels stored on worksheets, putting password prompts on userforms to access other userforms, etc.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
However, I seem to have an issue where excel crashes frequently, at least twice per hour, I’ve tried re-installing Microsoft Office and Windows but the problem still persists. It is also happening on other machines.<o></o>
It frequently happens when trying to save the file, and comes up with the following error message:<o></o>
Problem signature:<o></o>
Problem Event Name: APPCRASH<o></o>
Application Name: EXCEL.EXE<o></o>
Application Version: 12.0.4518.1014<o></o>
Application Timestamp: 45428263<o></o>
Fault Module Name: VBE6.DLL<o></o>
Fault Module Version: 6.5.10.20<o></o>
Fault Module Timestamp: 45187577<o></o>
Exception Code: c0000005<o></o>
Exception Offset: 00111497<o></o>
OS Version: 6.1.7600.2.0.0.256.48<o></o>
Locale ID: 2057<o></o>
<o></o>
Additional information about the problem:<o></o>
LCID: 1033<o></o>
Brand: Office12Crash<o></o>
skulcid: 1033<o></o>
<o></o>
It also crashes when I am initiating or editing userforms, this is normally when I have changed the VB code (and crashes both when accessing particular forms or modules and on the worksheets). A common message is ‘Object not found.’ When I click on debug it displays the module that calls the userform and highlights the line ‘Userform1.Show’, which is in fact there and works previously.<o></o>
The Workbook has to go out to a client who will only have limited access to the worksheet and will not be able to edit the VBA code. I’m guessing it must be an error in part of the code that is causing the worksheet to crash but I’m not sure what to look for, as all the code seems to work but it just crashes at random intervals. If anyone could suggest what could be causing this and what I should be looking for it would be greatly appreciated.<o></o>
Many Thanks,<o></o>
Chris<o></o>

 

[VoG]

It might be an idea to rebuild your workbook http://www.mrexcel.com/forum/showthread.php?t=223851

 

[clloydy]

The spreadsheet is very large and I am working to a deadline so remaking the sheet would have to be a last resort. However, I think I might have narrowed it down to the following code:

The below code comes from a userform I am using and is designed to initialise the userform and find the next value in a column that is not equal to 1.

Private Sub UserForm_Initialize()
ActiveWorkbook.Sheets("Auditors").Activate
Range("N13").Select
Do
If [ActiveCell] = 1 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value <> 1
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
UniqueID.Caption = ActiveCell.Offset(0, 3).Value
FirstName.Value = ""
LastName.Value = ""
Company.Value = ""
ContactNumber.Value = ""
PasswordCheckBox = True
With Status
.AddItem "Active"
.AddItem "Inactive"
End With
FirstName.SetFocus
End Sub

Then the following code is designed to input the data entered on the userform into cells on that row then close the userform, the code for this is:

Private Sub Ok_Click()
ActiveCell.Offset(0, 6) = FirstName.Value
ActiveCell.Offset(0, 7) = LastName.Value
ActiveCell.Offset(0, -10) = Company.Value
ActiveCell.Offset(0, -9) = ContactNumber.Value
ActiveCell.Offset(0, -8) = Status.Value

If PasswordCheckBox = True Then
ActiveCell.Offset(0, 8) = Password.Value
Else: ActiveCell.Offset(0, 8) = ""
End If
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Unload Me
End Sub


This code works but I think it may be causing the system to crash. Could the active cell referencing be conflicting with other modules running in the background, if so would there be a way i could change the code so that it wasn't referenced to the active cell?

Thanks,
Chris

 

[VoG]

I would avoid activating cells altogether. For example your first routine could be written as

Private Sub UserForm_Initialize()
Dim r As Range
ActiveWorkbook.Sheets("Auditors").Activate
Set r = Range("N13")
Do
    If r.Value = 1 Then
        Set r = r.Offset(1)
    End If
Loop Until r.Value <> 1
UniqueID.Caption = r.Offset(0, 3).Value
FirstName.Value = ""
LastName.Value = ""
Company.Value = ""
ContactNumber.Value = ""
PasswordCheckBox = True
With Status
    .AddItem "Active"
    .AddItem "Inactive"
End With
FirstName.SetFocus
End Sub

 

[clloydy]

Thankyou I don't think ive solved the crashing issue but that's a much more streamlined way of carrying out what I was trying to achieve. If possible would you be able to show me how to start the previous and next user buttons on the form using this method, they were previously coded starting like this:

Private Sub Previous_Click()
ActiveCell.Offset(-1, 0).Select
'Then I had code to populate the userform with the current data in that row

 

[VoG]

It would have helped to see a bit more of your code but something like the following should work

Private Sub Previous_Click()
Dim r As Range
Set r = ActiveCell.Offset(-1, 0)
userform1.textbox1.Text = r.Value
userform.textbox2.Text = r.Offset(, 1).Value