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,401
    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,401
    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,401
    Rep Power
    10
    Can you check if the worksheet is protected?
    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
    12
    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,401
    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
  •