VBA Code Breaks During Runtime But Not In Debug Mode

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.

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

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

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

---

Can you check if the worksheet is protected?

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
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=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
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=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
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=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
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://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=176255#p176255
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984
https://eileenslounge.com/viewtopic.php?f=30&t=41610
https://eileenslounge.com/viewtopic.php?p=322176#p322176
https://eileenslounge.com/viewtopic.php?p=322238#p322238
https://eileenslounge.com/viewtopic.php?p=322270#p322270
https://eileenslounge.com/viewtopic.php?p=322300#p322300
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086
https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851
http://www.eileenslounge.com/viewtopic.php?p=322084#p322084
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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

---

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

Did you try to run the workbook from another computer?

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.

My reference library looks like this...
Attachment 1527

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.

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.

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.

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 :)