Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: VBA Code Breaks During Runtime But Not In Debug Mode

  1. #1

    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.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    xander1981, go to the VBA editor, and compile the VBA project. Exit Excel, and then after restarting your computer, open the workbook again.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    In addition, here are some suggestions on Module breaks with no breakpoint added - Microsoft Access / VBA
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    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

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    Last edited by DocAElstein; 07-27-2024 at 01:48 PM.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    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.

  7. #7
    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

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Look into the vbeditor/ special/references. You'll find probably some missing.

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Did you try to run the workbook from another computer?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    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.

Similar Threads

  1. Did You Know :: Bullet Points & Line Breaks in Excel Cells
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 1
    Last Post: 06-11-2013, 01:55 PM
  2. Combobox Not Working In Excel Workbook Shared Mode
    By peter renton in forum Excel Help
    Replies: 15
    Last Post: 06-03-2013, 01:25 PM
  3. Changing Slideshow viewing mode to kiosk using vba
    By Times in forum Powerpoint Help
    Replies: 1
    Last Post: 05-10-2013, 12:37 AM
  4. Runtime Error 481 invalid figure when PNG
    By Tony in forum Excel Help
    Replies: 0
    Last Post: 02-12-2013, 12:59 AM
  5. VBA Code Works Only in debug Mode.
    By princ_wns in forum Excel Help
    Replies: 2
    Last Post: 06-17-2012, 06:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •