VBA Code Breaks During Runtime But Not In Debug Mode

[xander1981]

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]

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]

In addition, here are some suggestions on Module breaks with no breakpoint added - Microsoft Access / VBA (http://bytes.com/topic/access/answers/767550-module-breaks-no-breakpoint-added)

[xander1981]

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



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]

Can you check if the worksheet is protected?

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=318868#p318868 (https://eileenslounge.com/viewtopic.php?p=318868#p318868)
https://eileenslounge.com/viewtopic.php?p=318311#p318311 (https://eileenslounge.com/viewtopic.php?p=318311#p318311)
https://eileenslounge.com/viewtopic.php?p=318302#p318302 (https://eileenslounge.com/viewtopic.php?p=318302#p318302)
https://eileenslounge.com/viewtopic.php?p=317704#p317704 (https://eileenslounge.com/viewtopic.php?p=317704#p317704)
https://eileenslounge.com/viewtopic.php?p=317704#p317704 (https://eileenslounge.com/viewtopic.php?p=317704#p317704)
https://eileenslounge.com/viewtopic.php?p=317857#p317857 (https://eileenslounge.com/viewtopic.php?p=317857#p317857)
https://eileenslounge.com/viewtopic.php?p=317541#p317541 (https://eileenslounge.com/viewtopic.php?p=317541#p317541)
https://eileenslounge.com/viewtopic.php?p=317520#p317520 (https://eileenslounge.com/viewtopic.php?p=317520#p317520)
https://eileenslounge.com/viewtopic.php?p=317510#p317510 (https://eileenslounge.com/viewtopic.php?p=317510#p317510)
https://eileenslounge.com/viewtopic.php?p=317547#p317547 (https://eileenslounge.com/viewtopic.php?p=317547#p317547)
https://eileenslounge.com/viewtopic.php?p=317573#p317573 (https://eileenslounge.com/viewtopic.php?p=317573#p317573)
https://eileenslounge.com/viewtopic.php?p=317574#p317574 (https://eileenslounge.com/viewtopic.php?p=317574#p317574)
https://eileenslounge.com/viewtopic.php?p=317582#p317582 (https://eileenslounge.com/viewtopic.php?p=317582#p317582)
https://eileenslounge.com/viewtopic.php?p=317583#p317583 (https://eileenslounge.com/viewtopic.php?p=317583#p317583)
https://eileenslounge.com/viewtopic.php?p=317605#p317605 (https://eileenslounge.com/viewtopic.php?p=317605#p317605)
https://eileenslounge.com/viewtopic.php?p=316935#p316935 (https://eileenslounge.com/viewtopic.php?p=316935#p316935)
https://eileenslounge.com/viewtopic.php?p=317030#p317030 (https://eileenslounge.com/viewtopic.php?p=317030#p317030)
https://eileenslounge.com/viewtopic.php?p=317030#p317030 (https://eileenslounge.com/viewtopic.php?p=317030#p317030)
https://eileenslounge.com/viewtopic.php?p=317014#p317014 (https://eileenslounge.com/viewtopic.php?p=317014#p317014)
https://eileenslounge.com/viewtopic.php?p=316940#p316940 (https://eileenslounge.com/viewtopic.php?p=316940#p316940)
https://eileenslounge.com/viewtopic.php?p=316927#p316927 (https://eileenslounge.com/viewtopic.php?p=316927#p316927)
https://eileenslounge.com/viewtopic.php?p=316875#p316875 (https://eileenslounge.com/viewtopic.php?p=316875#p316875)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (https://eileenslounge.com/viewtopic.php?p=316704#p316704)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316046#p316046 (https://eileenslounge.com/viewtopic.php?p=316046#p316046)
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1f2115da95#p317050 (https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1f2115da95#p317050)
https://www.youtube.com/@alanelston2330 (https://www.youtube.com/@alanelston2330)
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z- (https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-)
https://eileenslounge.com/viewtopic.php?p=316154#p316154 (https://eileenslounge.com/viewtopic.php?p=316154#p316154)
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg (https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg)
https://teylyn.com/2017/03/21/dollarsigns/#comment-191 (https://teylyn.com/2017/03/21/dollarsigns/#comment-191)
https://eileenslounge.com/viewtopic.php?p=317050#p317050 (https://eileenslounge.com/viewtopic.php?p=317050#p317050)
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854 (https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854)
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875 (https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875)
https://eileenslounge.com/viewtopic.php?p=316057#p316057 (https://eileenslounge.com/viewtopic.php?p=316057#p316057)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=316705#p316705 (https://eileenslounge.com/viewtopic.php?p=316705#p316705)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (https://eileenslounge.com/viewtopic.php?p=316704#p316704)
https://eileenslounge.com/viewtopic.php?p=176255#p176255 (https://eileenslounge.com/viewtopic.php?p=176255#p176255)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[xander1981]

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]

here is all the code in the form,



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]

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

[Excel Fox]

Did you try to run the workbook from another computer?

[xander1981]

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.

[xander1981]

My reference library looks like this...
1527

[Excel Fox]

Well, can you try to copy all of your code somewhere, and save the file as a non-macro XLSX file. And then reopen Excel, and add back all the modules and the userform and copy all the VBA codes, and then save as a macro enabled XLSM file.

[xander1981]

Thanks Excel fox, done that but not working, But..... I have had a slight breakthrough in understanding whats going wrong. I added a button to the sheet 1 and wrote a macro to call the initial form. When I do this and run the code behind the form there is no problem and so this means the issue is happening because of the method in which I am calling the form, which is on the Workbook Open() event i.e form1.show

When i open the workbook and form pops up the code breaks as the range is not recognized in the class but when the form is called after the workbook is open there is no issue.

[Excel Fox]

Ah! That's a reasonable conclusion. And that's probably because the worksheet object as well as the range objects aren't loaded, and only after the workbook open event is completely executed do those objects load. Well, you've probably nailed it there.

[xander1981]

brilliant, thanks for all your help. In which case I guess i will have to 'Show' the form when the macro is manually started. we got there in the end :)