VBA Code Breaks During Runtime But Not In Debug Mode

**xander1981** · 04-09-2014, 01:40 PM

Hello,

I think I am doing something stupid but can't figure out what. go to the code file and run the code there are not issues at all. I run the code from the workbook form and there are random errors???

I suspect this must be down to a setting on TOOLS>Options but I can't think what.

to clarrify, I have a workbook that pops up with a userform on OPEN event, from the form the user can produce various reports. All works fine when the form us run from code file but if user opens the workbook and continues from the userform there are lots of debug errors. This only happens on my machine also which leads me to think its my specific excel settings.

Any help would be very happily received. Thanks.

**Excel Fox** · 04-09-2014, 09:05 PM

xander1981, go to the VBA editor, and compile the VBA project. Exit Excel, and then after restarting your computer, open the workbook again.

**Excel Fox** · 04-09-2014, 10:02 PM

In addition, here are some suggestions on Module breaks with no breakpoint added - Microsoft Access / VBA

**xander1981** · 04-10-2014, 01:26 PM

Thanks Excel Fox but that didnt work. Here is my code, It breaks on line "ActiveSheet.Range("A10000").End(xlUp).Offset(1).S elect"

Code:

Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Me.oComment.Value = "" Then
MsgBox ("Please write your comment or cancel"), vbOKOnly, "Missing info"
Exit Sub
End If

Dim oAlexJ As String, AlexTwo As String
oAlexJ = Pass"
oAlexTwo = "word"
Workbooks.Open FileName:="c:\Felixstowe\Employee Opinion Database.xlsx", ReadOnly:=False, Password:=oAlexJ & AlexTwo, ignorereadonlyrecommended:=True
ActiveSheet.Range("A10000").End(xlUp).Offset(1).Select
Selection.Value = Me.oComment.Value
ActiveCell.Offset(0, 1).Value = Date
Workbooks("Employee Opinion Database.xlsx").Save
Workbooks("Employee Opinion Database.xlsx").Close
Unload Opinion
Workbooks("Employee Opinion Send.xlsm").Close
 
Application.ScreenUpdating = True
Application.DisplayAlerts = False

**Excel Fox** · 04-10-2014, 02:17 PM

**xander1981** · 04-10-2014, 03:15 PM

No, not protected. I have compiled the project with no errors and option explicit is now set on each form that runs code. So strange it works fine when run from code page but not form. I also notice that 'WITH' statements don't work now if thats any clue to the problem. The code runs from a Macro enabled workbook and opens various .xls workbooks.

**xander1981** · 04-10-2014, 03:30 PM

here is all the code in the form,

Code:

Option Explicit
Private Sub oCancel_Click()
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End Sub

Private Sub oSend_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Me.oComment.Value = "" Then
MsgBox ("Please write your comment or cancel"), vbOKOnly, "Missing info"
Exit Sub
End If

Dim oAlexJ As String, oAlexTwo As String
oAlexJ = "Pass"
oAlexTwo = "word"
Workbooks.Open FileName:="c:\\DGF Felixstowe\Employee Opinion Database.xlsx", ReadOnly:=False, Password:=oAlexJ & oAlexTwo, ignorereadonlyrecommended:=True
ActiveSheet.Range("A10000").End(xlUp).Offset(1).Select
Selection.Value = Me.oComment.Value
ActiveCell.Offset(0, 1).Value = Date
Workbooks("Employee Opinion Database.xlsx").Save
Workbooks("Employee Opinion Database.xlsx").Close
Unload Opinion
Workbooks("Employee Opinion Send.xlsm").Close
 
Application.ScreenUpdating = True
Application.DisplayAlerts = False
End Sub

Private Sub UserForm_Initialize()
Me.BackColor = RGB(255, 192, 0)
Me.oSend.BackColor = RGB(255, 192, 0)
Me.oSend.ForeColor = RGB(153, 0, 51)
Me.oCancel.BackColor = RGB(255, 192, 0)
Me.oCancel.ForeColor = RGB(153, 0, 51)
End Sub

**snb** · 04-10-2014, 04:54 PM

Look into the vbeditor/ special/references. You'll find probably some missing.

**Excel Fox** · 04-10-2014, 05:37 PM

Did you try to run the workbook from another computer?

**xander1981** · 04-10-2014, 06:06 PM

Thanks snb yes I thought of that also but don't think my library is missing anything. Excel Fox - Yes and there are no issues so i think its specific to me excel settings.