VB Error on Opening; possibly on Userform

[Electra_Pink]

I hope I can explain the problem well enough that someone can help because I cannot forward the file that is causing the error -- it's nearly 25MB. There's quite an extensive relational database involving several sheets and a VBA userform interface and code. All has been running well for the last couple of years and we've made small changes as time went by with no issues. However, in November 2008, I needed to make a fairly extensive change, adding several new rows of data to several pages of the userform1, forcing some items to be closer together and adding about 30 columns to one of the spreadsheets.

Everything works fine...except...Excel always errors out now upon opening the file. We are getting the "Recover my work and Restart Microsoft Excel" every time we open the file. We found we can circumvent the error by opening the file with DISABLE MACROS, then: open VBA, open USERFORM 1 and close VBA. After that, we can open the file as many times as we want in that instance of Excel and it will always work. If, however, we close that instance of Excel and open a new one, we are back to the Recover/Restart error.

There have been times when we have saved the file after making a change to the VB Code and everyone can suddenly get in directly for awhile -- but after a time, it always reverts to the same problem. This has happened so rarely we are not certain of the commonalities. It's possible that it relates to either the person who saved the file or the person who opened the file starting a new instance of Excel. I should note that only I and one other user can open the file in READ WRITE mode and it doesn't seem to matter which of us saves it, the result is the same. All other users open the file READ ONLY but everyone universally seems to have the same problem opening.

Does this ring a bell with anyone? We have several times re-created this large userform1 thinking that it was corrupt but that only solves things for a little while (but changing anything in VBA does seem to sometimes correct the problem short term). We are stymied and for the correct answer we would gladly give up our first born children (who are surly teenagers, anyway).

 

[VoG]

Try rebuilding http://www.mrexcel.com/forum/showthread.php?t=223851

 

[Datsmart]

Can you post any Event code you have?
In particular, macros that run when you open the file or activate worksheets.

Look in the "Thisworkbook" module, also Worksheet modules.

 

[Electra_Pink]

VoG: Thanks for your response -- it sounds like a lengthy process so I may try it this weekend when everyone is out of the file. I'll post if it works. -- Thanks

Meanwhile...

Datsmart: Thanks also for your response -- I am posting the contents of ThisWorkbook and Module 1 which should be all the event code. I did modify some sensitive User Information but otherwise it is, as written. FYI we did comment out much of ThisWorkbook Code and it "seemed" to fix things as I noted in the email only to have the problem crop up again a bit later.


============================
This is the THISWORKBOOK Code
============================

Public RPTOpened
Public LLOpened

Public Sub Workbook_Open()

llname = "Lessons Learned Action Items Form.xls"
Rptname = "Batch Reporting.xls"

RPTvalue = BatchReportOpen(Rptname)
LLvalue = LLOpen(llname)

If LLOpened = True Then
Exit Sub
End If
If RPTOpened = True Then
Exit Sub
End If

Windows("Cappters.xls").Activate
Sheets("CapptersProj").Activate

On Error Resume Next
Application.WindowState = xlMaximized
UserForm1.Show vbModeless
UserForm1.Hide
Application.WindowState = xlMinimized
UserForm1.Show vbModeless
End Sub

Private Function LLOpen(llname) As Boolean
' Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(llname)
If Err = 0 Then LLOpened = True _
Else LLOpened = False
End Function
Private Function BatchReportOpen(Rptname) As Boolean
' Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(Rptname)
If Err = 0 Then RPTOpened = True _
Else RPTOpened = False
End Function

Private Function EntryIsValidOpen() As Variant
'SETS ADMINISTRATOR RIGHTS
Dim Row As Variant
Dim GKCell As Variant
Dim GKStatuscell As Variant

If Application.UserName = "Ernie" Then
EntryIsValidOpen = True
Exit Function
End If

'LOOKS FOR ADDITIONAL ADMINISTRATOR RIGHTS SET BY ADMINISTRATOR
For Row = 1 To 3
Set GKCell = Sheets("Dropdowns").Range("AS2").Offset(Row - 1, 0)
Set GKStatuscell = Sheets("Dropdowns").Range("AT2").Offset(Row - 1, 0)
If GKCell.Value = Application.UserName Then
If GKStatuscell.Value = "GKValid" Then
ThisEntryisValid = "Valid"
GoTo Finish
End If
End If
Next Row
Finish:

If ThisEntryisValid = "Valid" Then
EntryIsValidOpen = True
Exit Function
End If

If Application.UserName = "Vicki" Then
EntryIsValidOpen = True
Exit Function
End If
If LCase(Application.UserName) = "pNumber" Then
EntryIsValidOpen = True
Exit Function
End If

'MESSAGE DISPLAYS IF ANY USER ATTEMPTS TO ENTER DATA WITHOUT ADMINISTRATOR RIGHTS
EntryIsValidOpen = "You are not authorized to View the Spreadsheet"

End Function
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Unload UserForm1
Unload UserForm11
Unload UserForm13
Unload UserForm14
Unload UserForm15
Unload UserForm16
Unload UserForm17
Unload UserForm18
Unload UserForm19
Unload UserForm20
Unload UserForm21
Unload UserForm22
Unload UserForm23
Unload UserForm24
Application.WindowState = xlMaximized
End Sub


==================================================================
Module 1
==================================================================

'FUNCTION FOR SETTING SCREEN RESOLUTION - DO NOT REMOVE
Declare Function GetSystemMetrics32 Lib "user32" _
Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
' 16-bit API declaration
Declare Function GetSystemMetrics16 Lib "user" _
Alias "GetSystemMetrics" (ByVal nIndex As Integer) As Integer
Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1


Public Function EntryIsValid(cell) As Variant

If Application.UserName = "Ernie" Then
EntryIsValid = True
Exit Function
End If

For Row = 1 To 4
Set GKCell = Sheets("Dropdowns").Range("AS2").Offset(Row - 1, 0)
Set GKStatuscell = Sheets("Dropdowns").Range("AT2").Offset(Row - 1, 0)
If GKCell.Value = Application.UserName Then
If GKStatuscell.Value = "GKValid" Then
ThisEntryisValid = "Valid"
GoTo Finish
End If
End If
Next Row
Finish:
If ThisEntryisValid = "Valid" Then
EntryIsValid = True
Exit Function
End If
If LCase(Application.UserName) = "pNumber" Then
EntryIsValidOpen = True
Exit Function
End If

EntryIsValid = "You cannot edit this field"
Exit Function
End Function

 

[Norie]

If you want to find the root of the problem I suggest you remove all the On Error... stuff.

Also don't use GoTo.

And goto Tools>Options... in the VBE and select Break On All Errors from the Error trapping section on the General tab.

 

[Electra_Pink]

Thanks to all for their thoughts. After "looking" at this problem for almost half a year (there's a dent in my keyboard), we think we've discovered our problem and I am posting it here so that it might help someone else in the future.

Our program was created for use with varied monitor sizes. My monitor font is very small and I use two monitors. That the problem always seemed to originate from when I saved the program file from my machine seemed to be a clue. Before this problem started I had added several rows of entry fields to our form and thought that might have hit some size limit wall but could never find anywhere that said there was a limit.

Finally, I moved one control because I noticed the handle was stuffed up under another control and I could only see the bottom handles. It was purely cosmetic...I thought. But it seemed to fix the problem. However, we tried to duplicate the fix and sometimes it seemed to work and sometimes not. We started thinking that maybe my smaller font was really crunching up the controls. So we completely rebuilt the form. This was not the first time we had rebuilt it but it was the first time we did so with a redesign. This time we rebuilt it with an eye toward ensuring that the field handles do not overlap "much". Touching seems to be ok but completely covering the handles of one control with the handles of another seems to be a problem. So far, that seems to have done the trick. Everyone can get in -- and we are several days without having a problem. We don't know if separating the controls has really helped or if we fixed some other problem accidentally -- you know how that is.

Anyway, if you are having similar issues, my best advice to you is to go through your form section-by-section, using CTL-A to select all the controls within a section and visually make sure all the handles are visible (i.e. you can see all the dots in the corners and middles of every control). If anyone knows of a logical reason for this, feel free to add your comments -- even if it makes me look stupid. Because I'm not feeling particularly smart at the moment, anyway. Thanks - Deborah

 

[Ivan F Moala]

Thanks for posting back Deborah and glad you found it. Always good when you solve it yourself.

 

[Datsmart]

Thank you for the follow up.

That is what is so great with this site, thoughful people posting to help others.
Nicely done.