Results 1 to 7 of 7

Thread: Excel 2013 Workbooks Breaking
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Excel 2013 Workbooks Breaking

    I'd consider myself to have advanced visual basic knowledge and capabilities, however a project I've been working on for a month or so is in risk of being thrown away as my workbook is having issues every time I run a procedure that places formulas into cells. I run into a problem where if I then type in a value on the spreadsheet that I'm manipulating with VBA then the spreadsheet becomes unresponsive, as in I can't use Freeze Panes to view the spreadsheet without excel glitching out, or whenever I type a value into the worksheet it just disappears after I hit enter.

    I have figured out how to get it to work again. And it's by stepping through a macro like this.


    Code:
    Sub applicationSaver()    Application.EnableEvents = False: Application.ScreenUpdating = False
        Application.EnableEvents = True: Application.ScreenUpdating = True
    End Sub
    I cant just run this code it only works when I step through it by hitting F8
    Any help will be appreciated.

  2. #2
    New Member
    Join Date
    Nov 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Workbooks Breaking

    Here's an update, as long as I step into code the application works fine after. I just can't be stepping into/out of code repeatedly for this application.

    So the question now is, to work around this bug, how do I replicate stepping into/out of visual basic code without forcing the user to do that.

    Thanks guys!

  3. #3
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel 2013 Workbooks Breaking

    I'm guessing somewhere in your code you have Application.EnableEvents = False and thats partly why you can only recover when this is run

    Code:
    Sub applicationSaver()
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    Sub applicationSaver2()
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    have you Option Explicit at the top of the module outside the sub, does the code compile
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  4. #4
    New Member
    Join Date
    Nov 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Workbooks Breaking

    I use option explicit religiously, the code compiles it just pasted incorrectly into the forum post. I went ahead and tried both your examples you posted and again it only works when the code is stepped into. When I type into a cell the entire cell is greyed, out like it's selected or something, and then when I hit enter the numbers I just typed disappear and nothing happens. And I've gone and tested it with a different section of code that looks like this

    Code:
    Sub testIt()
    
    Dim a as string
    a = "Test"
    
    End Sub
    I guess I don't know what is happening when code is stepped into with VBA, because if I did I would be able to replicate it! (hopefully)

    Thanks again for any help!

  5. #5
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel 2013 Workbooks Breaking

    is the workbook shared or protected (as for not working the four instructions will run through and not be seen by the human eye. If you left click in the border next to a line of code you will get a dot, then when you are in the sub press F5 and it should just run to the dot, you can use escape to get out of that
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  6. #6
    New Member
    Join Date
    Nov 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Workbooks Breaking

    It is a shared workbook however there are no protection settings on the workbook at this time. (or have there ever been, but I plan on putting protection on it later)

  7. #7
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel 2013 Workbooks Breaking

    unshare it and try your code again
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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